From: lionel duboeuf <lionel.duboeuf@boozter.com>
To: pgsql-performance@postgresql.org
Subject: Re: Almost infinite query -> Different Query Plan when changing where clause value
Date: Mon, 15 Feb 2010 10:52:49 +0100
Message-ID: <4B791971.6020804@boozter.com> (raw)
In-Reply-To: <4B756CE5020000250002F27C@gw.wicourts.gov>
References: <4B755913.7090004@boozter.com>
<4B7519C5020000250002F242@gw.wicourts.gov>
<4B75BADE.9030906@boozter.com>
<4B756CE5020000250002F27C@gw.wicourts.gov>
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.duboeuf@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
Attachments:explain_analyse_ok.txt (text/plain, 5.8K) download
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lionel.duboeuf@boozter.com
Subject: Re: Almost infinite query -> Different Query Plan when changing where clause value
In-Reply-To: <4B791971.6020804@boozter.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox