Вы создали индекс, но запрос по-прежнему делает Seq Scan? Стоимость в EXPLAIN — загадочное число, и непонятно, как его интерпретировать? Данные с индексом читаются медленнее, чем без него? Разберёмся, как PostgreSQL на самом деле работает с индексами — на живых примерах с 4 миллионами строк.

Это вторая часть серии, основанной на моих внутренних докладах по PostgreSQL. Я долго работал с другим стеком, а вернувшись к PostgreSQL — пересмотрел собственные записи для освежения знаний. Фундаментальная механика индексов не изменилась, но за 5 лет появились полезные инструменты — об этом в конце статьи.

Подготовка: 4 миллиона строк

Для экспериментов создадим таблицу без индексов и первичного ключа — по 2 миллиона записей с именами ‘hans’ и ‘paul’:

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (id serial, name text);
INSERT INTO t_test (name) SELECT 'hans' FROM generate_series(1, 2000000);
INSERT INTO t_test (name) SELECT 'paul' FROM generate_series(1, 2000000);
SELECT name, count(*) FROM t_test GROUP BY name;

Без индексов — катастрофа

EXPLAIN ANALYZE SELECT * FROM t_test WHERE id = 432332;

Seq Scan on t_test  (cost=0.00..71622.00 rows=1 width=9)
                    (actual time=15.304..126.473 rows=1 loops=1)
  Filter: (id = 432332)
  Rows Removed by Filter: 3999999
Planning Time: 0.561 ms
Execution Time: 126.488 ms

~126 миллисекунд. PostgreSQL перебрал все 4 миллиона строк, отбросил 3 999 999 и нашёл одну. Все понимают, что индексы решат проблему, но прежде чем их строить — нужно понять план запроса.

EXPLAIN и «попугаи»

Посмотрим ещё раз на cost=0.00..71622.00 — что это за число?

Отключим параллельность для чистоты эксперимента:

SET max_parallel_workers_per_gather TO 0;

Стоимость складывается из количества блоков на диске и стоимости обработки строк:

SELECT pg_relation_size('t_test') / 8192.0; -- ~21622 блока по 8 КБ
SHOW cpu_tuple_cost;    -- 0.01 (стоимость обработки строки)
SHOW cpu_operator_cost; -- 0.0025 (стоимость оператора/функции)

Формула стоимости нашего запроса:

SELECT (pg_relation_size('t_test') / 8192.0) * 1
       + count(id) * 0.01
       + count(id) * 0.0025
FROM t_test;

Получается ~71622 — примерно то, что показывает EXPLAIN.

Стоимость в EXPLAIN не имеет отношения к реальному времени. Это условный коэффициент, «попугаи». Перевести его в миллисекунды невозможно. Сравнивать cost двух разных запросов между собой — тоже бессмысленно, потому что он не учитывает аппаратную составляющую. В рамках одного запроса — полезно для понимания, какая часть дороже. Между запросами — только как грубая оценка.

Строим индекс

CREATE INDEX idx_id ON t_test (id);
EXPLAIN SELECT * FROM t_test WHERE id = 43242;

Index Scan using idx_id on t_test  (cost=0.43..8.45 rows=1 width=9)
  Index Cond: (id = 43242)

Стоимость упала с 71 622 до 8.45 — почти в 8500 раз. Время выборки — доли миллисекунды вместо 126.

По умолчанию строится BTree-индекс. Он обеспечивает высокую конкурентность, но использование индексов не бесплатно — мы платим памятью и скоростью INSERT.

Индексы и сортировка

BTree используется не только для поиска:

EXPLAIN SELECT * FROM t_test ORDER BY id DESC LIMIT 10;

Limit  (cost=0.43..0.74 rows=10 width=9)
  ->  Index Scan Backward using idx_id on t_test  (cost=0.43..125505.43 rows=4000000 width=9)

PostgreSQL не сортирует — он просто идёт по индексу в обратном направлении (Backward) и останавливается после 10 строк.

EXPLAIN SELECT min(id), max(id) FROM t_test;

