Re: significant slow down with various LIMIT

From: Kevin Grittner
Subject: Re: significant slow down with various LIMIT
Date: ,
Msg-id: 4BC4627202000025000307E5@gw.wicourts.gov
(view: Whole thread, Raw)
In response to: Re: significant slow down with various LIMIT  (norn)
Responses: Re: significant slow down with various LIMIT  (Chris Bowlby)
Re: significant slow down with various LIMIT  ("Kevin Grittner")
List: pgsql-performance

Tree view

significant slow down with various LIMIT  (Helio Campos Mello de Andrade, )
 Re: significant slow down with various LIMIT  ("Kevin Grittner", )
  Re: significant slow down with various LIMIT  (norn, )
   Re: significant slow down with various LIMIT  ("Kevin Grittner", )
  Re: significant slow down with various LIMIT  (norn, )
   Re: significant slow down with various LIMIT  ("Kevin Grittner", )
    Re: significant slow down with various LIMIT  (Chris Bowlby, )
    Re: significant slow down with various LIMIT  ("Kevin Grittner", )
  Re: significant slow down with various LIMIT  (norn, )
   Re: significant slow down with various LIMIT  ("Kevin Grittner", )
    Re: significant slow down with various LIMIT  ("Kevin Grittner", )
  Re: significant slow down with various LIMIT  (norn, )
  Re: significant slow down with various LIMIT  (norn, )

norn <> 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


pgsql-performance by date:

From: Alvaro Herrera
Date:
Subject: Re: stats collector suddenly causing lots of IO
From: Krzysztof Kardas
Date:
Subject: Re: PostgreSQL with Zabbix - problem of newbe