On 7/25/12 6:39 PM, Tom Lane wrote:
Marcus Engene <mengpg2@xxxxxxxxx> 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)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