Result  (cost=0.92..0.93 rows=1 width=8)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.43..0.46 rows=1 width=4)
          ->  Index Only Scan using idx_id on t_test  (cost=0.43..128791.43 rows=4000000 width=4)
                Index Cond: (id IS NOT NULL)
  InitPlan 2 (returns $1)
    ->  Limit  (cost=0.43..0.46 rows=1 width=4)
          ->  Index Only Scan Backward using idx_id on t_test t_test_1  (cost=0.43..128791.43 rows=4000000 width=4)
                Index Cond: (id IS NOT NULL)

Для min — одна строка с начала дерева. Для max — одна с конца (Backward). Общая стоимость — меньше единицы.

Битовая карта: несколько условий

PostgreSQL умеет использовать один индекс несколько раз через OR:

EXPLAIN SELECT * FROM t_test WHERE id = 30 OR id = 50;

Bitmap Heap Scan on t_test  (cost=8.88..16.85 rows=2 width=9)
  Recheck Cond: ((id = 30) OR (id = 50))
  ->  BitmapOr  (cost=8.88..8.88 rows=2 width=0)
        ->  Bitmap Index Scan on idx_id  (cost=0.00..4.44 rows=1 width=0)
              Index Cond: (id = 30)
        ->  Bitmap Index Scan on idx_id  (cost=0.00..4.44 rows=1 width=0)
              Index Cond: (id = 50)

PostgreSQL сканирует индекс дважды (по разу на каждое условие), объединяет результаты через BitmapOr в битовую карту страниц, и только потом обращается к таблице (Bitmap Heap Scan) за нужными строками.

Почему PostgreSQL игнорирует ваш индекс

Добавим индекс на name:

CREATE INDEX idx_name ON t_test (name);

Поищем несуществующее имя:

EXPLAIN SELECT * FROM t_test WHERE name = 'hans2';

Index Scan using idx_name on t_test  (cost=0.43..8.45 rows=1 width=9)
  Index Cond: (name = 'hans2'::text)

Индекс работает. Обратите внимание: rows=1, хотя ‘hans2’ в таблице нет. PostgreSQL никогда не ставит rows=0 — даже для несуществующих данных он ожидает хотя бы одну строку, иначе не было бы смысла пытаться сканировать.

А теперь поищем то, что покрывает почти всю таблицу:

EXPLAIN SELECT * FROM t_test WHERE name = 'hans' OR name = 'paul';

Seq Scan on t_test  (cost=0.00..81622.00 rows=3000003 width=9)
  Filter: ((name = 'hans'::text) OR (name = 'paul'::text))

Индекс не используется. При покрытии большей части таблицы (ориентировочно от 70%) PostgreSQL решает, что дешевле прочитать всё с диска напрямую, чем прыгать между индексом и таблицей.

Это ключевой факт: планы выполнения зависят от данных и не являются статическими. Один и тот же запрос с разными параметрами может выполняться совершенно по-разному. Планировщик принимает решение на основе статистики — того, что он знает о распределении данных. Если статистика устарела, решение может быть неоптимальным.

Термин для этого — селективность: какую долю таблицы затрагивает условие. Высокая селективность (мало строк) — индекс выгоден. Низкая селективность (много строк) — Seq Scan дешевле.

Физическое расположение данных

Допустим, мы хотим прочитать диапазон: первые 10 000 записей. С индексом всё быстро — если данные лежат на диске последовательно.

Но что если данные разбросаны?

CREATE TABLE t_random AS SELECT * FROM t_test ORDER BY random();
CREATE INDEX idx_random ON t_random (id);
VACUUM ANALYZE t_random;

Сравним:

-- Оригинальная таблица (данные по порядку)
EXPLAIN (analyze true, buffers true) SELECT * FROM t_test WHERE id < 10000;

Index Scan using idx_id on t_test  (cost=0.43..346.88 rows=10083 width=9)
                                   (actual time=0.010..1.219 rows=9999 loops=1)
  Index Cond: (id < 10000)
  Buffers: shared hit=3 read=82
Planning Time: 0.108 ms
Execution Time: 1.668 ms

85 блоков (hit + read) — данные лежат рядом, читаются последовательно. А теперь рандомная таблица:

-- Рандомная таблица (данные вперемешку)
EXPLAIN (analyze true, buffers true) SELECT * FROM t_random WHERE id < 10000;

