Hi ,
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.
I have entered the two execution plans to explain.depesz.com - the results
are here:
slow (first execution): http://explain.depesz.com/s/tvd
fast (second execution): http://explain.depesz.com/s/3C
It seems there's something very wrong - the plans are "equal" but in the
first case the results (actual time) are multiplied by 100. Eithere there
is some sort of cache (so the second execution is much faster), or the
system was busy during the first execution, or there is something wrong
with the hardware.
Yes, I think it is the cache.
I am running the first and the second query on my test server and no
one is working on it at this time.
My general problem is tath the query without and n.num like '191%'
is much faster.
I detect the same also with many other field on the n-table.
I think planer expect to find all the records in cache but it is not there.
Can I tune some parameters in postgres.conf (effective_cache_size for
example) ?
regards
Tom (not Lane)
regards, Ivan.
-------------------------------------
Powered by Mail.BG - http://mail.bg
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
