significant slow down with various LIMIT

Поиск
Список
Период
Сортировка
От norn
Тема significant slow down with various LIMIT
Дата
Msg-id 9587baca-c902-4215-9863-7043802ec27e@10g2000yqq.googlegroups.com
обсуждение исходный текст
Ответы Re: significant slow down with various LIMIT  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Re: significant slow down with various LIMIT  (Robert Haas <robertmhaas@gmail.com>)
Re: significant slow down with various LIMIT  (Helio Campos Mello de Andrade <helio.campos@gmail.com>)
Список pgsql-performance
Hi there!

I have some mysterious slow downs with ORDER BY and LIMIT. When LIMIT
getting greater than some value (greater than 3 in my case), query
takes 4-5 secs instead of 0.25ms. All of the necessary indexes are in
place. I have no idea what to do, so any advices are welcome!

Here my queries and explain analyzes;

First Query with LIMIT 3 (fast)
-------------
explain analyze SELECT core_object.id from "core_object" INNER JOIN
"plugins_plugin_addr" ON ("core_object"."id" =
"plugins_plugin_addr"."oid_id") INNER JOIN "plugins_guide_address" ON
("plugins_plugin_addr"."address_id" = "plugins_guide_address"."id")
WHERE "plugins_guide_address"."city_id" = 4535  ORDER BY
"core_object"."id" DESC LIMIT 3;

 Limit  (cost=0.00..9.57 rows=3 width=4) (actual time=0.090..0.138
rows=3 loops=1)
   ->  Merge Join  (cost=0.00..1098182.56 rows=344125 width=4) (actual
time=0.088..0.136 rows=3 loops=1)
         Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
         ->  Nested Loop  (cost=0.00..972804.02 rows=344125 width=4)
(actual time=0.056..0.095 rows=3 loops=1)
               ->  Index Scan Backward using
plugins_plugin_addr_oid_id on plugins_plugin_addr
(cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..0.032
rows=3 loops=1)
               ->  Index Scan using plugins_guide_address_pkey on
plugins_guide_address  (cost=0.00..0.56 rows=1 width=4) (actual
time=0.017..0.018 rows=1 loops=3)
                     Index Cond: (plugins_guide_address.id =
plugins_plugin_addr.address_id)
                     Filter: (plugins_guide_address.city_id = 4535)
         ->  Index Scan using core_object_pkey_desc on core_object
(cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.026..0.028
rows=3 loops=1)
 Total runtime: 0.244 ms
(10 rows)

Second Query, the same, but with LIMIT 4 (slooooow)
-------------
explain analyze SELECT core_object.id from "core_object" INNER JOIN
"plugins_plugin_addr" ON ("core_object"."id" =
"plugins_plugin_addr"."oid_id") INNER JOIN "plugins_guide_address" ON
("plugins_plugin_addr"."address_id" = "plugins_guide_address"."id")
WHERE "plugins_guide_address"."city_id" = 4535  ORDER BY
"core_object"."id" DESC LIMIT 4;

 Limit  (cost=0.00..12.76 rows=4 width=4) (actual time=0.091..4436.795
rows=4 loops=1)
   ->  Merge Join  (cost=0.00..1098182.56 rows=344125 width=4) (actual
time=0.089..4436.791 rows=4 loops=1)
         Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
         ->  Nested Loop  (cost=0.00..972804.02 rows=344125 width=4)
(actual time=0.056..3988.249 rows=4 loops=1)
               ->  Index Scan Backward using
plugins_plugin_addr_oid_id on plugins_plugin_addr
(cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..329.942
rows=1244476 loops=1)
               ->  Index Scan using plugins_guide_address_pkey on
plugins_guide_address  (cost=0.00..0.56 rows=1 width=4) (actual
time=0.003..0.003 rows=0 loops=1244476)
                     Index Cond: (plugins_guide_address.id =
plugins_plugin_addr.address_id)
                     Filter: (plugins_guide_address.city_id = 4535)
         ->  Index Scan using core_object_pkey_desc on core_object
(cost=0.00..113516.08 rows=3091134 width=4) (actual
time=0.027..284.195 rows=1244479 loops=1)
 Total runtime: 4436.894 ms
(10 rows)

В списке pgsql-performance по дате отправления:

Предыдущее
От: Joel Jacobson
Дата:
Сообщение: Re: LIMIT causes planner to do Index Scan using a less optimal index
Следующее
От: "Sabin Coanda"
Дата:
Сообщение: How check execution plan of a function