Поэтому со временем у разработчика может выработаться рефлекс Дай-ка я на всякий случай это вот отсортирую! Конечно, иногда подобная сортировка бывает оправдана прикладными задачами, но обычно такой случай выглядит как в старом анекдоте:
Программист ставит себе на тумбочку перед сном два стакана. Один с водой на случай, если захочет ночью пить. А второй пустой на случай, если не захочет.Давайте разбираться когда сортировка в запросе точно не нужна и несет с собой потерю производительности, когда от нее можно относительно дешево избавиться, а когда сделать из нескольких одну.
#1: Нехватка work_mem
Обычно мы просто не обращаем внимания на наличие лишнего
Sort-узла в плане он отрабатывает очень быстро по
сравнению с самим извлечением данных. Но стоит чему-то пойти не так
и перестать сортируемой выборке умещаться в памяти
SELECT generate_series(1, 1e6) i ORDER BY i;
Из-за сортировки мы начали свапаться на диск (
buffers temp
written), и потратили на это порядка 70% времени!Давайте как-то ускорять. И самый простой способ, который рекомендует нам подсказка под иконкой восклицательного знака просто увеличить параметр work_mem так, чтобы памяти на сортировку все-таки хватало:
ALTER SYSTEM SET work_mem = '128MB';
На четверть быстрее, хотя мы не трогали ни текст запроса, ни структуру базы! К сожалению, такой способ не всегда допустим например, наш СБИС обслуживает одновременно тысячи клиентов в рамках одного узла PostgreSQL, и просто так раздавать память направо-налево мы не можем себе позволить. Может, есть способ вообще избавиться от сортировки?..
#2: Сортировка уже отсортированного
Начнем с самого простого варианта чтения данных из вложенного запроса:
SELECT *FROM ( SELECT generate_series(1, 1e6) i ) TORDER BY i;
Почти 2/3 всего времени выполнения заняла сортировка, хоть и происходила вся в памяти:
Но был ли в ней вообще смысл? Нет, потому что чтение из вложенного запроса сохраняет порядок записей и так. Поправим:
SELECT *FROM ( SELECT generate_series(1, 1e6) i ) T;
Вроде мы ничего особенного не сделали, а запрос уже ускорился более чем в 2 раза.
Этим же свойством сохранения порядка записей обладают чтение из CTE (Common Table Expression, узелCTE Scanв плане), SRF (Set-Returning Function,Function Scan) или VALUES (Values Scan).
#3: Вложенная отладочная сортировка
Следующий пример обычно возникает в результате отладки, когда разработчик сначала проверял внутренний запрос, а потом вставил его в обвязку внешнего:
SELECT i, 1e6 - iFROM ( SELECT * FROM generate_series(1, 1e6) i WHERE (i % 2, i % 3, i % 5, i % 7) = (1, 2, 4, 6) ORDER BY -- осталось от отладки i DESC ) TORDER BY i;
Мы-то понимаем, что внутренняя сортировка ни на что не влияет (в большинстве случаев), но СУБД нет. Поправим:
SELECT i, 1e6 - iFROM ( SELECT * FROM generate_series(1, 1e6) i WHERE (i % 2, i % 3, i % 5, i % 7) = (1, 2, 4, 6) ) TORDER BY i;
Минус одна сортировка. Но вспомним предыдущий пункт насчет упорядоченности SRF, и исправим до конца:
SELECT i, 1e6 - iFROM generate_series(1, 1e6) iWHERE (i % 2, i % 3, i % 5, i % 7) = (1, 2, 4, 6);
Вот и минус вторая сортировка. На данном конкретном запросе мы выиграли порядка 5%, всего лишь убрав лишнее.
#4: Index Scan вместо сортировки
Одна из классических причин неэффективности SQL-запросов, о которых я рассказывал в статье Рецепты для хворающих SQL-запросов:
CREATE TABLE tbl ASSELECT (random() * 1e4)::integer fk -- 10K разных внешних ключей, now() - ((random() * 1e8) || ' sec')::interval tsFROM generate_series(1, 1e6) pk; -- 1M "фактов"CREATE INDEX ON tbl(fk); -- индекс для foreign key
То есть при разработке структуры базы мы описали FOREIGN KEY, повесили индекс на это поле, чтобы он отрабатывал быстро А потом пошли прикладные задачи.
Например, мы хотим узнать, когда был выставлен последний счет по конкретному клиенту:
SELECT tsFROM tblWHERE fk = 1 -- отбор по конкретной связиORDER BY ts DESC -- хотим всего одну "последнюю" записьLIMIT 1;
Н-да Прочитать почти мегабайт данных ради одного числа это сильно. Но давайте расширим индекс полем сортировки, используемой в запросе:
DROP INDEX tbl_fk_idx;CREATE INDEX ON tbl(fk, ts DESC);
Ух! Теперь весь наш запрос выполнился быстрее, чем одна только сортировка в предыдущем варианте.
#5: UNION ALL вместо сортировки
Но что делать, если от нас хотят такую сортировку, которая ну никак нормально на индекс не укладывается, хотя вроде и должна?
TRUNCATE TABLE tbl;INSERT INTO tblSELECT CASE WHEN random() >= 1e-5 THEN (random() * 1e4)::integer END fk -- 10K разных внешних ключей, из них 0.0001 - NULL'ы, now() - ((random() * 1e8) || ' sec')::interval tsFROM generate_series(1, 1e6) pk; -- 1M "фактов"
Допустим, что нам надо показать оператору топовые 10 заявок сначала все неназначенные заявки (
fk IS NULL), начиная от
самых старых, а затем все его (fk = 1):
SELECT *FROM tblWHERE fk IS NULL OR fk = 1ORDER BY fk NULLS FIRST, ts DESCLIMIT 10;
Вроде и индекс у нас есть, вроде и сортировка по нужным ключам, но как-то все некрасиво в плане Но давайте воспользуемся знанием, что чтение из вложенного запроса сохраняет порядок разделим нашу выборку на две заведомо непересекающиеся и снова склеим с помощью
UNION ALL, примерно как делали это в статье
PostgreSQL Antipatterns: сказ об итеративной доработке поиска по
названию, или Оптимизация туда и обратно:
( SELECT * FROM tbl WHERE fk IS NULL ORDER BY fk, ts DESC LIMIT 10)UNION ALL( SELECT * FROM tbl WHERE fk = 1 ORDER BY fk, ts DESC LIMIT 10)LIMIT 10;
И снова ни одной сортировки в плане, а запрос стал почти в 5 раз быстрее.
#6: Сортировки для оконных функций
Давайте теперь попробуем посчитать сразу по первым 10 клиентам одновременно количество заявок и время последней с помощью оконных функций:
SELECT DISTINCT ON(fk) *, count(*) OVER(PARTITION BY fk ORDER BY ts) -- без DESC!FROM tblWHERE fk < 10ORDER BY fk, ts DESC; -- хотим "последнее" значение ts
Сначала мы сортируем по
(fk, ts) для вычисления
оконного count(*), а потом еще раз по (fk, ts
DESC) для вычисления DISTINCT.Замечу, что если просто написать сортировку как в самом запросе
count(*) OVER(PARTITION BY fk ORDER BY ts
DESC), то будет, конечно, быстрее. Только вот
результат неправильный везде будут одни единички.Но ведь
count, в отличие от разных
first_value/lead/lag/..., вообще не зависит от порядка
записей давайте просто уберем сортировку для него:
SELECT DISTINCT ON(fk) *, count(*) OVER(PARTITION BY fk)FROM tblWHERE fk < 10ORDER BY fk, ts DESC;
Так, от одной сортировки избавились. Правда, из-за этого теперь стали читать чуть больше
buffers, обменяв Bitmap
Heap Scan на Index Only Scan по нашему индексу,
с которым совпадает целевая сортировка зато быстрее!Хм Но ведь и оставшаяся сортировка с ним тоже совпадает. Можно ли и от нее избавиться, не нарушив корректность результата? Вполне! Для этого всего лишь укажем нужную рамку окна по всем записям (
ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING):
SELECT DISTINCT ON(fk) *, count(*) OVER(PARTITION BY fk ORDER BY ts DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)FROM tblWHERE fk < 10ORDER BY fk, ts DESC;
Итого тот же результат в 2.5 раза быстрее, и без единой лишней сортировки.
Bonus: Полезные сортировки, которые не происходят
Давайте продолжим экспериментировать, и попробуем получить полный список клиентов со временем последней заявки для каждого, раз у нас есть подходящий индекс:
SELECT DISTINCT ON(fk) *FROM tblORDER BY fk, ts DESC;
Прочитать почти 8GB данных ради 10K записей как-то многовато Давайте воспользуемся методикой рекурсивного DISTINCT:
WITH RECURSIVE T AS ( ( SELECT fk , ts FROM tbl ORDER BY fk, ts DESC LIMIT 1 -- первая запись с минимальным ключом )UNION ALL SELECT X.* FROM T , LATERAL( SELECT fk , ts FROM tbl WHERE fk > T.fk ORDER BY fk, ts DESC LIMIT 1 -- следующая по индексу запись ) X WHERE T.fk IS NOT NULL)TABLE T;
Получилось в 12 раз быстрее, потому что мы не читали ничего лишнего, в отличие от
Index Only Scan. Хоть мы и
использовали дважды в запросе ORDER BY, ни одной
сортировки в плане так и не появилось.Вероятно, в будущих версиях PostgreSQL для таких
точечных чтений появится соответствующий тип узла
Index Skip Scan. Но скоро его ждать не
стоит.
Замечу, что результат этих запросов все-таки немного отличается
второй не обрабатывает записи с fk IS NULL. Но кому
надо извлечет их отдельно.Знаете другие способы устранения лишних сортировок? Напишите в комментариях!
