Almost infinite query -> Different Query Plan when changing where clause value - Mailing list 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@boozter.com
http://ns355324.ovh.net/mailman/listinfo/boozter-dev