Re: significant slow down with various LIMIT

From: Chris Bowlby <excalibur(at)accesswave(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: significant slow down with various LIMIT
Date: 2010-04-13 17:59:55
Message-ID: 4BC4B11B.6090106@accesswave.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(dot)perliev(at)gmail(dot)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
>
>

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Pierre C 2010-04-13 18:09:27 Re: count is ten times faster
Previous Message Kevin Grittner 2010-04-13 17:24:18 Re: significant slow down with various LIMIT