Вы создали индекс, но запрос по-прежнему делает 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.
