Message-ID: <501036EB.4080206@engene.se>
Date: Wed, 25 Jul 2012 20:11:55 +0200
From: Marcus Engene <mengpg2@engene.se>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.7;
	rv:13.0) Gecko/20120614 Thunderbird/13.0.1
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: odd planner again, pg 9.0.8
References: <50101269.2020209@engene.se> <27536.1343234349@sss.pgh.pa.us>
In-Reply-To: <27536.1343234349@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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)