PostgreSQL Antipatterns: убираем медленные и ненужные сортировки07.10.2020 19:48

«Просто так» результат SQL-запроса возвращает записи в том порядке, который наиболее удобен серверу СУБД. Но человек гораздо лучше воспринимает хоть как-то упорядоченные данные — это помогает быстро сравнивать соответствие различных датасетов.

Поэтому со временем у разработчика может выработаться рефлекс «Дай-ка я на всякий случай это вот отсортирую!» Конечно, иногда подобная сортировка бывает оправдана прикладными задачами, но обычно такой случай выглядит как в старом анекдоте:

Программист ставит себе на тумбочку перед сном два стакана. Один с водой — на случай, если захочет ночью пить. А второй пустой — на случай, если не захочет.

Давайте разбираться — когда сортировка в запросе точно не нужна и несет с собой потерю производительности, когда от нее можно относительно дешево избавиться, а когда сделать из нескольких — одну.

kwyzcgdzcsj-cwbied-cr84brwe.png


#1: Нехватка work_mem


Обычно мы просто не обращаем внимания на наличие лишнего Sort-узла в плане — он отрабатывает очень быстро по сравнению с самим извлечением данных. Но стоит чему-то пойти не так и перестать сортируемой выборке умещаться в памяти…

SELECT generate_series(1, 1e6) i ORDER BY i;


nzusv8rlbaxb_fepjstft1qa8uu.png

Из-за сортировки мы начали «свапаться» на диск (buffers temp written), и потратили на это порядка 70% времени!

Давайте как-то ускорять. И самый простой способ, который рекомендует нам подсказка под иконкой восклицательного знака — просто увеличить параметр work_mem так, чтобы памяти на сортировку все-таки хватало: 

ALTER SYSTEM SET work_mem = '128MB';


lfi15q74hxgffhyyifabxib2fho.png

На четверть быстрее, хотя мы не трогали ни текст запроса, ни структуру базы! К сожалению, такой способ не всегда допустим — например, наш СБИС обслуживает одновременно тысячи клиентов в рамках одного узла PostgreSQL, и просто так раздавать память направо-налево мы не можем себе позволить. Может, есть способ вообще избавиться от сортировки?…

#2: Сортировка уже отсортированного


Начнем с самого простого варианта — чтения данных из вложенного запроса: 

SELECT
  *
FROM
  (
    SELECT generate_series(1, 1e6) i
  ) T
ORDER BY
  i;


Почти 2/3 всего времени выполнения заняла сортировка, хоть и происходила вся в памяти:
itux-sdbvd6fg1xh3d2kkgc1f5a.png

Но был ли в ней вообще смысл? Нет, потому что чтение из вложенного запроса сохраняет порядок записей и так. Поправим: 

SELECT
  *
FROM
  (
    SELECT generate_series(1, 1e6) i
  ) T;


najxfgvpaj0hdbeopvdd5y_kndi.png

Вроде мы ничего особенного не сделали, а запрос уже ускорился более чем в 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;


yjp6gpzse41j-4d8ha2_6bhgxve.png

Мы-то понимаем, что «внутренняя» сортировка ни на что не влияет (в большинстве случаев), но СУБД — нет. Поправим: 

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;


jzwn93bl-nywarzrjvxcpfsnccs.png

Минус одна сортировка. Но вспомним предыдущий пункт насчет упорядоченности SRF, и исправим до конца: 

SELECT
  i
, 1e6 - i
FROM
  generate_series(1, 1e6) i
WHERE
  (i % 2, i % 3, i % 5, i % 7) = (1, 2, 4, 6);


kifp1wbrvc4n-2ngxgltchxgyxy.png

Вот и минус вторая сортировка. На данном конкретном запросе мы выиграли порядка 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;


gxeb1uff9kjbc_nmy4mlbwkclwq.png

Н-да… Прочитать почти мегабайт данных ради одного числа — это сильно. Но давайте расширим индекс полем сортировки, используемой в запросе: 

DROP INDEX tbl_fk_idx;
CREATE INDEX ON tbl(fk, ts DESC);


jpeydlkrqgig_r1ou5l_haloydk.png

Ух! Теперь весь наш запрос выполнился быстрее, чем одна только сортировка в предыдущем варианте.

#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;


wo4gucop0q7mu2gqhk0ii5cnffm.png

Вроде и индекс у нас есть, вроде и сортировка по нужным ключам, но как-то все некрасиво в плане… Но давайте воспользуемся знанием, что чтение из вложенного запроса сохраняет порядок — разделим нашу выборку на две заведомо непересекающиеся и снова «склеим» с помощью 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;


jeaxjrpvumfrsabwzrbds0fmvmw.png

И — снова ни одной сортировки в плане, а запрос стал почти в 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


laa8eq-hy48rjqipuzpqxxvf19s.png

Сначала мы сортируем по (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;


n8dy_oeu_rdn8ryndqrlqjhd3w8.png

Так, от одной сортировки избавились. Правда, из-за этого теперь стали читать чуть больше 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;


ijrn9jjy0gylcxibw1d-wkbwbnu.png

Итого — тот же результат в 2.5 раза быстрее, и без единой лишней сортировки.

Bonus: Полезные сортировки, которые не происходят


Давайте продолжим экспериментировать, и попробуем получить полный список клиентов со временем последней заявки для каждого, раз у нас есть подходящий индекс: 

SELECT DISTINCT ON(fk)
  *
FROM
  tbl
ORDER BY
  fk, ts DESC;


9bgar66wr6uan9v5on3mpnsfxp4.png

Прочитать почти 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;


gtmxuzxamv08mpuzccvna_fxr78.png

Получилось в 12 раз быстрее, потому что мы не читали ничего лишнего, в отличие от Index Only Scan. Хоть мы и использовали дважды в запросе ORDER BY, ни одной сортировки в плане так и не появилось.

Вероятно, в будущих версиях PostgreSQL для таких «точечных» чтений появится соответствующий тип узла Index Skip Scan. Но скоро его ждать не стоит.

Замечу, что результат этих запросов все-таки немного отличается — второй не обрабатывает записи с fk IS NULL. Но кому надо — извлечет их отдельно.

Знаете другие способы устранения лишних сортировок? Напишите в комментариях!

© Habrahabr.ru