From: lionel duboeuf <lionel.duboeuf@boozter.com>
To: pgsql-performance@postgresql.org
Subject: Almost infinite query -> Different Query Plan when changing where clause value
Date: Fri, 12 Feb 2010 14:35:15 +0100
Message-ID: <4B755913.7090004@boozter.com> (raw)
Some informations:
The following problem has been detected on
Postgresql 8.3 and 8.4. on System linux or windows
Default AutoVacuum daemon working
One pg_dump every day
This happens sometimes and i don't see what can be the cause.
A manual Vacuum Analyse repair that problem.
Dear you all,
Hope someone would help me understand why only changing a where clause
value attribute will have a big impact on query plan and lead to almost
unending query.
regards
lionel
This is my query:
select element2_.element_seqnum as col_0_0_,
element1_.element_seqnum as col_1_0_,
link0_.link_rank_in_bunch as col_2_0_,
element2_.element_state as col_3_0_
from public.link link0_
inner join public.element element1_ on
link0_.element_target=element1_.element_seqnum
inner join public.user_element users3_ on
element1_.element_seqnum=users3_.element_seqnum
inner join public.user user4_ on users3_.user_seqnum=user4_.user_seqnum
inner join public.element_block blocks7_ on
element1_.element_seqnum=blocks7_.element_seqnum
inner join public.block block8_ on
blocks7_.block_seqnum=block8_.block_seqnum
inner join public.element element2_ on
link0_.element_source=element2_.element_seqnum
inner join public.user_element users5_ on
element2_.element_seqnum=users5_.element_seqnum
inner join public.user user6_ on
users5_.user_seqnum=user6_.user_seqnum
inner join public.element_block blocks9_ on
element2_.element_seqnum=blocks9_.element_seqnum
inner join public.block block10_ on
blocks9_.block_seqnum=block10_.block_seqnum
where block10_.block_seqnum=5
and block8_.block_seqnum=5
and user6_.user_seqnum=XX
and (link0_.link_sup_date is null)
and user4_.user_seqnum=XX
-------------------------------------------------------
This one works well: Query Plan for that user "2"
("user4_.user_seqnum=2" and "user6_.user_seqnum=2 ") will be:
Nested Loop (cost=36.33..5932.28 rows=1 width=16)
-> Nested Loop (cost=36.33..5926.38 rows=1 width=20)
-> Nested Loop (cost=36.33..5925.23 rows=1 width=24)
Join Filter: (link0_.element_source = blocks9_.element_seqnum)
-> Index Scan using fki_element_block_block on
element_block blocks9_ (cost=0.00..8.29 rows=1 width=8)
Index Cond: (block_seqnum = 5)
-> Nested Loop (cost=36.33..5916.64 rows=24 width=28)
-> Nested Loop (cost=36.33..5883.29 rows=4 width=40)
-> Seq Scan on "user" user4_ (cost=0.00..5.89
rows=1 width=4)
Filter: (user_seqnum = 2)
-> Nested Loop (cost=36.33..5877.36 rows=4
width=36)
-> Nested Loop (cost=36.33..5860.81
rows=4 width=28)
-> Nested Loop
(cost=36.33..5835.59 rows=6 width=20)
-> Nested Loop
(cost=0.00..17.76 rows=1 width=8)
-> Nested Loop
(cost=0.00..16.61 rows=1 width=12)
-> Index Scan
using fki_element_block_block on element_block blocks7_
(cost=0.00..8.29 rows=1 width=8)
Index Cond:
(block_seqnum = 5)
-> Index Scan
using pk_element on element element1_ (cost=0.00..8.31 rows=1 width=4)
Index Cond:
(element1_.element_seqnum = blocks7_.element_seqnum)
-> Seq Scan on block
block8_ (cost=0.00..1.14 rows=1 width=4)
Filter:
(block8_.block_seqnum = 5)
-> Bitmap Heap Scan on link
link0_ (cost=36.33..5792.21 rows=2050 width=12)
Recheck Cond:
(link0_.element_target = element1_.element_seqnum)
Filter:
(link0_.link_sup_date IS NULL)
-> Bitmap Index Scan
on element_target_fk (cost=0.00..35.82 rows=2050 width=0)
Index Cond:
(link0_.element_target = element1_.element_seqnum)
-> Index Scan using
pk_user_element on user_element users5_ (cost=0.00..4.19 rows=1 width=8)
Index Cond:
((users5_.user_seqnum = 2) AND (users5_.element_seqnum =
link0_.element_source))
-> Index Scan using pk_element on
element element2_ (cost=0.00..4.12 rows=1 width=8)
Index Cond:
(element2_.element_seqnum = link0_.element_source)
-> Index Scan using pk_user_element on user_element
users3_ (cost=0.00..8.33 rows=1 width=8)
Index Cond: ((users3_.user_seqnum = 2) AND
(users3_.element_seqnum = link0_.element_target))
-> Seq Scan on block block10_ (cost=0.00..1.14 rows=1 width=4)
Filter: (block10_.block_seqnum = 5)
-> Seq Scan on "user" user6_ (cost=0.00..5.89 rows=1 width=4)
Filter: (user6_.user_seqnum = 2)
*
This one is very very very long (was still in process 10 mins later with
100%cpu*): Query Plan for user "10" ("user4_.user_seqnum=10" and
"user6_.user_seqnum=10 ") will be:
QUERY PLAN
Nested Loop (cost=54.34..1490.62 rows=1 width=16)
-> Nested Loop (cost=54.34..1484.72 rows=1 width=20)
Join Filter: (link0_.element_source = blocks9_.element_seqnum)
-> Nested Loop (cost=54.34..1476.41 rows=1 width=32)
-> Nested Loop (cost=54.34..1475.26 rows=1 width=28)
-> Nested Loop (cost=54.34..1466.95 rows=1 width=36)
-> Nested Loop (cost=54.34..1461.05 rows=1
width=40)
-> Nested Loop (cost=54.34..1459.90
rows=1 width=44)
-> Nested Loop
(cost=54.34..1455.52 rows=1 width=36)
-> Nested Loop
(cost=13.15..1410.30 rows=1 width=24)
-> Nested Loop
(cost=0.00..16.63 rows=1 width=16)
-> Index Scan
using fki_element_block_block on element_block blocks7_
(cost=0.00..8.29 rows=1 width=8)
Index Cond:
(block_seqnum = 5)
-> Index Scan
using pk_user_element on user_element users3_ (cost=0.00..8.33 rows=1
width=8)
Index Cond:
((users3_.user_seqnum = 10) AND (users3_.element_seqnum =
blocks7_.element_seqnum))
-> Bitmap Heap Scan on
user_element users5_ (cost=13.15..1387.40 rows=627 width=8)
Recheck Cond:
(users5_.user_seqnum = 10)
-> Bitmap Index
Scan on fki_user_element_user (cost=0.00..12.99 rows=627 width=0)
Index Cond:
(users5_.user_seqnum = 10)
-> Bitmap Heap Scan on link
link0_ (cost=41.19..45.20 rows=1 width=12)
Recheck Cond:
((link0_.element_source = users5_.element_seqnum) AND
(link0_.element_target = users3_.element_seqnum))
Filter:
(link0_.link_sup_date IS NULL)
-> BitmapAnd
(cost=41.19..41.19 rows=1 width=0)
-> Bitmap Index
Scan on element_source_fk (cost=0.00..4.60 rows=21 width=0)
Index Cond:
(link0_.element_source = users5_.element_seqnum)
-> Bitmap Index
Scan on element_target_fk (cost=0.00..35.82 rows=2050 width=0)
Index Cond:
(link0_.element_target = users3_.element_seqnum)
-> Index Scan using pk_element on
element element2_ (cost=0.00..4.37 rows=1 width=8)
Index Cond:
(element2_.element_seqnum = link0_.element_source)
-> Seq Scan on block block8_
(cost=0.00..1.14 rows=1 width=4)
Filter: (block8_.block_seqnum = 5)
-> Seq Scan on "user" user4_ (cost=0.00..5.89
rows=1 width=4)
Filter: (user4_.user_seqnum = 10)
-> Index Scan using pk_element on element element1_
(cost=0.00..8.31 rows=1 width=4)
Index Cond: (element1_.element_seqnum =
link0_.element_target)
-> Seq Scan on block block10_ (cost=0.00..1.14 rows=1
width=4)
Filter: (block10_.block_seqnum = 5)
-> Index Scan using fki_element_block_block on element_block
blocks9_ (cost=0.00..8.29 rows=1 width=8)
Index Cond: (blocks9_.block_seqnum = 5)
-> Seq Scan on "user" user6_ (cost=0.00..5.89 rows=1 width=4)
Filter: (user6_.user_seqnum = 10)
_______________________________________________
Boozter-dev mailing list
Boozter-dev@boozter.com
http://ns355324.ovh.net/mailman/listinfo/boozter-dev
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: <4B755913.7090004@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