Re: odd planner again, pg 9.0.8

On 7/25/12 6:39 PM, Tom Lane wrote:
> Marcus Engene <mengpg2@engene.se> writes:
>> Lacking index hints I have a bit of a problem with a slow select.
> I don't think you need index hints.  What you probably do need is to
> increase join_collapse_limit and/or from_collapse_limit to deal with
> this complex query as a whole.
>
>> There are several selects looking similar to this in our application
>> that suddenly jumped from a handfull of ms to many seconds.
> Perhaps you had those settings adjusted properly and somebody turned
> them off again?
>
>             regards, tom lane
>
Wonderful mr Lane, now the query executes amazingly fast! I increased
from_collapse_limit from it default 8 to 10 and it behaves as expected.

Thank you!
Marcus

  Sort  (cost=10628.68..10631.95 rows=1307 width=89) (actual
time=26.430..26.493 rows=919 loops=1)
    Sort Key: ppcr.item_common
    Sort Method:  quicksort  Memory: 154kB
    ->  Nested Loop  (cost=0.00..10561.03 rows=1307 width=89) (actual
time=0.093..25.612 rows=919 loops=1)
          ->  Nested Loop  (cost=0.00..3433.41 rows=460 width=85)
(actual time=0.061..13.257 rows=919 loops=1)
                ->  Nested Loop Left Join  (cost=0.00..3134.45 rows=460
width=85) (actual time=0.057..10.972 rows=919 loops=1)
                      ->  Nested Loop Left Join (cost=0.00..2706.99
rows=460 width=32) (actual time=0.053..9.092 rows=919 loops=1)
                            ->  Nested Loop  (cost=0.00..2391.21
rows=460 width=20) (actual time=0.047..6.964 rows=919 loops=1)
                                  ->  Nested Loop (cost=0.00..1212.82
rows=460 width=12) (actual time=0.039..3.756 rows=919 loops=1)
                                        ->  Nested Loop
(cost=0.00..36.70 rows=460 width=4) (actual time=0.028..0.436 rows=919
loops=1)
                                              Join Filter: (ppc.objectid
= ppcr.pic_curate)
                                              ->  Seq Scan on pic_curate
ppc  (cost=0.00..1.02 rows=1 width=4) (actual time=0.006..0.006 rows=1
loops=1)
                                                    Filter:
(user_curator = 2)
                                              ->  Seq Scan on
pic_curate_row ppcr  (cost=0.00..24.19 rows=919 width=8) (actual
time=0.019..0.147 rows=919 loops=1)
                                        ->  Index Scan using
uploading_x2 on uploading pul  (cost=0.00..2.54 rows=1 width=8) (actual
time=0.003..0.003 rows=1 loops=919)
                                              Index Cond:
(pul.item_common = ppcr.item_common)
                                  ->  Index Scan using item_common_pkey
on item_common pic  (cost=0.00..2.55 rows=1 width=8) (actual
time=0.003..0.003 rows=1 loops=919)
                                        Index Cond: (pic.objectid =
ppcr.item_common)
                            ->  Index Scan using item_movieclip_pkey on
item_movieclip pim  (cost=0.00..0.67 rows=1 width=16) (actual
time=0.002..0.002 rows=0 loops=919)
                                  Index Cond: (pim.item_common =
pic.objectid)
                      ->  Index Scan using item_image_pkey on item_image
pii  (cost=0.00..0.92 rows=1 width=57) (actual time=0.002..0.002 rows=0
loops=919)
                            Index Cond: (pii.item_common = pic.objectid)
                ->  Index Scan using user_pkey on user pu
(cost=0.00..0.64 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=919)
                      Index Cond: (pu.objectid = pic.user)
          ->  Index Scan using pricing_x1 on pricing pp (cost=0.00..3.63
rows=3 width=12) (actual time=0.004..0.004 rows=1 loops=919)
                Index Cond: (pp.item_common = ppcr.item_common)
                Filter: ((date_trunc('sec'::text, now()) >=
pp.startdate) AND (date_trunc('sec'::text, now()) <= pp.stopdate))
          SubPlan 1
            ->  Index Scan using codec_gfx_pkey on codec_gfx pcg
(cost=0.00..2.26 rows=1 width=27) (actual time=0.000..0.000 rows=0
loops=919)
                  Index Cond: (objectid = $0)
          SubPlan 2
            ->  Seq Scan on item_snd pis  (cost=0.00..1.90 rows=1
width=15) (actual time=0.007..0.008 rows=0 loops=919)
                  Filter: (objectid = $1)
  Total runtime: 26.795 ms
(34 rows)