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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
