Re: Almost infinite query -> Different Query Plan when changing where clause value - Mailing list pgsql-performance

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


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=357563loops=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.650rows=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.823rows=15 loops=1) 
                                      ->  Seq Scan on "user" user4_  (cost=0.00..5.89 rows=1 width=4) (actual
time=0.012..0.051rows=1 loops=1) 
                                            Filter: (user_seqnum = 2)
                                      ->  Nested Loop  (cost=2881.38..15264.14 rows=1025 width=16) (actual
time=9.549..212.724rows=15 loops=1) 
                                            ->  Seq Scan on block block8_  (cost=0.00..1.14 rows=1 width=4) (actual
time=0.004..0.009rows=1 loops=1) 
                                                  Filter: (block_seqnum = 5)
                                            ->  Nested Loop  (cost=2881.38..15252.75 rows=1025 width=20) (actual
time=9.540..212.673rows=15 loops=1) 
                                                  ->  Hash Join  (cost=2881.38..6613.08 rows=1434 width=16) (actual
time=9.522..212.453rows=15 loops=1) 
                                                        Hash Cond: (users3_.element_seqnum = blocks7_.element_seqnum)
                                                        ->  Bitmap Heap Scan on user_element users3_
(cost=1091.43..4090.04rows=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.46rows=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.079rows=15 loops=1) 
                                                              ->  Bitmap Heap Scan on element_block blocks7_
(cost=116.50..1705.72rows=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.81rows=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.01rows=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=60568loops=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=419895loops=1) 
                                      ->  Seq Scan on element element2_  (cost=0.00..7552.95 rows=419895 width=8)
(actualtime=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.058rows=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)
(actualtime=0.028..0.028 rows=15 loops=1) 
                                Index Cond: (block_seqnum = 5)
Total runtime: 8731.494 ms