PostgreSQL Antipatterns: куда крутить NULLS

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

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

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 раз хуже по производительности, «слетел» с индекса и теперь вычитывает всю таблицу целиком:

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

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’ами все-таки надо исключить, но зато использует он ровно тот же самый индекс.