See as attachment the "correct" query plan for an other 'user'.
I confirm by executing manual "VACUUM ANALYZE" that the problem is solved.
But what i don't understand is that i would expect autovacuum to do the job.
Lionel
Kevin Grittner a écrit :
lionel duboeuf <lionel.dubo...@boozter.com> wrote:
Thanks kevin for your answer. Here is some additionnal
informations attached as files.
Could you supply an EXPLAIN ANALYZE of the fast plan as an
attachment, for comparison?
Anyway, it looks like at least one big problem is the bad estimate
on how many rows will be generated by joining to the users5_ table:
(cost=13.20..1427.83 rows=1 width=24)
(actual time=1.374..517.662 rows=122850 loops=1)
If it had expected 122850 rows to qualify for that join, it probably
would have picked a different plan.
I just reread your original email, and I'm not sure I understand
what you meant regarding VACUUM ANALYZE. If you run that right
beforehand, do you still get the slow plan for user 10?
-Kevin
QUERY PLAN
Nested Loop (cost=40145.11..87205.30 rows=1836 width=16) (actual
time=4675.927..8731.066 rows=14 loops=1)
-> Seq Scan on "user" user6_ (cost=0.00..5.89 rows=1 width=4) (actual
time=0.021..0.070 rows=1 loops=1)
Filter: (user_seqnum = 2)
-> Nested Loop (cost=40145.11..87181.05 rows=1836 width=20) (actual
time=4675.899..8730.951 rows=14 loops=1)
-> Seq Scan on block block10_ (cost=0.00..1.14 rows=1 width=4)
(actual time=0.005..0.013 rows=1 loops=1)
Filter: (block_seqnum = 5)
-> Hash Join (cost=40145.11..87161.55 rows=1836 width=24) (actual
time=4675.889..8730.897 rows=14 loops=1)
Hash Cond: (link0_.element_source = blocks9_.element_seqnum)
-> Hash Join (cost=38355.16..84432.84 rows=81814 width=28)
(actual time=1959.785..8659.972 rows=54481 loops=1)
Hash Cond: (link0_.element_source =
element2_.element_seqnum)
-> Hash Join (cost=15293.09..59268.67 rows=100436
width=12) (actual time=213.249..6178.980 rows=357563 loops=1)
Hash Cond: (link0_.element_target =
element1_.element_seqnum)
-> Seq Scan on link link0_ (cost=0.00..36544.07
rows=1968922 width=12) (actual time=0.017..2750.650 rows=1968833 loops=1)
Filter: (link_sup_date IS NULL)
-> Hash (cost=15280.27..15280.27 rows=1025
width=12) (actual time=212.858..212.858 rows=15 loops=1)
-> Nested Loop (cost=2881.38..15280.27
rows=1025 width=12) (actual time=9.566..212.823 rows=15 loops=1)
-> Seq Scan on "user" user4_
(cost=0.00..5.89 rows=1 width=4) (actual time=0.012..0.051 rows=1 loops=1)
Filter: (user_seqnum = 2)
-> Nested Loop (cost=2881.38..15264.14
rows=1025 width=16) (actual time=9.549..212.724 rows=15 loops=1)
-> Seq Scan on block block8_
(cost=0.00..1.14 rows=1 width=4) (actual time=0.004..0.009 rows=1 loops=1)
Filter: (block_seqnum = 5)
-> Nested Loop
(cost=2881.38..15252.75 rows=1025 width=20) (actual time=9.540..212.673 rows=15
loops=1)
-> Hash Join
(cost=2881.38..6613.08 rows=1434 width=16) (actual time=9.522..212.453 rows=15
loops=1)
Hash Cond:
(users3_.element_seqnum = blocks7_.element_seqnum)
-> Bitmap Heap Scan on
user_element users3_ (cost=1091.43..4090.04 rows=63889 width=8) (actual
time=9.252..131.240 rows=60568 loops=1)
Recheck Cond:
(user_seqnum = 2)
-> Bitmap Index
Scan on fki_user_element_user (cost=0.00..1075.46 rows=63889 width=0) (actual
time=9.017..9.017 rows=60568 loops=1)
Index Cond:
(user_seqnum = 2)
-> Hash
(cost=1705.72..1705.72 rows=6738 width=8) (actual time=0.079..0.079 rows=15
loops=1)
-> Bitmap Heap
Scan on element_block blocks7_ (cost=116.50..1705.72 rows=6738 width=8)
(actual time=0.032..0.053 rows=15 loops=1)
Recheck
Cond: (block_seqnum = 5)
-> Bitmap
Index Scan on fki_element_block_block (cost=0.00..114.81 rows=6738 width=0)
(actual time=0.025..0.025 rows=15 loops=1)
Index
Cond: (block_seqnum = 5)
-> Index Scan using
pk_element on element element1_ (cost=0.00..6.01 rows=1 width=4) (actual
time=0.007..0.008 rows=1 loops=15)
Index Cond:
(element1_.element_seqnum = users3_.element_seqnum)
-> Hash (cost=21951.46..21951.46 rows=63889 width=16)
(actual time=1746.309..1746.309 rows=60568 loops=1)
-> Hash Join (cost=15534.07..21951.46 rows=63889
width=16) (actual time=1277.246..1653.836 rows=60568 loops=1)
Hash Cond: (users5_.element_seqnum =
element2_.element_seqnum)
-> Bitmap Heap Scan on user_element users5_
(cost=1091.43..4090.04 rows=63889 width=8) (actual time=7.775..87.724
rows=60568 loops=1)
Recheck Cond: (user_seqnum = 2)
-> Bitmap Index Scan on
fki_user_element_user (cost=0.00..1075.46 rows=63889 width=0) (actual
time=7.565..7.565 rows=60568 loops=1)
Index Cond: (user_seqnum = 2)
-> Hash (cost=7552.95..7552.95 rows=419895
width=8) (actual time=1269.211..1269.211 rows=419895 loops=1)
-> Seq Scan on element element2_
(cost=0.00..7552.95 rows=419895 width=8) (actual time=0.031..640.484
rows=419895 loops=1)
-> Hash (cost=1705.72..1705.72 rows=6738 width=8) (actual
time=0.081..0.081 rows=15 loops=1)
-> Bitmap Heap Scan on element_block blocks9_
(cost=116.50..1705.72 rows=6738 width=8) (actual time=0.034..0.058 rows=15
loops=1)
Recheck Cond: (block_seqnum = 5)
-> Bitmap Index Scan on fki_element_block_block
(cost=0.00..114.81 rows=6738 width=0) (actual time=0.028..0.028 rows=15 loops=1)
Index Cond: (block_seqnum = 5)
Total runtime: 8731.494 ms
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
