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
(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.)
DROP INDEX plugins_plugin_addr_oid_id;
explain analyze <your query>
(3) Try it like this (untested, so you may need to fix it up):
from (SELECT id, city_id FROM "plugins_guide_address")
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
In response to
pgsql-performance by date
|Next:||From: Chris Bowlby||Date: 2010-04-13 17:59:55|
|Subject: Re: significant slow down with various LIMIT|
|Previous:||From: Chris||Date: 2010-04-13 17:01:06|
|Subject: stats collector suddenly causing lots of IO|