possible wrong query plan on pg 8.3.5, - Mailing list pgsql-performance

Hi,

I am running a relativ complex query on pg 8.3.5 and have (possible)
wrong query plan.
My select :

explain analyze  select d.ids  from a_doc d  join a_sklad s on
(d.ids=s.ids_doc)  join a_nomen n on (n.ids=s.ids_num)  join a_nom_gr
nmgr on (nmgr.ids=n.ids_grupa)  join a_gar_prod_r gr on
(gr.ids_a_sklad=s.ids and gr.sernum!='ok')  join a_location l on
(l.ids=s.ids_sklad)  join a_klienti kl on (kl.ids=d.ids_ko)  left
outer join a_slujiteli sl on (sl.ids=d.ids_slu_ka)  left outer join
a_slujiteli slu on (slu.ids=d.ids_slu_targ)  where d.op=1  AND
d.date_op >= 12320 AND d.date_op <= 12362 and n.num like '191%';

If I run the query without thle last part : and n.num like '191%'
it work ok as speed ~ 30 sec on not very big db.
If I run the full query it take very long time to go ( i never waited
to the end but it take  > 60 min.)

The filed n.num is indexed and looks ok for me.

I post explan analyze for query without n.num like '191%' and only
explain for query with n.num like '191%' :

explain analyze  select d.ids  from a_doc d  join a_sklad s on
(d.ids=s.ids_doc)  join a_nomen n on (n.ids=s.ids_num)  join a_nom_gr
nmgr on (nmgr.ids=n.ids_grupa)  join a_gar_prod_r gr on
(gr.ids_a_sklad=s.ids and gr.sernum!='ok')  join a_location l on
(l.ids=s.ids_sklad)  join a_klienti kl on (kl.ids=d.ids_ko)  left
outer join a_slujiteli sl on (sl.ids=d.ids_slu_ka)  left outer join
a_slujiteli slu on (slu.ids=d.ids_slu_targ)  where d.op=1  AND
d.date_op >= 12320 AND d.date_op <= 12362 ;

-------------
  Nested Loop Left Join  (cost=345.50..190641.97 rows=1488 width=64)
(actual time=446.905..30681.604 rows=636 loops=1)
    ->  Nested Loop  (cost=345.50..189900.14 rows=1488 width=128)
(actual time=446.870..30676.472 rows=636 loops=1)
          ->  Nested Loop  (cost=345.50..189473.66 rows=1488
width=192) (actual time=427.522..30595.438 rows=636 loops=1)
                ->  Nested Loop  (cost=345.50..189049.52 rows=1488
width=192) (actual time=370.034..29609.647 rows=636 loops=1)
                      ->  Hash Join  (cost=345.50..178565.42 rows=7204
width=256) (actual time=363.667..29110.776 rows=9900 loops=1)
                            Hash Cond: (s.ids_sklad = l.ids)
                            ->  Nested Loop  (cost=321.79..178442.65
rows=7204 width=320) (actual time=363.163..29096.591 rows=9900 loops=1)
                                  ->  Hash Left Join
(cost=321.79..80186.96 rows=4476 width=128) (actual
time=278.277..13852.952 rows=8191 loops=1)
                                        Hash Cond: (d.ids_slu_ka = sl.ids)
                                        ->  Nested Loop
(cost=223.17..80065.83 rows=4476 width=192) (actual
time=164.664..13731.739 rows=8191 loops=1)
                                              ->  Bitmap Heap Scan on
a_doc d  (cost=223.17..36926.67 rows=6598 width=256) (actual
time=121.306..587.479 rows=8191 loops=1)
                                                    Recheck Cond:
((date_op >= 12320) AND (date_op <= 12362))
                                                    Filter: (op = 1)
                                                    ->  Bitmap Index
Scan on i_doc_date_op  (cost=0.00..221.52 rows=10490 width=0) (actual
time=107.212..107.212 rows=11265 loops=1)
                                                          Index Cond:
((date_op >= 12320) AND (date_op <= 12362))
                                              ->  Index Scan using
a_klienti_pkey on a_klienti kl  (cost=0.00..6.53 rows=1 width=64)
(actual time=1.598..1.602 rows=1 loops=8191)
                                                    Index Cond:
(kl.ids = d.ids_ko)
                                        ->  Hash  (cost=77.72..77.72
rows=1672 width=64) (actual time=113.591..113.591 rows=1672 loops=1)
                                              ->  Seq Scan on
a_slujiteli sl  (cost=0.00..77.72 rows=1672 width=64) (actual
time=10.434..112.508 rows=1672 loops=1)
                                  ->  Index Scan using i_sklad_ids_doc
on a_sklad s  (cost=0.00..21.90 rows=4 width=256) (actual
time=1.582..1.859 rows=1 loops=8191)
                                        Index Cond: (s.ids_doc = d.ids)
                            ->  Hash  (cost=19.43..19.43 rows=343
width=64) (actual time=0.460..0.460 rows=343 loops=1)
                                  ->  Seq Scan on a_location l
(cost=0.00..19.43 rows=343 width=64) (actual time=0.017..0.248
rows=343 loops=1)
                      ->  Index Scan using i_a_gar_prod_r_ids_a_sklad
