«Индекс есть, но запрос всё равно медленный» — одна из самых частых жалоб при работе с PostgreSQL. Почти всегда причина в селективности: PostgreSQL сам решает, выгодно ли использовать индекс или проще прочитать всю таблицу. Разберём, как он это решает и что делать, если он решает неверно.
Цена чтения всей таблицы одна и та же, сколько бы строк ни подошло под условие, — это горизонтальная линия. Цена индекса растёт вместе с числом найденных строк: за каждой из них приходится прыгать в случайное место. Линии пересекаются где-то между 5% и 20% строк; слева от перелома выигрывает индекс, справа — seq-scan. Условие status = 'PAID' попадает влево, status = 'DELIVERED' — вправо, хотя индекс в обоих случаях один и тот же.
Почему PostgreSQL иногда не использует индекс
Представьте таблицу из миллиона заказов. У каждого заказа есть поле status с пятью значениями: NEW, PAID, SHIPPED, DELIVERED, CANCELLED. Большинство заказов — DELIVERED, примерно 62%.
Вы добавили индекс на status и запрашиваете WHERE status = 'DELIVERED'. PostgreSQL смотрит на индекс и видит: он приведёт к 620 000 строкам из 1 000 000. Чтобы получить каждую из них, нужно прыгнуть в случайное место на диске. Это медленнее, чем просто прочитать всю таблицу последовательно. Поэтому PostgreSQL выбирает seq-scan — и он прав.
Ключевой термин здесь — селективность.
Что такое селективность
Слово «селективность» в документации PostgreSQL и в разговоре разработчиков означает немного разные вещи, и путаница тут обычная. Планировщик под селективностью понимает долю строк, которые пройдут условие: чем меньше эта доля, тем избирательнее условие и тем выгоднее индекс. Разработчики же чаще имеют в виду разнообразие значений в колонке — сколько в ней различных значений на общее число строк. Величины разные, но связаны напрямую: чем больше в колонке различных значений, тем меньше строк вернёт условие на равенство.
Дальше в статье под селективностью колонки понимается второе — разнообразие:
разнообразие = количество_уникальных_значений / всего_строк
| Колонка | Уникальных | Всего строк | Разнообразие |
|---|---|---|---|
id (первичный ключ) | 1 000 000 | 1 000 000 | 1.0 — идеально |
email | 998 000 | 1 000 000 | 0.998 — отлично |
customer_id | 50 000 | 1 000 000 | 0.05 — средне |
status (5 значений) | 5 | 1 000 000 | 0.000005 — низкая |
is_deleted | 2 | 1 000 000 | 0.000002 — почти нулевая |
Индекс на is_deleted или status практически бесполезен: запрос WHERE is_deleted = false вернёт 98% таблицы. PostgreSQL разумно предпочтёт seq-scan.
Как PostgreSQL принимает решение
Планировщик оценивает процент строк, который вернёт условие запроса, и сравнивает стоимость двух путей:
| Доля строк по условию | Индекс? |
|---|---|
| менее 1% | да, точно |
| 1–5% | скорее всего да |
| 5–20% | зависит от размера строки и кеша |
| более 20% | скорее всего нет |
| более 50% | точно нет |
Сама таблица — грубый ориентир, а не правило из кода планировщика: тот считает стоимость и сравнивает числа. Входов в этот счёт несколько: сколько данных PostgreSQL считает закешированным (effective_cache_size), во сколько раз случайное чтение дороже последовательного (random_page_cost) — и, про что забывают чаще всего, насколько порядок строк в таблице совпадает с порядком значений в индексе. В статистике это отдельное число, correlation. Если оно близко к единице — свежие заказы физически лежат подряд, потому что подряд и вставлялись, — индексный проход читает страницы почти последовательно и остаётся дешёвым даже на 30–40% строк. Если близко к нулю, индекс сдаётся куда раньше порогов из таблицы.
И ещё одно: исходов у выбора не два, а три. В спорной середине PostgreSQL обычно берёт промежуточный путь — Bitmap Index Scan вместе с Bitmap Heap Scan. Сначала он проходит по индексу и складывает адреса подходящих строк в битовую карту, потом сортирует её по номерам страниц и читает таблицу по порядку, каждую страницу ровно один раз. Прыжков в случайные места нет, лишнего читается немного — отсюда и выигрыш посередине:
Bitmap Heap Scan on orders (actual rows=8000)
Recheck Cond: (status = 'PAID')
Heap Blocks: exact=2000
-> Bitmap Index Scan on ix_orders_status (actual rows=8000)
Index Cond: (status = 'PAID')
Увидели в плане Bitmap Heap Scan — значит, условие попало ровно в ту середину: индекс работает, просто не тем способом, которого вы ждали.
Настройка random_page_cost на SSD
По умолчанию random_page_cost = 4.0 — это значение рассчитано под жёсткие диски, где случайное чтение в четыре раза медленнее последовательного. На SSD разница почти исчезает.
Если не обновить этот параметр, PostgreSQL будет систематически выбирать seq-scan там, где индекс реально быстрее.
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();
Значение 1.1 — стандартная рекомендация для SSD.
Только отдавайте себе отчёт, что делает ALTER SYSTEM: он дописывает параметр в файл postgresql.auto.conf, и настройка переживает перезапуск базы. Это решение для всего сервера, а не эксперимент на пять минут. Чтобы просто проверить догадку «а если бы индекс был дешевле», хватит SET random_page_cost = 1.1; в своей сессии — там значение живёт до отключения и больше никого не задевает.
Рядом с random_page_cost стоит параметр, который на решение влияет не меньше, а объясняется реже, — effective_cache_size. Это не выделяемая память, база ничего по нему не занимает: это подсказка планировщику, сколько данных, по вашему мнению, вообще окажется в кеше — своём и дисковом кеше операционной системы. Чем больше значение, тем дешевле планировщику кажутся повторные обращения к страницам, и тем охотнее он берёт индекс вместо перебора. По умолчанию там 4 ГБ, что для сервера с 64 ГБ памяти — грубое занижение; обычная рекомендация — от половины до трёх четвертей оперативной памяти машины. Строка «памяти много, а база упорно читает таблицу целиком» чаще всего объясняется именно этим параметром, оставшимся по умолчанию.
Как посмотреть статистику колонки
PostgreSQL хранит статистику по каждой колонке в системном представлении pg_stats:
живой пример
SELECT
attname,
n_distinct,
most_common_vals,
most_common_freqs,
null_frac,
correlation
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders';
Запустить
Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →
Что означают поля:
n_distinct— оценка уникальных значений. Положительное число — абсолютное количество; отрицательное (-0.05) — доля от числа строк (5% уникальных).most_common_vals— массив наиболее частых значений.most_common_freqs— частоты от 0 до 1 для каждого из них.null_frac— доля NULL-значений.correlation— от −1 до 1: насколько физический порядок строк в таблице совпадает с порядком значений в колонке. Единица уcreated_atв журнале, где строки только дописывают; около нуля — у колонки, значения которой разбросаны по таблице как попало.
Пример результата для колонки status:
attname | status
n_distinct | 5
most_common_vals | {DELIVERED,SHIPPED,NEW,CANCELLED,PAID}
most_common_freqs | {0.62, 0.18, 0.10, 0.06, 0.04}
По этой статистике и видно, откуда берутся 62% строк у DELIVERED и 4% у PAID.
В pg_stats лежит оценка, собранная последним ANALYZE. Как доли выглядят на самом деле, показывает обычный запрос:
живой пример
SELECT status,
count(*) AS rows_with_status,
round(100.0 * count(*) / (SELECT count(*) FROM orders), 1) AS pct
FROM orders
GROUP BY status
ORDER BY rows_with_status DESC;
Запустить
Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →
Полезно знать, откуда эти числа берутся, — тогда понятно, когда им не верить. ANALYZE не читает таблицу целиком: он берёт случайную выборку примерно в 300 × default_statistics_target строк (по умолчанию 30 000) и по ней оценивает всё остальное. Для доли значений и для списка самых частых это работает прекрасно. А вот n_distinct — «сколько всего разных значений» — по выборке оценивается плохо в принципе, и на больших таблицах систематически занижается: в тридцати тысячах строк из ста миллионов просто не видно, что разных клиентов там два миллиона, а не сто тысяч.
Последствие прямое: база считает, что условие WHERE customer_id = ? вернёт в двадцать раз больше строк, чем на самом деле, и отказывается от индекса. Лечится двумя способами. Увеличить точность для конкретной колонки: ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000; и переснять ANALYZE — выборка станет больше. Или, если правильное число известно, задать его прямо: ALTER TABLE orders ALTER COLUMN customer_id SET (n_distinct = -0.3); — отрицательное значение означает долю от числа строк и переживает рост таблицы.
Составной индекс там, где одиночный бесполезен
Колонка status с пятью значениями сама по себе малополезна для индексирования. Но если добавить вторую колонку с высокой селективностью, картина меняется.
Составной индекс (status, created_at):
WHERE status = 'DELIVERED'— 62% строк → seq-scan.WHERE status = 'DELIVERED' AND created_at > now() - interval '1 day'— примерно 62% × 0.5% = 0.3% → индекс идеально подходит.
Селективности двух условий перемножаются, если условия независимы: доля заказов клиента не зависит от их статуса. Поэтому составной индекс может работать там, где одиночный бесполезен, но оба условия должны присутствовать в запросе; что происходит, когда колонки зависят друг от друга, разобрано ниже, в коррелирующих колонках.
Арифметика планировщика — перемножить доли и сравнить с порогами из таблицы выше: меньше 5 % строк — индекс, больше 20 % — перебор, между ними по обстоятельствам. Те же 0.05 и 0.20 стоят в коде:
живой пример
public class Selectivity {
static final long ROWS = 1_000_000;
public static void main(String[] args) {
verdict("status = 'DELIVERED'", 0.62);
verdict("status = 'PAID'", 0.04);
verdict("status = 'DELIVERED' AND created_at > now() - 1 day", 0.62 * 0.005);
double independent = 0.05 * 0.05;
double real = 0.05;
System.out.println();
System.out.println("country_code = 'RU' AND currency = 'RUB'");
System.out.println(" планировщик ждёт " + rows(independent) + " строк");
System.out.println(" на деле вернётся " + rows(real) + " — разница в "
+ Math.round(real / independent) + " раз");
}
static void verdict(String where, double fraction) {
String choice = fraction < 0.05 ? "индекс"
: fraction > 0.20 ? "seq scan" : "по обстоятельствам";
System.out.printf("%-52s %7d строк (%5.2f%%) -> %s%n",
where, rows(fraction), fraction * 100, choice);
}
static long rows(double fraction) {
return Math.round(ROWS * fraction);
}
}
Запустить
Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →
Первые три строки — таблица порогов в числах, последние две — где перемножение врёт.
Как проверить, что происходит в реальности
EXPLAIN ANALYZE показывает и план, и фактическое выполнение:
живой пример
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'PAID';
Запустить
Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →
Seq Scan on orders (cost=0.00..18334.00 rows=400000 width=...) (actual time=0.012..89.123 rows=42137 loops=1)
Filter: (status = 'PAID')
Rows Removed by Filter: 957863
Здесь важно сравнить два числа:
rows=400000— оценка планировщика.actual rows=42137— реальное количество строк.
Расхождение почти в десять раз означает, что статистика устарела и планировщик принял решение на основе неверных данных.
Как переспросить планировщика
Самый дешёвый способ проверить «а был бы индекс лучше» — заставить базу построить альтернативный план и сравнить два. Для этого есть переключатели, которые не запрещают узел совсем, а делают его очень дорогим:
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ; -- как база решила сама
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ; -- как было бы с индексом
RESET enable_seqscan;
Сравнивать надо не «красивость» планов, а два числа: фактическое время и Buffers — сколько страниц пришлось прочитать. Если с индексом блоков меньше и время меньше, планировщик ошибся, и дальше вопрос в том, почему: устаревшая статистика, заниженный n_distinct, неверный random_page_cost. Если же с индексом хуже — база была права, и спорить не с чем.
Важно: это инструмент диагностики, а не настройка. SET enable_seqscan = off в приложении или в postgresql.conf — почти всегда ошибка: он не запрещает перебор, а назначает ему заградительную цену, и в запросах, где перебор действительно уместен, планы станут хуже.
Статистика по выражениям
Индекс по функции планировщик оценивает вслепую: статистики по lower(email) у него нет, есть только по email, и для условия WHERE lower(email) = ? он подставляет грубую догадку. С PostgreSQL 14 это чинится расширенной статистикой по выражению:
CREATE STATISTICS email_lower_stat ON lower(email) FROM customer;
ANALYZE customer;
После этого оценки для условия по выражению становятся такими же честными, как для обычной колонки. Та же команда, но с несколькими колонками, решает соседнюю задачу — коррелирующие колонки, о которых ниже.
Когда статистика устаревает и что делать
Запустить ANALYZE вручную
Autovacuum запускает сбор статистики автоматически, когда в таблице изменилось около 10% строк. При массовой загрузке данных он не успевает. После крупной загрузки стоит запустить вручную:
ANALYZE orders; -- одна таблица
ANALYZE orders (status, created_at); -- только нужные колонки
ANALYZE; -- все таблицы базы, доступные пользователю
Увеличить глубину статистики
По умолчанию PostgreSQL хранит статистику по 100 наиболее частым значениям (statistics_target = 100). Пока различных значений в колонке меньше сотни, в этот список попадают все, и оценки точны. Проблема начинается, когда значений больше: скажем, у city их десятки тысяч. Тогда в статистику попадут сто самых частых городов, а все остальные планировщик будет оценивать одной усреднённой цифрой — и для редкого города решит, что строк вернётся куда больше, чем на самом деле.
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;
После этого PostgreSQL будет хранить до 1000 наиболее частых значений для этой колонки.
Когда статистика верна, а план всё равно плохой
ANALYZE — не универсальное лекарство, и стоит знать случаи, где дело не в нём.
ORDER BY … LIMIT с подходящим индексом не по тому полю. База видит индекс, дающий нужный порядок, и решает: пойду по нему и остановлюсь, как только наберу двадцать строк. Расчёт верен, если подходящие строки попадаются часто. Если же условие выполняется у одной строки из миллиона, она уйдёт по индексу сортировки до конца таблицы и прочитает всё. Признак — план с Index Scan по индексу сортировки, огромный actual rows у нижнего узла и время в секундах на запросе с LIMIT 20. Лечится составным индексом, где колонка условия стоит первой, а колонка сортировки — второй.
Условие по нескольким колонкам, которые связаны между собой. Город и страна, марка и модель, статус и дата закрытия: база перемножает доли, считая колонки независимыми, и получает оценку в десятки раз меньше реальной. Это отдельная тема — расширенная статистика CREATE STATISTICS … (dependencies), о ней ниже в разделе про коррелирующие колонки.
Коррелированный подзапрос или функция в условии. Стоимость пользовательской функции планировщик не знает (по умолчанию считает её очень дешёвой) и вызывает её столько раз, сколько строк проверяет. Помогает вынести вызов в LATERAL-подзапрос или честно объявить стоимость: ALTER FUNCTION … COST 1000.
Условие возвращает 90 % строк — и это нормально. Здесь плох не план, а сам запрос: перебор действительно дешевле. Если же нужны именно оставшиеся 10 %, ответ не в статистике, а в частичном индексе: CREATE INDEX ... ON orders (created_at) WHERE status <> 'COMPLETED' — он меньше, обновляется реже и отвечает ровно на тот запрос, ради которого заведён.
Коррелирующие колонки
Бывает, что две колонки связаны между собой: например, country_code и currency. Страна Россия — почти всегда рубль. PostgreSQL по умолчанию считает колонки независимыми и перемножает доли: 5% × 5% = 0.25%. Но если рубль встречается только у российских заказов, условие вернёт все 5% — ровно это и посчитал пример выше. Планировщик ждёт две с половиной тысячи строк, а приходит пятьдесят тысяч — план построен под выборку в двадцать раз меньше реальной.
Для таких случаев есть расширенная статистика:
CREATE STATISTICS stats_orders_geo (mcv, dependencies, ndistinct)
ON country_code, currency FROM orders;
ANALYZE orders;
Три вида статистики в скобках отвечают за разное, и для условий на равенство главный здесь первый. mcv (с PostgreSQL 12) запоминает частые сочетания значений целиком — «RU вместе с RUB встречается в 5% строк», и это ровно ответ на наш запрос. dependencies описывает зависимость «страна определяет валюту», ndistinct — сколько разных пар вообще бывает.
После этого планировщик не перемножает доли вслепую: для WHERE country_code = 'RU' AND currency = 'RUB' он берёт оценку из сочетания, а не произведение двух независимых.
Частые ошибки
Индекс на булевой колонке. WHERE is_deleted = false — почти всегда 98%+ строк. Индекс не поможет. Если нужен частичный охват, лучше сделать частичный индекс: CREATE INDEX ON orders (created_at) WHERE deleted_at IS NULL — он охватывает только актуальные строки.
Игнорировать расхождение rows и actual rows. Десятикратное расхождение в EXPLAIN ANALYZE — сигнал, что статистика устарела. Лечится ANALYZE — после массовой загрузки его запускают руками: autovacuum за быстрой вставкой не успевает.
Глубже: Bitmap-планы и неиспользуемые индексырасширенное
Между «пройти по индексу» и «прочитать таблицу целиком» у PostgreSQL есть третий способ. Bitmap Index Scan сначала собирает по индексу битовую карту страниц, где лежат подходящие строки, а потом Bitmap Heap Scan читает эти страницы по порядку, каждую один раз. Так база обслуживает условия средней селективности, когда строк слишком много для прыжков по индексу и слишком мало для полного перебора, и так же объединяет несколько индексов: BitmapAnd пересекает карты по двум условиям, BitmapOr складывает по OR. Поэтому два одиночных индекса иногда всё же работают вместе, только через карту, а не так эффективно, как один составной.
Обратная задача, лишние индексы. Каждый стоит места и замедляет запись, а часть из них не используется никогда:
живой пример
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
Запустить
Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →
idx_scan = 0 при работающей неделю базе значит, что индекс никому не понадобился, кандидат на удаление; статистика копится с последнего сброса, поэтому смотрят после достаточного срока. Дубли ищут по определениям в pg_indexes: индекс (customer_id) рядом с (customer_id, created_at) лишний, второй покрывает первый по правилу левого префикса. Уникальные индексы и индексы под внешние ключи из этого списка не удаляют, даже с нулём: они держат целостность и ускоряют каскады.
Коротко
- Селективность колонки — уникальных значений на общее число строк; планировщик считает долю строк по условию.
- Пороги — ориентир, а не правило: менее 5% строк → индекс, более 20% → seq-scan, между ними обычно
Bitmap Heap Scan; сдвигает границуcorrelation— физический порядок строк в таблице. Индекс наis_deletedили наstatusс пятью значениями бесполезен. - Составной индекс
(status, created_at)работает там, где(status)бесполезен: доли условий перемножаются. pg_statsпоказывает, что знает планировщик (n_distinct,most_common_freqs); расхождениеrowsиactual rowsв десять раз вEXPLAIN ANALYZE— сигнал запуститьANALYZE.- На SSD ставят
random_page_cost = 1.1: значение по умолчанию 4.0 рассчитано на жёсткий диск и занижает пользу индексов. - Колонки с тысячами значений —
SET STATISTICS 1000; связанные колонки —CREATE STATISTICS ... (mcv, dependencies), иначе перемножение долей врёт. - Третий способ между Index Scan и Seq Scan: Bitmap-план, он же объединяет несколько индексов через
BitmapAndиBitmapOr; неиспользуемые индексы ищут поpg_stat_user_indexesсidx_scan = 0. - Переспросить планировщика можно
SET enable_seqscan = offи сравнением двух планов поBuffersи времени — это диагностика, а не настройка. ANALYZEчитает выборку (~300 ×default_statistics_targetстрок), поэтомуn_distinctна больших таблицах занижается: лечитсяSET STATISTICSили заданным вручнуюn_distinct.- Не всё чинится статистикой:
ORDER BY … LIMITпо чужому индексу, связанные колонки и дорогие функции требуют другого индекса илиCREATE STATISTICS.
Что почитать дальше
- Составные индексы в PostgreSQL — порядок колонок и левый префикс.
- EXPLAIN ANALYZE — как читать план запроса детально.
- Типы индексов PostgreSQL — когда B-tree, а когда GIN или BRIN.
- VACUUM и autovacuum — когда и как обновляется статистика.