Re: odd planner again, pg 9.0.8

On 7/25/12 6:39 PM, Tom Lane wrote:
> Marcus Engene <mengpg2(at)engene(dot)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)