Skip site navigation (1) Skip section navigation (2)

Almost infinite query -> Different Query Plan when changing where clause value

From: lionel duboeuf <lionel(dot)duboeuf(at)boozter(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Almost infinite query -> Different Query Plan when changing where clause value
Date: 2010-02-12 13:35:15
Message-ID: 4B755913.7090004@boozter.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)boozter(dot)com
http://ns355324.ovh.net/mailman/listinfo/boozter-dev



Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2010-02-12 14:03:48
Subject: Re: moving pg_xlog -- yeah, it's worth it!
Previous:From: Yeb HavingaDate: 2010-02-12 13:31:22
Subject: Re: Immutable table functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group