Поэтому со временем у разработчика может выработаться рефлекс «Дай-ка я на всякий случай это вот отсортирую!» Конечно, иногда подобная сортировка бывает оправдана прикладными задачами, но обычно такой случай выглядит как в старом анекдоте:
Программист ставит себе на тумбочку перед сном два стакана. Один с водой — на случай, если захочет ночью пить. А второй пустой — на случай, если не захочет.
Давайте разбираться — когда сортировка в запросе точно не нужна и несет с собой потерю производительности, когда от нее можно относительно дешево избавиться, а когда сделать из нескольких — одну.
#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 ) T ORDER 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 - i FROM ( SELECT * FROM generate_series(1, 1e6) i WHERE (i % 2, i % 3, i % 5, i % 7) = (1, 2, 4, 6) ORDER BY -- осталось от отладки i DESC ) T ORDER BY i; 
Мы-то понимаем, что «внутренняя» сортировка ни на что не влияет (в большинстве случаев), но СУБД — нет. Поправим:
SELECT i , 1e6 - i FROM ( SELECT * FROM generate_series(1, 1e6) i WHERE (i % 2, i % 3, i % 5, i % 7) = (1, 2, 4, 6) ) T ORDER BY i; 
Минус одна сортировка. Но вспомним предыдущий пункт насчет упорядоченности SRF, и исправим до конца:
SELECT i , 1e6 - i FROM generate_series(1, 1e6) i WHERE (i % 2, i % 3, i % 5, i % 7) = (1, 2, 4, 6); 
Вот и минус вторая сортировка. На данном конкретном запросе мы выиграли порядка 5%, всего лишь убрав лишнее.
#4: Index Scan вместо сортировки
Одна из «классических» причин неэффективности SQL-запросов, о которых я рассказывал в статье «Рецепты для хворающих SQL-запросов»:
CREATE TABLE tbl AS SELECT (random() * 1e4)::integer fk -- 10K разных внешних ключей , now() - ((random() * 1e8) || ' sec')::interval ts FROM generate_series(1, 1e6) pk; -- 1M "фактов" CREATE INDEX ON tbl(fk); -- индекс для foreign keyТо есть при разработке структуры базы мы описали FOREIGN KEY, повесили индекс на это поле, чтобы он отрабатывал быстро… А потом пошли прикладные задачи.
Например, мы хотим узнать, когда был выставлен последний счет по конкретному клиенту:
SELECT ts FROM tbl WHERE 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 tbl SELECT CASE WHEN random() >= 1e-5 THEN (random() * 1e4)::integer END fk -- 10K разных внешних ключей, из них 0.0001 - NULL'ы , now() - ((random() * 1e8) || ' sec')::interval ts FROM generate_series(1, 1e6) pk; -- 1M "фактов" Допустим, что нам надо показать оператору «топовые» 10 заявок — сначала все «неназначенные» заявки (fk IS NULL), начиная от самых старых, а затем все «его» (fk = 1):
SELECT * FROM tbl WHERE fk IS NULL OR fk = 1 ORDER BY fk NULLS FIRST, ts DESC LIMIT 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 tbl WHERE fk < 10 ORDER 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 tbl WHERE fk < 10 ORDER 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 tbl WHERE fk < 10 ORDER BY fk, ts DESC; 
Итого — тот же результат в 2.5 раза быстрее, и без единой лишней сортировки.
Bonus: Полезные сортировки, которые не происходят
Давайте продолжим экспериментировать, и попробуем получить полный список клиентов со временем последней заявки для каждого, раз у нас есть подходящий индекс:
SELECT DISTINCT ON(fk) * FROM tbl ORDER 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. Но кому надо — извлечет их отдельно.
Знаете другие способы устранения лишних сортировок? Напишите в комментариях!
ссылка на оригинал статьи https://habr.com/ru/company/tensor/blog/522114/
Добавить комментарий