on a_gar_prod_r gr  (cost=0.00..1.44 rows=1 width=64) (actual
time=0.049..0.049 rows=0 loops=9900)
                            Index Cond: (gr.ids_a_sklad = s.ids)
                            Filter: (gr.sernum <> 'ok'::text)
                ->  Index Scan using a_nomen_pkey on a_nomen n
(cost=0.00..0.27 rows=1 width=128) (actual time=1.548..1.548 rows=1
loops=636)
                      Index Cond: (n.ids = s.ids_num)
          ->  Index Scan using a_nom_gr_pkey on a_nom_gr nmgr
(cost=0.00..0.27 rows=1 width=64) (actual time=0.125..0.126 rows=1
loops=636)
                Index Cond: (nmgr.ids = n.ids_grupa)
    ->  Index Scan using a_slujiteli_pkey on a_slujiteli slu
(cost=0.00..0.49 rows=1 width=64) (actual time=0.006..0.006 rows=1
loops=636)
          Index Cond: (slu.ids = d.ids_slu_targ)
  Total runtime: 30682.134 ms
(33 rows)


  explain select d.ids  from a_doc d  join a_sklad s on
(d.ids=s.ids_doc)  join a_nomen n on (n.ids=s.ids_num)  join a_nom_gr
nmgr on (nmgr.ids=n.ids_grupa)  join a_gar_prod_r gr on
(gr.ids_a_sklad=s.ids and gr.sernum!='ok')  join a_location l on
(l.ids=s.ids_sklad)  join a_klienti kl on (kl.ids=d.ids_ko)  left
outer join a_slujiteli sl on (sl.ids=d.ids_slu_ka)  left outer join
a_slujiteli slu on (slu.ids=d.ids_slu_targ)  where d.op=1  AND
d.date_op >= 12320 AND d.date_op <= 12362 and n.num like '191%';
                                                                  QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop Left Join  (cost=63.61..133467.00 rows=4 width=64)
    ->  Nested Loop  (cost=63.61..133433.87 rows=4 width=128)
          ->  Nested Loop  (cost=63.61..133422.75 rows=4 width=192)
                ->  Nested Loop Left Join  (cost=63.61..133421.63
rows=4 width=256)
                      ->  Nested Loop  (cost=63.61..133420.31 rows=4 width=320)
                            ->  Nested Loop  (cost=63.61..133381.08
rows=6 width=384)
                                  ->  Nested Loop
(cost=63.61..127621.55 rows=2833 width=192)
                                        ->  Nested Loop
(cost=63.61..107660.43 rows=13716 width=256)
                                              ->  Index Scan using
i_nomen_num on a_nomen n  (cost=0.00..56.39 rows=24 width=128)
                                                    Index Cond:
(((num)::text >= '191'::text) AND ((num)::text < '192'::text))
                                                    Filter:
((num)::text ~~ '191%'::text)
                                              ->  Bitmap Heap Scan on
a_sklad s  (cost=63.61..4468.84 rows=1173 width=256)
                                                    Recheck Cond:
(s.ids_num = n.ids)
                                                    ->  Bitmap Index
Scan on i_sklad_ids_num  (cost=0.00..63.32 rows=1173 width=0)
                                                          Index Cond:
(s.ids_num = n.ids)
                                        ->  Index Scan using
i_a_gar_prod_r_ids_a_sklad on a_gar_prod_r gr  (cost=0.00..1.44 rows=1
width=64)
                                              Index Cond:
(gr.ids_a_sklad = s.ids)
                                              Filter: (gr.sernum <> 'ok'::text)
                                  ->  Index Scan using a_doc_pkey on
a_doc d  (cost=0.00..2.02 rows=1 width=256)
                                        Index Cond: (d.ids = s.ids_doc)
                                        Filter: ((d.date_op >= 12320)
AND (d.date_op <= 12362) AND (d.op = 1))
                            ->  Index Scan using a_klienti_pkey on
a_klienti kl  (cost=0.00..6.53 rows=1 width=64)
                                  Index Cond: (kl.ids = d.ids_ko)
                      ->  Index Scan using a_slujiteli_pkey on
a_slujiteli sl  (cost=0.00..0.32 rows=1 width=64)
                            Index Cond: (sl.ids = d.ids_slu_ka)
                ->  Index Scan using a_location_pkey on a_location l
(cost=0.00..0.27 rows=1 width=64)
                      Index Cond: (l.ids = s.ids_sklad)
          ->  Index Scan using a_nom_gr_pkey on a_nom_gr nmgr
(cost=0.00..2.77 rows=1 width=64)
                Index Cond: (nmgr.ids = n.ids_grupa)
    ->  Index Scan using a_slujiteli_pkey on a_slujiteli slu
(cost=0.00..8.27 rows=1 width=64)
          Index Cond: (slu.ids = d.ids_slu_targ)
(31 rows)


I can not find the reason for this problem.
Is it bug or configuration problem ?
I am running the pg on Contos 5.2 8 GB RAM.

Regards, Ivan.



-------------------------------------

ICN.Bg с най-богатата гама от Хостинг услуги на Българския пазар -
Професионален Хостинг за 23 стотинки на ден с ДДС, 18 GB място,
Неограничен трафик и Безплатен домейн
  http://www.icn.bg/?referer=MailBg