Re: possible wrong query plan on pg 8.3.5,
От | zz_11@mail.bg |
---|---|
Тема | Re: possible wrong query plan on pg 8.3.5, |
Дата | |
Msg-id | 20090914091632.n1ys04weulxc8c4c@mail.bg обсуждение исходный текст |
Ответ на | Re: possible wrong query plan on pg 8.3.5, (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: possible wrong query plan on pg 8.3.5,
(tv@fuzzy.cz)
Re: possible wrong query plan on pg 8.3.5, (Scott Marlowe <scott.marlowe@gmail.com>) |
Список | pgsql-performance |
Цитат от Tom Lane <tgl@sss.pgh.pa.us>: > zz_11@mail.bg writes: >> I am running a relativ complex query on pg 8.3.5 and have (possible) >> wrong query plan. >> ... >> 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.) > > I'm betting that it's badly underestimating the number of rows > satisfying the LIKE condition: > >> -> 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) > > Is 24 the right number of rows for that, or anywhere close? If not, try > raising the statistics target for this table. > > regards, tom lane > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > > Hi Tom, Yes, 24 is relative ok ( the real number is 20). And the statistic target for the database is 800 at the moment. If needet I can set it to 1000 ( the maximum). Also I waited to the end of this query to gather info for explain analyze. It is it: 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%'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=63.64..133732.47 rows=4 width=64) (actual time=616059.833..1314396.823 rows=91 loops=1) -> Nested Loop (cost=63.64..133699.35 rows=4 width=128) (actual time=616033.205..1313991.756 rows=91 loops=1) -> Nested Loop (cost=63.64..133688.22 rows=4 width=192) (actual time=616033.194..1313991.058 rows=91 loops=1) -> Nested Loop Left Join (cost=63.64..133687.10 rows=4 width=256) (actual time=616033.183..1313936.577 rows=91 loops=1) -> Nested Loop (cost=63.64..133685.78 rows=4 width=320) (actual time=616033.177..1313929.258 rows=91 loops=1) -> Nested Loop (cost=63.64..133646.56 rows=6 width=384) (actual time=616007.069..1313008.701 rows=91 loops=1) -> Nested Loop (cost=63.64..127886.54 rows=2833 width=192) (actual time=376.309..559763.450 rows=211357 loops=1) -> Nested Loop (cost=63.64..107934.83 rows=13709 width=256) (actual time=224.058..148475.499 rows=370803 loops=1) -> Index Scan using i_nomen_num on a_nomen n (cost=0.00..56.39 rows=24 width=128) (actual time=15.702..198.049 rows=20 loops=1) 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.64..4480.23 rows=1176 width=256) (actual time=93.223..7398.764 rows=18540 loops=20) Recheck Cond: (s.ids_num = n.ids) -> Bitmap Index Scan on i_sklad_ids_num (cost=0.00..63.34 rows=1176 width=0) (actual time=78.430..78.430 rows=18540 loops=20) 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) (actual time=1.098..1.108 rows=1 loops=370803) 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) (actual time=3.563..3.563 rows=0 loops=211357) 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) (actual time=10.109..10.113 rows=1 loops=91) 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) (actual time=0.078..0.078 rows=0 loops=91) 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) (actual time=0.596..0.597 rows=1 loops=91) 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) (actual time=0.005..0.006 rows=1 loops=91) 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) (actual time=4.448..4.449 rows=1 loops=91) Index Cond: (slu.ids = d.ids_slu_targ) Total runtime: 1314397.153 ms (32 rows) And if I try this query for second time it is working very fast: ----------------------------------------- Nested Loop Left Join (cost=63.64..133732.47 rows=4 width=64) (actual time=9438.195..29429.861 rows=91 loops=1) -> Nested Loop (cost=63.64..133699.35 rows=4 width=128) (actual time=9438.155..29363.045 rows=91 loops=1) -> Nested Loop (cost=63.64..133688.22 rows=4 width=192) (actual time=9438.145..29355.229 rows=91 loops=1) -> Nested Loop Left Join (cost=63.64..133687.10 rows=4 width=256) (actual time=9438.132..29335.008 rows=91 loops=1) -> Nested Loop (cost=63.64..133685.78 rows=4 width=320) (actual time=9438.128..29314.640 rows=91 loops=1) -> Nested Loop (cost=63.64..133646.56 rows=6 width=384) (actual time=9438.087..29312.490 rows=91 loops=1) -> Nested Loop (cost=63.64..127886.54 rows=2833 width=192) (actual time=192.451..21060.439 rows=211357 loops=1) -> Nested Loop (cost=63.64..107934.83 rows=13709 width=256) (actual time=192.367..11591.661 rows=370803 loops=1) -> Index Scan using i_nomen_num on a_nomen n (cost=0.00..56.39 rows=24 width=128) (actual time=0.045..0.434 rows=20 loops=1) 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.64..4480.23 rows=1176 width=256) (actual time=14.333..565.417 rows=18540 loops=20) Recheck Cond: (s.ids_num = n.ids) -> Bitmap Index Scan on i_sklad_ids_num (cost=0.00..63.34 rows=1176 width=0) (actual time=9.164..9.164 rows=18540 loops=20) 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) (actual time=0.024..0.024 rows=1 loops=370803) 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) (actual time=0.038..0.038 rows=0 loops=211357) 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) (actual time=0.021..0.022 rows=1 loops=91) 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) (actual time=0.222..0.222 rows=0 loops=91) 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) (actual time=0.220..0.220 rows=1 loops=91) 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) (actual time=0.083..0.084 rows=1 loops=91) 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) (actual time=0.731..0.732 rows=1 loops=91) Index Cond: (slu.ids = d.ids_slu_targ) Total runtime: 29430.170 ms After this I wait a little time ( ~30 min) and all works bad again. I think it is related to cache or not ? Can I disable using index of n.num field for this query onli ( I know it is wrong direction, but I have no idea how to solve this situaion) ? Regards, Ivan. ------------------------------------- 3.5 Mbps Сателитен достъп до Интернет навсякъде в България www.tooway.bg http://www.tooway.bg/
В списке pgsql-performance по дате отправления: