Индекс — это отдельная структура данных, которая хранит значения колонки в отсортированном виде вместе со ссылкой на строку. База находит нужные строки через поиск по этой структуре вместо перебора всей таблицы. Дальше работаем в том же мире users/orders, что и в статье про нормализацию, но со схемой, расширенной колонками под примеры этой статьи, и другим объёмом: 200 тыс. клиентов и 800 тыс. заказов, чтобы планировщик реально выбирал между сканами, а не работал с таблицей на пять строк. Все планы сняты в PostgreSQL 16.11.
Что делает индекс
Заказы одного клиента, без индекса по user_id:
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;Gather (actual time=0.543..15.557 rows=4 loops=1)
Workers Launched: 2
-> Parallel Seq Scan on orders (actual time=3.038..10.452 rows=1 loops=3)
Filter: (user_id = 12345)
Rows Removed by Filter: 266665
Execution Time: 15.611 msДва параллельных воркера всё равно проверяют все 800 тыс. строк по одной, ищут четыре и выбрасывают остальное. Теперь тот же запрос после CREATE INDEX:
CREATE INDEX idx_orders_user_id ON orders(user_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;Index Scan using idx_orders_user_id on orders (actual time=0.019..0.023 rows=4 loops=1)
Index Cond: (user_id = 12345)
Execution Time: 0.055 ms15.6 мс против 0.055 мс, разница в 280 раз. Индекс тут — B-дерево по user_id, база спускается по нему за несколько сравнений и сразу знает, в каких блоках таблицы лежат нужные строки, вместо чтения всех 800 тыс.
Как создать и удалить индекс
Синтаксис один и тот же для любой колонки:
CREATE INDEX idx_orders_user_id ON orders(user_id);CREATE UNIQUE INDEX делает то же самое и вдобавок запрещает дубликаты. Так на практике часто реализован UNIQUE в схеме:
CREATE UNIQUE INDEX idx_users_email ON users(email);
INSERT INTO users VALUES (200001, 'dup', 'Москва', 'user_5@example.com', current_date);ERROR: duplicate key value violates unique constraint "idx_users_email"
DETAIL: Key (email)=(user_5@example.com) already exists.Индекс занимает место отдельно от таблицы, и его стоит проверять перед тем, как вешать пятый индекс на горячую таблицу:
SELECT pg_size_pretty(pg_relation_size('idx_orders_user_id')); -- 9640 kB
SELECT pg_size_pretty(pg_relation_size('idx_users_email')); -- 7960 kB
SELECT pg_size_pretty(pg_relation_size('orders')); -- 106 MBОдин индекс по int-колонке на 800 тыс. строк — почти 10 МБ, то есть 8.9% размера самой таблицы. Удаляется индекс отдельной командой, без блокировки таблицы на запись других объектов:
DROP INDEX idx_users_email;Виды индексов
B-tree: индекс по умолчанию
CREATE INDEX без указания типа создаёт B-tree. Его достаточно для равенства, диапазонов (<, >, BETWEEN) и сортировки, то есть для подавляющего большинства запросов в отчётах. Оба индекса выше как раз B-tree.
Составной индекс: порядок колонок решает
Индекс (status, created_at) использует левый столбец как первую сортировку, второй как вторую. Запрос по обеим колонкам:
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'paid' AND created_at = date '2025-03-01';Bitmap Heap Scan on orders (actual time=0.228..1.144 rows=889 loops=1)
-> Bitmap Index Scan on idx_orders_status_created
Index Cond: ((status = 'paid'::text) AND (created_at = '2025-03-01'::date))
Execution Time: 1.251 msТот же индекс, но фильтр только по второй колонке:
EXPLAIN ANALYZE SELECT * FROM orders WHERE created_at = date '2025-03-01';Bitmap Heap Scan on orders (cost=8828.64..11489.33 ... actual time=2.513..3.076 rows=889)
-> Bitmap Index Scan on idx_orders_status_created
Index Cond: (created_at = '2025-03-01'::date)
Execution Time: 3.138 msТут наглядно видно то, что в учебниках упрощают до «не работает вообще»: планировщик всё-таки применил индекс, но cost=8828 вместо ожидаемых десятков — это фактически проход по всему дереву индекса, а не спуск к нужному месту, просто строка индекса короче строки таблицы. Отдельный индекс по created_at даёт настоящий спуск по дереву:
CREATE INDEX idx_orders_created_at ON orders(created_at);
EXPLAIN ANALYZE SELECT * FROM orders WHERE created_at = date '2025-03-01';Bitmap Heap Scan on orders (cost=11.25..2671.93 ... actual time=0.143..0.836 rows=889)
-> Bitmap Index Scan on idx_orders_created_at
Index Cond: (created_at = '2025-03-01'::date)
Execution Time: 0.906 mscost=11 против cost=8828, время 0.9 мс против 3.1 мс. Конкретный узел плана (Bitmap Heap Scan или простой Index Scan) зависит от статистики и версии PostgreSQL: важны cost и время, а не название узла. Правило порядка колонок в составном индексе: он полезен для запросов по первой колонке и по первой+второй вместе, но почти бесполезен для запросов только по второй.
Частичный индекс
WHERE внутри CREATE INDEX строит индекс не по всей таблице, а по подмножеству строк. Отменённых заказов в таблице 160 тыс. из 800 тыс.:
CREATE INDEX idx_orders_cancelled ON orders(created_at) WHERE status = 'cancelled';
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'cancelled' AND created_at > date '2025-01-01';Bitmap Heap Scan on orders (actual time=3.037..10.682 rows=95103)
-> Bitmap Index Scan on idx_orders_cancelled
Index Cond: (created_at > '2025-01-01'::date)
Execution Time: 12.117 msДо частичного индекса тот же запрос шёл 14.785 мс через полный idx_orders_status_created. Выигрыш по времени скромный (план тоже может прийти как простой Index Scan вместо Bitmap Heap Scan, смотря на статистику), зато по размеру заметный: частичный индекс весит 1136 kB, а полный индекс по одному created_at на все 800 тыс. строк — 5648 kB. Частичный индекс имеет смысл не столько ради скорости конкретного запроса, сколько ради того, чтобы не тащить в индекс 640 тыс. строк, которые в такой выборке никогда не понадобятся.
GIN для jsonb и массивов
B-tree сравнивает значение целиком, а jsonb с оператором @> («содержит») требует заглянуть внутрь документа. Для этого нужен GIN. Без индекса запрос идёт полным сканом:
EXPLAIN ANALYZE SELECT * FROM orders WHERE meta @> '{"coupon": "SALE10"}';Coupon SALE10 стоит примерно у 6% заказов. После CREATE INDEX ... USING gin (meta):
EXPLAIN ANALYZE SELECT * FROM orders WHERE meta @> '{"coupon": "SALE10"}';Bitmap Heap Scan on orders (cost=762.77..11597.10 ... actual time=11.569..26.448 rows=47058)
-> Bitmap Index Scan on idx_orders_meta_gin
Index Cond: (meta @> '{"coupon": "SALE10"}'::jsonb)
Execution Time: 27.280 msДля сравнения, фильтр по tags, у которого всего три значения и любое встречается в трети заказов: SEQ SCAN шёл 151.938 мс, с GIN 80.355 мс. Выигрыш заметно меньше, чем у coupon, потому что треть таблицы всё равно приходится прочитать с диска: GIN режет только объём просмотра индекса, а не число совпавших строк. Индекс размером 3472 kB на всю таблицу обходится недорого за то, что он вообще умеет искать внутри JSON.
Когда индекс не используется
Индекс — не гарантия, что база им воспользуется. Планировщик считает стоимость обоих вариантов и берёт дешёвый.
Маленькая таблица. В cities пять строк:
EXPLAIN ANALYZE SELECT * FROM cities WHERE city = 'Казань';Seq Scan on cities (cost=0.00..1.06 rows=1 width=64) (actual time=0.004..0.005 rows=1)
Execution Time: 0.020 msИндекс тут только замедлил бы: прочитать структуру дерева дороже, чем перебрать пять значений в памяти одной страницы.
Функция над колонкой. Обычный индекс по name не подходит для upper(name):
EXPLAIN ANALYZE SELECT * FROM users WHERE upper(name) = 'USER_500';Parallel Seq Scan on users (actual time=6.639..14.513 rows=0 loops=2)
Filter: (upper(name) = 'USER_500'::text)
Execution Time: 17.852 msБаза не может сопоставить результат функции с готовым порядком в индексе по сырой колонке. Индекс по выражению решает это напрямую:
CREATE INDEX idx_users_upper_name ON users(upper(name));
EXPLAIN ANALYZE SELECT * FROM users WHERE upper(name) = 'USER_500';Bitmap Heap Scan on users (actual time=0.017..0.018 rows=1)
-> Bitmap Index Scan on idx_users_upper_name
Index Cond: (upper(name) = 'USER_500'::text)
Execution Time: 0.032 ms17.9 мс против 0.032 мс: индекс по выражению вычисляет upper(name) один раз при вставке, а не при каждом запросе.
LIKE с ведущим %. Добавил колонку ref со случайным хешем на 800 тыс. строк и обычный text_pattern_ops-индекс. Поиск по префиксу использует индекс:
EXPLAIN ANALYZE SELECT * FROM orders WHERE ref LIKE 'a1b2%';Bitmap Heap Scan on orders (actual time=0.036..0.105 rows=18)
-> Bitmap Index Scan on idx_orders_ref_pattern
Index Cond: ((ref ~>=~ 'a1b2'::text) AND (ref ~<~ 'a1b3'::text))
Execution Time: 0.145 ms70.456 мс без индекса, 0.145 мс с ним (узел плана здесь может быть и Index Scan, и Bitmap Heap Scan, зависит от статистики, суть та же): префикс превращается в диапазон, а диапазоны B-tree ищет быстро. Поиск с % в начале так не работает в принципе, никакой B-tree-индекс не помогает:
EXPLAIN ANALYZE SELECT * FROM orders WHERE ref LIKE '%a1b2';Parallel Seq Scan on orders (actual time=6.720..34.766 rows=4 loops=3)
Filter: (ref ~~ '%a1b2'::text)
Execution Time: 40.249 msИндекс на месте, но неизвестное начало строки не даёт использовать сортировку вообще, и план идентичен запросу без индекса. Для такого поиска нужен pg_trgm с GIN, это отдельная тема.
Низкая селективность. status = 'paid' — это 60% из 800 тыс. заказов. Индекс по status существует, но планировщик его игнорирует:
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'paid';Seq Scan on orders (cost=0.00..23632.01 rows=479787 width=103) (actual time=0.010..47.448 rows=480001)
Filter: (status = 'paid'::text)
Execution Time: 54.527 msПланировщик оценивает Seq Scan как более дешёвый вариант: cost=23632 против cost=25048, если заставить его пройти по индексу (SET enable_seqscan = off):
Bitmap Heap Scan on orders (cost=5418.77..25048.11 rows=479787 width=103) (actual time=5.619..25.555 rows=480001)
Recheck Cond: (status = 'paid'::text)
Heap Blocks: exact=8320
-> Bitmap Index Scan on idx_orders_status_created (cost=0.00..5298.83 rows=479787 width=0)
Execution Time: 32.531 msНа пяти прогонах с прогретым кэшем разброс стабильный: Seq Scan держится в районе 54–55 мс, принудительный Bitmap Heap Scan в районе 32–33 мс. То есть на этой машине индекс на деле быстрее, хотя собственная cost-оценка планировщика выше именно у него. Это не значит, что «планировщик ошибается»: cost-модель считает условные единицы ввода-вывода и CPU по статистике таблицы, а не засекает время на конкретном железе с прогретым буферным кэшем. Вывод для практики: не полагайся на enable_seqscan = off как на серебряную пулю для медленного запроса. И не верь оценке планировщика на слово: проверяй EXPLAIN ANALYZE на своих данных, а не на цифрах из чужой статьи.
«Кластерные индексы»: чего в PostgreSQL нет
В MS SQL Server и InnoDB кластерный индекс — это способ хранения таблицы: строки физически лежат в порядке первичного ключа, и таблица без него не существует. В PostgreSQL это устроено иначе: таблица — обычная куча (heap), строки лежат в порядке вставки независимо от того, есть индексы или нет. Постоянного кластерного индекса не бывает в принципе.
Единственное, что есть, — разовая команда CLUSTER: она физически пересортировывает уже существующие строки по указанному индексу.
Строки в orders вставлялись по возрастанию даты, поэтому correlation колонки created_at в этой таблице и так уже равна 1: CLUSTER тут нечего улучшать. Физический разброс появляется от вставок не по датам: массовых UPDATE, догрузки истории, нескольких источников данных. Чтобы показать эффект честно, собрал копию с теми же строками, но в случайном порядке вставки:
CREATE TABLE orders_shuffled AS SELECT * FROM orders ORDER BY random();
CREATE INDEX idx_orders_shuf_created ON orders_shuffled(created_at);
ANALYZE orders_shuffled;
SELECT correlation FROM pg_stats WHERE tablename = 'orders_shuffled' AND attname = 'created_at'; correlation
-------------
-0.0038389307Корреляция около нуля: строки за один месяц раскиданы по всей таблице. Диапазонный запрос по дате идёт битмапом:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders_shuffled
WHERE created_at BETWEEN date '2025-01-01' AND date '2025-01-31';Bitmap Heap Scan on orders_shuffled (actual time=2.393..30.938 rows=27560)
Heap Blocks: exact=11926
Buffers: shared hit=530 read=11423 written=7
Execution Time: 31.678 msCLUSTER orders_shuffled USING idx_orders_shuf_created;
SELECT correlation FROM pg_stats WHERE tablename = 'orders_shuffled' AND attname = 'created_at'; correlation
-------------
1EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders_shuffled
WHERE created_at BETWEEN date '2025-01-01' AND date '2025-01-31';Index Scan using idx_orders_shuf_created on orders_shuffled (actual time=0.009..1.405 rows=27560)
Buffers: shared hit=497
Execution Time: 1.899 msКорреляция ушла в 1, суммарное чтение упало с 11953 буферов (530 из кэша плюс 11423 с диска) до 497 буферов из кэша, время с 31.7 до 1.9 мс: разница почти в 17 раз. Заодно план сменился с Bitmap Heap Scan на простой Index Scan — при высокой корреляции строк планировщик больше не считает нужным собирать битмап, читает по индексу напрямую. Но порядок держится ровно до первой вставки не по месту:
INSERT INTO orders_shuffled (id, user_id, amount, status, created_at, meta)
VALUES (900002, 1, 500, 'paid', date '2025-01-15', '{}'::jsonb);
SELECT ctid, id, created_at FROM orders_shuffled ORDER BY ctid DESC LIMIT 1; ctid | id | created_at
-----------+--------+------------
(13617,6) | 900002 | 2025-01-15Таблица занимает 13618 блоков (relpages), и новая строка легла ровно в последний, физический конец кучи, рядом с чем угодно, что вставили последним по времени INSERT, а не с январскими датами, куда её положил бы порядок CLUSTER. PostgreSQL не поддерживает этот порядок при вставках, команду нужно перезапускать вручную, а на большой таблице это полная блокировка на время пересборки.
Обслуживание: REINDEX и цена индекса на запись
Индекс со временем раздувается: из-за MVCC старые версии строк какое-то время занимают место и в индексе тоже. REINDEX пересобирает структуру с нуля:
REINDEX TABLE orders;Индексы не бесплатны на запись. Вставил 300 тыс. строк в таблицу без индексов и в такую же таблицу с четырьмя индексами (user_id, (status, created_at), created_at, GIN по meta):
| таблица | индексов | время INSERT 300 000 строк |
|---|---|---|
orders_noidx | 0 | 365.960 мс |
orders_idx | 4 | 1200.510 мс |
Каждый INSERT вместе со строкой таблицы дописывает запись в каждый индекс, и с четырьмя индексами вставка втрое медленнее. Для таблицы, в которую в основном пишут (лог событий, очередь), лишний индекс стоит как тариф на каждую запись, который платится независимо от того, читает ли кто-нибудь эти данные вообще. Добавлять индекс имеет смысл под конкретный медленный запрос, а не «на будущее», ровно так же, как денормализация оправдана под конкретный отчёт, а не заранее.
На чём ловят на собеседовании
«Индекс всегда ускоряет запросы». Индекс по status из примера выше есть, но планировщик по умолчанию всё равно выбирает Seq Scan: при 60% строк со значением paid его cost-модель считает индекс дороже одного последовательного прохода. На этой машине с прогретым кэшем принудительный индекс на деле оказался даже быстрее, но опираться на это нельзя: cost-оценка и секундомер меряют разное, единственный надёжный способ узнать правду про свой запрос — прогнать EXPLAIN ANALYZE на своих данных.
«Составной индекс (a, b) работает для запроса по b». Формально да, планировщик может его подставить, но полезной выборки не получится — только проход по всему дереву индекса вместо строк таблицы. Отдельный индекс по b в замере оказался в 780 раз дешевле по cost.
«В PostgreSQL есть кластерный индекс, как в MS SQL». Нет постоянной структуры, есть разовая команда CLUSTER, которая теряет эффект после первой же вставки не по порядку.
Частые вопросы
Какой индекс создать по умолчанию
B-tree, командой CREATE INDEX ON таблица(колонка) без указания типа. Он покрывает равенство, диапазоны и сортировку, а это большинство реальных запросов. GIN, pg_trgm, BRIN нужны под конкретную задачу: JSON, полнотекстовый поиск, огромные таблицы с естественной сортировкой по времени вставки.
Как понять, что индекс не используется зря
EXPLAIN показывает Seq Scan там, где ожидался Index Scan. Дальше три вопроса: колонка отфильтрована через функцию, значение занимает больше 5–10% таблицы, или таблица настолько маленькая, что дешевле прочитать её целиком. Всё это разобрано по отдельности выше с конкретными планами.
Сколько индексов уже слишком много
Единого числа нет, вопрос в профиле нагрузки. Для таблицы, в которую активно пишут, каждый лишний индекс — заметная доля стоимости INSERT, как показывает замер: 4 индекса утроили время вставки. Для таблицы-справочника, которую читают в сотни раз чаще, чем меняют, это не аргумент вообще.
Обязательно ли делать VACUUM после REINDEX
Нет, это разные операции: REINDEX пересобирает индекс с нуля и не трогает таблицу, VACUUM возвращает место от старых версий строк в самой таблице. Обе решают проблему раздувания, но с разных сторон, и обычно достаточно автовакуума без ручного вмешательства.
Где потренироваться
Тот же users/orders из нормализации лежит в основе задач пути «SQL для аналитиков» — там же разбираются JOIN и GROUP BY, на которых видно, как индекс по внешнему ключу меняет план соединения и агрегации. Задачи с автопроверкой на Koddo дают сразу увидеть план своего запроса, а не поверить на слово.