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

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

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