Skip site navigation (1) Skip section navigation (2)

Re: significant slow down with various LIMIT

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "norn" <andrey(dot)perliev(at)gmail(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: significant slow down with various LIMIT
Date: 2010-04-13 17:24:18
Message-ID: 4BC4627202000025000307E5@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Chris BowlbyDate: 2010-04-13 17:59:55
Subject: Re: significant slow down with various LIMIT
Previous:From: ChrisDate: 2010-04-13 17:01:06
Subject: stats collector suddenly causing lots of IO

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group