Периодически приходится разбирать случаи внезапного промаха запроса мимо "вроде бы подходящего" индекса - а все дело оказывается в чуть-чуть не той сортировке.
Давайте посмотрим на примере:
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'ами все-таки надо исключить, но зато использует он ровно тот же самый индекс.