Чрезвычайно медленный запрос PostgreSQL с предложениями ORDER и LIMIT

У меня есть таблица, назовите ее "foos", с ней почти 6 миллионов записей. Я выполняю следующий запрос:

SELECT "foos".*
FROM "foos"
INNER JOIN "bars" ON "foos".bar_id = "bars".id
WHERE (("bars".baz_id = 13266))
ORDER BY "foos"."id" DESC
LIMIT 5 OFFSET 0;

Этот запрос занимает очень много времени для запуска (время ожидания Rails во время его запуска). На всех идентификаторах есть индекс. Любопытная часть заключается в том, что если я удалю либо предложение ORDER BY, либо предложение LIMIT, оно выполняется почти мгновенно.

Я предполагаю, что наличие как ORDER BY, так и LIMIT делает PostgreSQL несколько неправильным выбором при планировании запросов. У кого-нибудь есть идеи о том, как это исправить?

В случае, если это помогает, вот EXPLAIN для всех трех случаев:

//////// Both ORDER and LIMIT
SELECT "foos".*
FROM "foos"
INNER JOIN "bars" ON "foos".bar_id = "bars".id
WHERE (("bars".baz_id = 13266))
ORDER BY "foos"."id" DESC
LIMIT 5 OFFSET 0;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..16663.44 rows=5 width=663)
   ->  Nested Loop  (cost=0.00..25355084.05 rows=7608 width=663)
         Join Filter: (foos.bar_id = bars.id)
         ->  Index Scan Backward using foos_pkey on foos  (cost=0.00..11804133.33 rows=4963477 width=663)
               Filter: (((NOT privacy_protected) OR (user_id = 67962)) AND ((status)::text = 'DONE'::text))
         ->  Materialize  (cost=0.00..658.96 rows=182 width=4)
               ->  Index Scan using index_bars_on_baz_id on bars  (cost=0.00..658.05 rows=182 width=4)
                     Index Cond: (baz_id = 13266)
(8 rows)

//////// Just LIMIT
SELECT "foos".*
FROM "foos"
INNER JOIN "bars" ON "foos".bar_id = "bars".id
WHERE (("bars".baz_id = 13266))
LIMIT 5 OFFSET 0;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..22.21 rows=5 width=663)
   ->  Nested Loop  (cost=0.00..33788.21 rows=7608 width=663)
         ->  Index Scan using index_bars_on_baz_id on bars  (cost=0.00..658.05 rows=182 width=4)
               Index Cond: (baz_id = 13266)
         ->  Index Scan using index_foos_on_bar_id on foos  (cost=0.00..181.51 rows=42 width=663)
               Index Cond: (foos.bar_id = bars.id)
               Filter: (((NOT foos.privacy_protected) OR (foos.user_id = 67962)) AND ((foos.status)::text = 'DONE'::text))
(7 rows)

//////// Just ORDER
SELECT "foos".*
FROM "foos"
INNER JOIN "bars" ON "foos".bar_id = "bars".id
WHERE (("bars".baz_id = 13266))
ORDER BY "foos"."id" DESC;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=36515.17..36534.19 rows=7608 width=663)
   Sort Key: foos.id
   ->  Nested Loop  (cost=0.00..33788.21 rows=7608 width=663)
         ->  Index Scan using index_bars_on_baz_id on bars  (cost=0.00..658.05 rows=182 width=4)
               Index Cond: (baz_id = 13266)
         ->  Index Scan using index_foos_on_bar_id on foos  (cost=0.00..181.51 rows=42 width=663)
               Index Cond: (foos.bar_id = bars.id)
               Filter: (((NOT foos.privacy_protected) OR (foos.user_id = 67962)) AND ((foos.status)::text = 'DONE'::text))
(8 rows)

Ответ 1

Когда у вас есть как LIMIT, так и ORDER BY, оптимизатор решил, что быстрее хромает через нефильтрованные записи по foo по убыванию ключа, пока он не получит пять совпадений для остальных критериев. В других случаях он просто запускает запрос в виде вложенного цикла и возвращает все записи.

Оффлайн, я бы сказал, проблема заключается в том, что PG не собирает совместное распределение различных идентификаторов и почему план настолько неоптимальный.

Для возможных решений: я предполагаю, что вы недавно запустили ANALYZE. Если нет, сделайте это. Это может объяснить, почему ваши расчетные времена высоки даже в версии, которая быстро возвращается. Если проблема не устранена, возможно, запустите ORDER BY в качестве подзаголовка и удалите LIMIT во внешнем запросе.

Ответ 2

В плане запроса указан фильтр на

(((NOT privacy_protected) OR (user_id = 67962)) AND ((status)::text = 'DONE'::text))

который не отображается в SELECT - откуда он?

Также обратите внимание, что выражение указано как "Фильтр", а не "Индексный код", который, как представляется, указывает на то, что к нему не применяется индекс.

Ответ 3

Наверное, это происходит потому, что перед тем, как он попытается заказать, тогда выбрать. Почему бы не попытаться отсортировать результат во внешнем select all? Что-то вроде: SELECT * FROM (SELECT... INNER JOIN ETC...) ORDER BY... DESC

Ответ 4

он может запускать сканирование полного стола на "foos". вы пытались изменить порядок таблиц и вместо этого использовать левое соединение вместо внутреннего соединения и посмотреть, будет ли он быстрее показывать результаты.

скажем...

SELECT "bars"."id", "foos".*
FROM "bars"
LEFT JOIN "foos" ON "bars"."id" = "foos"."bar_id"
WHERE "bars"."baz_id" = 13266
ORDER BY "foos"."id" DESC
LIMIT 5 OFFSET 0;