Bitmap Heap Scan on t_random  (cost=196.41..18136.79 rows=10320 width=9)
                              (actual time=1.826..79.262 rows=9999 loops=1)
  Recheck Cond: (id < 10000)
  Heap Blocks: exact=7981
  Buffers: shared hit=801 read=7210
  ->  Bitmap Index Scan on idx_random  (cost=0.00..193.83 rows=10320 width=0)
        Buffers: shared hit=3 read=27
Planning Time: 0.289 ms
Execution Time: 79.843 ms

8011 блоков (shared hit + read) вместо 85 — почти в 100 раз больше обращений к диску. 79 мс вместо 1.6 мс. Те же 10 000 строк, тот же индекс, но данные раскиданы по диску. PostgreSQL даже переключился с Index Scan на Bitmap Heap Scan, потому что при низкой корреляции это дешевле.

Визуализация плана на explain.tensor.ru

Корреляция

PostgreSQL знает об этом через статистику:

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('t_test', 't_random') AND attname = 'id'
ORDER BY 1, 2;

 tablename | attname | correlation
-----------+---------+--------------
 t_random  | id      | -0.003704906
 t_test    | id      |            1

Корреляция — насколько физический порядок данных на диске совпадает с логическим:

~1 — данные упорядочены, блоки читаются последовательно (t_test)
~0 — данные случайно разбросаны, каждая строка — отдельное обращение к диску (t_random)

CLUSTER

Можно физически отсортировать данные по индексу:

CLUSTER t_random USING idx_random;
VACUUM ANALYZE t_random;

После этого выборка снова быстрая. Но минусы существенные:

Блокирует всю таблицу на время работы — даже SELECT ждёт
Только по одному индексу
Не поддерживается автоматически — после новых вставок данные снова станут неупорядоченными

Index Only Scan

Если запрос обращается только к столбцам, которые есть в индексе — PostgreSQL может не лезть в таблицу:

EXPLAIN SELECT id FROM t_test WHERE id = 34234;

Index Only Scan using idx_id on t_test  (cost=0.43..8.45 rows=1 width=4)
  Index Cond: (id = 34234)

Всё, что нужно (id), уже есть в индексе — в таблицу идти не надо. А вот если запросить все столбцы:

EXPLAIN SELECT * FROM t_test WHERE id = 34234;

Index Scan using idx_id on t_test  (cost=0.43..8.45 rows=1 width=9)
  Index Cond: (id = 34234)

Уже обычный Index Scan — нужен столбец name, которого в индексе нет, поэтому PostgreSQL идёт в таблицу.

Для покрытия всех нужных столбцов — covering index с INCLUDE:

CREATE INDEX idx_random_cover ON t_random (id) INCLUDE (name);
EXPLAIN SELECT * FROM t_random WHERE id = 34234;

Index Only Scan using idx_random_cover on t_random  (cost=0.43..4.45 rows=1 width=9)
  Index Cond: (id = 34234)

Теперь снова Index Only Scan — даже для SELECT *, потому что name включён в индекс через INCLUDE.

Функциональные индексы

Индекс можно построить по результату функции. Единственное условие — функция должна быть детерминированной (одинаковый вход → одинаковый выход, без побочных эффектов):

CREATE INDEX idx_cos ON t_random (cos(id));
EXPLAIN SELECT * FROM t_random WHERE cos(id) = 10;

Index Scan using idx_cos on t_random  (cost=0.43..8.45 rows=1 width=9)
  Index Cond: (cos((id)::double precision) = '10'::double precision)

Типичное применение: индекс на lower(email) для поиска без учёта регистра.

Частичные индексы

Если 99% данных — это ‘hans’ и ‘paul’, и мы по ним никогда не ищем:

CREATE INDEX idx_name ON t_test (name) WHERE name NOT IN ('hans', 'paul');

Индекс будет крошечным и будет обновляться только при вставке “нетипичных” значений.

Типы индексов

В PostgreSQL 6 типов индексов (SELECT * FROM pg_am). Про BTree поговорили, разберём ещё два.

GiST и нечёткий поиск (pg_trgm)

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Расширение pg_trgm разбивает строки на триграммы и вычисляет расстояние между ними:

SELECT 'abcde' <-> 'abdeacb'; -- число от 0 до 1
SELECT show_trgm('abcdef');

Загрузим 2354 австрийских города и попробуем найти с ошибкой в написании:

CREATE TABLE t_location (name text);
-- импорт данных

