PostgreSQL Antipatterns: куда крутить NULLS07.07.2022 11:31

d9260854e873b95429f6bc85ae4b8242.png

Периодически приходится разбирать случаи внезапного промаха запроса мимо «вроде бы подходящего» индекса -, а все дело оказывается в чуть-чуть не той сортировке.

Давайте посмотрим на примере: 

CREATE TABLE tstord AS
SELECT
  i
, CASE
    WHEN random() < 0.99 THEN (random() * 1e6)::integer -- 1% NULLs
  END val
FROM
  generate_series(1, 1e6) i;

CREATE INDEX ON tstord(val); -- стандартный индекс

Давайте получим первую 1000 минимальных значений таблицы: 

SELECT
  *
FROM
  tstord
ORDER BY
  val
LIMIT 1000;

Выдаст он ровно, что мы хотим, и достаточно быстро: 

Limit (actual time=0.021..0.671 rows=1000 loops=1)
  Buffers: shared hit=1005
  ->  Index Scan using tstord_val_idx on tstord (actual time=0.020..0.606 rows=1000 loops=1)
        Buffers: shared hit=1005

Но что если мы захотим получить 1000 максимальных значений? 

SELECT
  *
FROM
  tstord
ORDER BY
  val DESC -- обратная сортировка
LIMIT 1000;
Limit (actual time=0.020..0.506 rows=1000 loops=1)
  Buffers: shared hit=477
  ->  Index Scan Backward using tstord_val_idx on tstord (actual time=0.019..0.441 rows=1000 loops=1)
        Buffers: shared hit=477

Получилось даже еще быстрее, да и данных прочитать пришлось вдвое меньше! Какой PostgreSQL молодец, как он удачно использовал обратный проход по индексу Index Scan Backward!

Только вот получили мы что-то не совсем то — одни NULL: 

  i    | val
995623 | [NULL]
995628 | [NULL]
995687 | [NULL]
...

Обратимся к мануалу:

… при прямом сканировании индекса по столбцу x порядок оказывается соответствующим указанию ORDER BY x (или точнее,  ORDER BY x ASC NULLS LAST). Индекс также может сканироваться в обратную сторону, и тогда порядок соответствует указанию ORDER BY x DESC (или точнее,  ORDER BY x DESC NULLS FIRST, так как для ORDER BY DESC подразумевается NULLS FIRST).

Оказывается, все просто — нам надо лишь сказать, чтобы NULL'ы сортировались «в конец», как и в первом запросе: 

SELECT
  *
FROM
  tstord
ORDER BY
  val DESC NULLS LAST -- NULL'ы в конец
LIMIT 1000;

Теперь результат нас вполне устраивает. Или все-таки нет?

Внезапно наш запрос стал в 200 раз хуже по производительности, «слетел» с индекса и теперь вычитывает всю таблицу целиком:

Seq Scan при неудачной сортировкеSeq Scan при неудачной сортировке

Первый выход нам подсказывает сам сервис анализа планов — создать новый индекс с подходящей сортировкой: 

CREATE INDEX CONCURRENTLY "~tstord-55d3ca1e"
  ON tstord(val DESC NULLS LAST);

И да, это конечно же поможет, но такой индекс…

  • периодически будет вычитываться в память и вытеснять оттуда более актуальные данные

  • будет замедлять вставку в нашу таблицу и увеличит дисковую нагрузку на запись

А можно как-то без этого всего?

Так разве нас кто-то заставлял менять сортировку? Нет! Мы всего-то хотели исключить NULL'ы из «топа» нашей выборки — так почему бы не сделать это в явном виде? 

SELECT
  *
FROM
  tstord
WHERE
  val IS NOT NULL -- просто убираем NULL'ы
ORDER BY
  val DESC -- никаких NULLS
LIMIT 1000;
Limit (actual time=0.024..0.741 rows=1000 loops=1)
  Buffers: shared hit=1007
  ->  Index Scan Backward using tstord_val_idx on tstord (actual time=0.023..0.658 rows=1000 loops=1)
        Index Cond: (val IS NOT NULL)
        Buffers: shared hit=1007

Да, такой запрос займет чуть больше времени, поскольку записи с NULL’ами все-таки надо исключить, но зато использует он ровно тот же самый индекс.

© Habrahabr.ru