Почему добавление ORDER BY значительно ускоряет запрос?
Я обнаружил очень странное и нелогичное поведение в PostgreSQL.
У меня есть структура запроса следующим образом. Я выбираю идентификаторы и количество из подзапроса. Подзапрос выполняет фильтрацию, объединение, подсчет, но только упорядочивает по идентификаторам, так как я использую DISTINCT ON() для получения только уникальных идентификаторов.
Внешний запрос затем выполняет правильное упорядочение, а также любые ограничения и смещения по мере необходимости. Вот пример того, как выглядит структура запроса:
SELECT s.id, s.item_count
FROM (
SELECT DISTINCT ON (work_items.id) work_items.id
, work_item_states.disposition AS disposition
, COUNT(work_items.id) OVER () AS item_count
FROM work_items
JOIN work_item_states ON work_item_states.work_item_refer = work_items.id
WHERE work_item_states.disposition = 'cancelled'
ORDER BY work_items.id
) AS s
ORDER BY s.disposition
LIMIT 50
OFFSET 0
Однако я обнаружил нечто странное. В моей базе данных несколько миллионов записей, поэтому общие запросы не самые быстрые. Но когда я удаляю предложение ORDER BY в запросе OUTER, это резко замедляет время запроса.
Однако, если я также удалю предложение LIMIT, оно снова станет быстрым, несмотря на то, что этот пример запроса возвращает 800 000+ результатов.
Таким образом, для внешнего запроса:
ЗАКАЗАТЬ ПО И ОГРАНИЧЕНИЮ - быстро
...
) AS s
ORDER BY s.disposition
LIMIT 50
OFFSET 0
ТОЛЬКО ОГРАНИЧЕНИЕ - очень медленно
...
) AS s
LIMIT 50
OFFSET 0
ONLY ORDER BY - Быстро, несмотря на 800 000 результатов
...
) AS s
ORDER BY s.disposition
OFFSET 0
НИ НИКОГДА - Быстро, несмотря на 800 000 результатов
...
) AS s
OFFSET 0
Чтобы дать представление о том, насколько медленнее только предложение LIMIT с обоими, ни одним, или просто ORDER BY, запросы занимают не более 10 секунд.
Однако только с предложением LIMIT запросы занимают около 15 минут, что в 7 раз больше!
Можно подумать, что ORDER BY вместо этого замедлит работу, поскольку он должен отсортировать результаты подзапроса, но, похоже, это не так. Это очень нелогично.
Если кто-то знает, что здесь происходит за кулисами, я был бы очень признателен, если бы он пролил некоторый свет на это.
Спасибо
РЕДАКТИРОВАТЬ - Добавлены планы выполнения для заявлений:
ЗАКАЗАТЬ ПО И ПРЕДЕЛИТЬ план выполнения
Limit (cost=518486.52..518486.65 rows=50 width=53)
-> Sort (cost=518486.52..520495.59 rows=803628 width=53)
Sort Key: s.disposition
-> Subquery Scan on s (cost=479736.16..491790.58 rows=803628 width=53)
-> Unique (cost=479736.16..483754.30 rows=803628 width=53)
-> Sort (cost=479736.16..481745.23 rows=803628 width=53)
Sort Key: work_items.id
-> WindowAgg (cost=136262.98..345979.65 rows=803628 width=53)
-> Hash Join (cost=136262.98..335934.30 rows=803628 width=45)
Hash Cond: (work_items.id = work_item_states.work_item_refer)
-> Seq Scan on work_items (cost=0.00..106679.48 rows=4020148 width=37)
-> Hash (cost=119152.97..119152.97 rows=803681 width=45)
-> Bitmap Heap Scan on work_item_states (cost=18968.96..119152.97 rows=803681 width=45)
Recheck Cond: (disposition = 'cancelled'::text)
-> Bitmap Index Scan on idx_work_item_states_disposition (cost=0.00..18768.04 rows=803681 width=0)
Index Cond: (disposition = 'cancelled'::text)
Только план выполнения LIMIT
Limit (cost=1.11..69.52 rows=50 width=45)
-> Subquery Scan on s (cost=1.11..1099599.17 rows=803628 width=45)
-> Unique (cost=1.11..1091562.89 rows=803628 width=77)
-> WindowAgg (cost=1.11..1089553.82 rows=803628 width=77)
-> Merge Join (cost=1.11..1079508.47 rows=803628 width=37)
Merge Cond: (work_items.id = work_item_states.work_item_refer)
-> Index Only Scan using idx_work_items_id on work_items (cost=0.56..477365.14 rows=4020148 width=37)
-> Index Scan using idx_work_item_states_work_item_refer on work_item_states (cost=0.56..582047.48 rows=803681 width=37)
Filter: (disposition = 'cancelled'::text)
Только ЗАКАЗАТЬ план выполнения
Sort (cost=625547.09..627556.16 rows=803628 width=53)
Sort Key: s.disposition
-> Subquery Scan on s (cost=479736.16..491790.58 rows=803628 width=53)
-> Unique (cost=479736.16..483754.30 rows=803628 width=53)
-> Sort (cost=479736.16..481745.23 rows=803628 width=53)
Sort Key: work_items.id
-> WindowAgg (cost=136262.98..345979.65 rows=803628 width=53)
-> Hash Join (cost=136262.98..335934.30 rows=803628 width=45)
Hash Cond: (work_items.id = work_item_states.work_item_refer)
-> Seq Scan on work_items (cost=0.00..106679.48 rows=4020148 width=37)
-> Hash (cost=119152.97..119152.97 rows=803681 width=45)
-> Bitmap Heap Scan on work_item_states (cost=18968.96..119152.97 rows=803681 width=45)
Recheck Cond: (disposition = 'cancelled'::text)
-> Bitmap Index Scan on idx_work_item_states_disposition (cost=0.00..18768.04 rows=803681 width=0)
Index Cond: (disposition = 'cancelled'::text)
1 ответ
Вы не опубликовали свои планы выполнения, но у меня есть готовый хрустальный шар, так что я угадаю, что происходит.
Во втором, очень медленном запросе у оптимизатора есть отличная идея, как сделать это быстро: он сканирует work_items используя индекс на idизвлекает все соответствующие строки из work_item_states во вложенном цикле и отфильтровывает все, что не соответствует work_item_states.disposition = 'cancelled' пока он не нашел 50 отличных результатов.
Это хорошая идея, но оптимизатор не знает, что все строки с work_item_states.disposition = 'cancelled' матч work_items с высоким idпоэтому он должен сканировать вечно, пока не найдет свои 50 строк.
Все остальные запросы не позволяют планировщику выбрать эту стратегию, потому что она обещает, только если несколько строк в work_items.id заказ будет делать.