MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII
Message-id: <4BC4B11B.6090106@accesswave.ca>
Date: Tue, 13 Apr 2010 14:59:55 -0300
From: Chris Bowlby <excalibur@accesswave.ca>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.5)
	Gecko/20091130 SUSE/3.0.0-16.1 Thunderbird/3.0
To: pgsql-performance@postgresql.org
Subject: Re: significant slow down with various LIMIT
References: <4BC2C7140200002500030705@gw.wicourts.gov>
	<f4ecdf51-a74f-45f7-9b6d-87244787ca6b@x3g2000yqd.googlegroups.com>
	<4BC34A340200002500030759@gw.wicourts.gov>
	<e1a8a6b8-d5c3-4bf1-8df7-007e5950b698@q23g2000yqd.googlegroups.com>
	<4BC4627202000025000307E5@gw.wicourts.gov>
In-reply-to: <4BC4627202000025000307E5@gw.wicourts.gov>

I'm also wondering if a re-clustering of the table would work based on
the index that's used.

such that:

CLUSTER core_object USING plugins_plugin_addr_oid_id;

and see if that makes any change in the differences that your seeing.

On 04/13/2010 02:24 PM, Kevin Grittner wrote:
> norn <andrey.perliev@gmail.com> wrote:
>  
>   
>> I am wondering why there are so big gap between two limits and how
>> to avoid this...
>>     
>  
> I think we've already established that it is because of the
> percentage of the table which must be scanned to get to the desired
> number of rows.  The problem is exacerbated by the fact that it's a
> "backward" scan on the index, which is slower than a forward scan --
> mainly because disks spin in one direction, and the spacing of the
> sectors is optimized for forward scans.
>  
> There are a couple things to try which will give a more complete
> picture of what might work to make the run time more predictable. 
> Please try these, and run EXPLAIN ANALYZE of your problem query each
> way.
>  
> (1) Try it without the ORDER BY clause and the LIMIT.
>  
> (2) Temporarily take that top index out of consideration.  (Don't
> worry, it'll come back when you issue the ROLLBACK -- just don't
> forget the BEGIN statement.)
>   
> BEGIN;
> DROP INDEX plugins_plugin_addr_oid_id;
> explain analyze <your query>
> ROLLBACK;
>  
> (3) Try it like this (untested, so you may need to fix it up):
>  
> explain analyze
> SELECT core_object.id
>   from (SELECT id, city_id FROM "plugins_guide_address")
>        "plugins_guide_address"
>   JOIN "plugins_plugin_addr"
>     ON ("plugins_plugin_addr"."address_id"
>        = "plugins_guide_address"."id")
>   JOIN "core_object"
>     ON ("core_object"."id" = "plugins_plugin_addr"."oid_id")
>   WHERE "plugins_guide_address"."city_id" = 4535
>   ORDER BY "core_object"."id" DESC
>   LIMIT 4 -- or whatever it normally takes to cause the problem
> ;
>  
> -Kevin
>
>