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