SELECT * FROM t_location
ORDER BY name <-> 'Kramertneusiedel'
LIMIT 3;

Строки ‘Gramatneusiedl’ и ‘Kramertneusiedel’ произносятся похоже, а K вместо G — распространённая ошибка. Без индекса — полное сканирование. С GiST-индексом:

CREATE INDEX idx_trgm ON t_location USING GiST(name gist_trgm_ops);

Теперь поиск использует индекс. Триграммный индекс также работает с LIKE и регулярными выражениями:

EXPLAIN SELECT * FROM t_location WHERE name LIKE '%neusi%';

Bitmap Heap Scan on t_location  (cost=4.31..18.42 rows=2 width=15)
  Recheck Cond: (name ~~ '%neusi%'::text)
  ->  Bitmap Index Scan on idx_trgm  (cost=0.00..4.31 rows=2 width=0)
        Index Cond: (name ~~ '%neusi%'::text)

BTree для LIKE '%...%' бесполезен — он не умеет искать подстроку в середине. А GiST + pg_trgm справляется, потому что работает с триграммами, а не с порядком символов.

GIN и полнотекстовый поиск

GIN-индексы работают с лексемами — каноническими формами слов:

SELECT to_tsvector('english', 'A car, I want a car. I would not even mind having many cars');

‘car’ и ‘cars’ → одна лексема. ‘want’ и ‘wanted’ → одна лексема. Это позволяет находить документы по любой форме слова:

SELECT to_tsvector('english', 'A car, I want a car...')
    @@ to_tsquery('english', 'wanted');        -- true
SELECT to_tsvector('english', 'A car, I want a car...')
    @@ to_tsquery('english', 'wanted & bmw');   -- false

Два подхода к настройке:

1. Функциональный индекс:

CREATE INDEX idx_fts ON t_fts USING gin(to_tsvector('english', comment));

2. Отдельный столбец с триггером (быстрее при поиске, дороже при вставке):

ALTER TABLE t_fts ADD COLUMN ts tsvector;

CREATE TRIGGER tsvectorupdate
BEFORE INSERT OR UPDATE ON t_fts
FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(ts, 'pg_catalog.english', 'comment');

CREATE INDEX idx_fts ON t_fts USING gin(ts);

GiST и Exclusion Constraints

С расширением btree_gist можно строить ограничения на пересечение диапазонов — например, для бронирования:

CREATE EXTENSION btree_gist;

CREATE TABLE t_reservation (
    room int,
    "from" date,
    "to" date,
    EXCLUDE USING GiST (room WITH =, daterange("from", "to") WITH &&)
);

INSERT INTO t_reservation VALUES (10, '2017-01-01', '2017-03-03'); -- OK
INSERT INTO t_reservation VALUES (13, '2017-01-01', '2017-03-03'); -- OK (другая комната)
INSERT INTO t_reservation VALUES (13, '2017-02-02', '2017-08-14'); -- ERROR! (пересечение)

PostgreSQL не даст забронировать одну комнату на пересекающийся период — проверка выполняется на уровне индекса.

Что изменилось в PostgreSQL за 5 лет

Фундаментальная механика индексов не изменилась, но появились полезные инструменты:

BRIN-индексы (Block Range Index) заслуживают отдельного упоминания. Для данных с высокой корреляцией (тема, которую мы разобрали выше) BRIN может быть на порядки компактнее BTree. Таблица с миллиардом строк и временными метками — идеальный кейс для BRIN.
GIN для pg_trgm. В статье показан GiST + pg_trgm, но начиная с PG 13 GIN-поддержка для триграмм существенно ускорилась. Для больших объёмов данных GIN + pg_trgm может быть быстрее, чем GiST + pg_trgm.
REINDEX CONCURRENTLY (PG 14) — перестройка индексов без эксклюзивной блокировки таблицы.
pg_repack — расширение, которое делает то же, что CLUSTER, но без блокировки таблицы. В продакшене CLUSTER практически неприменим, а pg_repack решает ту же задачу.
Параллельное построение индексов стало нормой. Параллельный Seq Scan и Index Scan тоже значительно улучшились — отключать параллельность стоит только для демонстрации, не для продакшена.

Это вторая часть серии. В первой части мы разобрали транзакции, блокировки и VACUUM.

Почему PostgreSQL не использует ваш индекс