Re: significant slow down with various LIMIT

От: Kevin Grittner
Тема: Re: significant slow down with various LIMIT
Дата: ,
Msg-id: 4BC4627202000025000307E5@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: significant slow down with various LIMIT  (norn)
Ответы: Re: significant slow down with various LIMIT  (Chris Bowlby)
Re: significant slow down with various LIMIT  ("Kevin Grittner")
Список: pgsql-performance

Скрыть дерево обсуждения

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 по дате сообщения:

От: Alvaro Herrera
Дата:
Сообщение: Re: stats collector suddenly causing lots of IO
От: Krzysztof Kardas
Дата:
Сообщение: Re: PostgreSQL with Zabbix - problem of newbe