Индексы PostgreSQL и EXPLAIN ANALYZE: запрос, который читал 400 тысяч строк ради 20
Индекс есть, а кабинет крупного мерчанта всё равно тормозит. Разбираем вывод EXPLAIN ANALYZE, устройство B-tree и то, как один составной индекс чинит запрос, — на реальных планах.
Представьте платёжную систему с личным кабинетом мерчанта. На главной странице — последние 20 успешных платежей. Код простейший, ORM отправляет один запрос, локально страница открывается мгновенно. В продакшене большинство мерчантов тоже ничего не замечает. Жалуется только самый крупный: его кабинет открывается медленно, а в часы пик график CPU базы ползёт вверх.
Код тот же, запрос тот же, индекс на merchant_id есть. Так в чём дело? В этой статье разберём это с помощью EXPLAIN ANALYZE: как читать цифры в плане, что на самом деле лежит внутри B-tree индекса, когда индекс не помогает вообще и как в итоге починить запрос правильным индексом.
Проблема: один запрос, две скорости
Модель самая обычная:
# billing/models.py
from django.db import models
class Merchant(models.Model):
name = models.CharField(max_length=100)
class Payment(models.Model):
class Status(models.TextChoices):
PENDING = "pending", "Pending"
PAID = "paid", "Paid"
REFUNDED = "refunded", "Refunded"
merchant = models.ForeignKey(
Merchant, on_delete=models.PROTECT, related_name="payments"
)
status = models.CharField(max_length=16, choices=Status.choices)
amount = models.DecimalField(max_digits=12, decimal_places=2)
created_at = models.DateTimeField(auto_now_add=True)
Селектор для кабинета:
# billing/selectors.py
from billing.models import Payment
def latest_paid(merchant_id: int):
return (
Payment.objects
.filter(merchant_id=merchant_id, status=Payment.Status.PAID)
.order_by("-created_at")[:20]
)
Django отправляет в PostgreSQL примерно такой SQL:
SELECT "billing_payment"."id", "billing_payment"."merchant_id",
"billing_payment"."status", "billing_payment"."amount",
"billing_payment"."created_at"
FROM "billing_payment"
WHERE ("billing_payment"."merchant_id" = 42
AND "billing_payment"."status" = 'paid')
ORDER BY "billing_payment"."created_at" DESC
LIMIT 20;
merchant — это ForeignKey, а для ForeignKey Django автоматически создаёт индекс (db_index=True по умолчанию). То есть индекс по billing_payment.merchant_id есть. На первый взгляд всё в порядке.
Чтобы цифры были настоящими, подготовим синтетические данные: 2 миллиона платежей, примерно 20% из них принадлежат одному крупному мерчанту (id = 42), 80% в статусе paid. В реальных системах такое неравномерное распределение встречается постоянно: пара крупных клиентов и тысячи мелких.
INSERT INTO billing_merchant (name)
SELECT 'merchant ' || g FROM generate_series(1, 5000) AS g;
-- 2M payments: ~20% belong to merchant 42, ~80% are 'paid'
INSERT INTO billing_payment (merchant_id, status, amount, created_at)
SELECT
CASE WHEN random() < 0.2 THEN 42 ELSE 1 + (random() * 4999)::int END,
CASE WHEN r < 0.80 THEN 'paid'
WHEN r < 0.97 THEN 'pending'
ELSE 'refunded' END,
(random() * 1000)::numeric(12, 2),
now() - random() * interval '365 days'
FROM (SELECT random() AS r FROM generate_series(1, 2000000)) AS s;
VACUUM ANALYZE billing_payment;
Все планы в статье получены на этих данных в PostgreSQL 16. Миллисекунды на вашем сервере будут другими — важны их соотношение и форма плана.
Что внутри B-tree
Прежде чем читать план, нужно понять сам индекс. CREATE INDEX в PostgreSQL по умолчанию строит B-tree. Это сбалансированное дерево из страниц по 8 КБ: наверху корневая страница, посередине внутренние, внизу листовые.
[ root: 2500 ]
/ \
[ 900 | 1700 ] [ 3300 | 4100 ] internal pages
/ | \ / | \
[1..899] [900..1699] [1700..] [2500..] [3300..] [4100..] leaf pages
<-> <-> <-> <-> <-> (linked list)
leaf entry: key = 42 -> TID (block 1834, item 7) -> row in the heap
Ключевые моменты:
- В листовых страницах ключи отсортированы, и каждая запись — это ключ плюс TID (физический адрес строки в таблице: номер блока и позиция в нём).
- Соседние листья связаны между собой. Поэтому для
BETWEEN,>/<иORDER BYPostgreSQL один раз спускается в нужное место, а дальше идёт по листьям последовательно. Идти можно и в обратную сторону, поэтому сортировкаDESCтоже может использовать индекс. - Глубина дерева растёт логарифмически: даже на таблице в миллионы строк от корня до листа — всего несколько страниц.
- В индексе нет информации о видимости строки (MVCC). По найденному TID PostgreSQL всё равно идёт в саму таблицу (heap), проверяет, видна ли строка текущей транзакции, и читает остальные колонки. Эти переходы в heap — случайное (random) чтение, и именно оно дорого стоит.
Поэтому у PostgreSQL есть несколько способов прочитать таблицу, и каждый раз он выбирает один из них по стоимости (cost):
| Способ | Как работает | Когда хорош |
|---|---|---|
| Seq Scan | Читает таблицу целиком, страница за страницей | Нужна большая часть строк, маленькие таблицы |
| Index Scan | Берёт TID из индекса и для каждого идёт в heap; результат приходит в порядке индекса | Нужно мало строк, особенно с ORDER BY ... LIMIT |
| Bitmap Index Scan + Bitmap Heap Scan | Сначала собирает все подходящие TID в битовую карту, потом читает страницы heap в физическом порядке | Среднее количество строк; комбинация нескольких индексов через AND/OR |
| Index Only Scan | Если все нужные колонки есть в индексе, не ходит в heap для страниц, помеченных all-visible в visibility map | Покрывающие индексы, недавно провакуумленные таблицы |
Планировщик считает стоимость по статистике (pg_stats, которую собирает ANALYZE: самые частые значения, гистограммы, n_distinct) и настройкам (seq_page_cost = 1, random_page_cost = 4 по умолчанию). Случайное чтение считается в четыре раза дороже последовательного, поэтому отказ от индекса, когда строк нужно много, — совершенно нормальное поведение.
Как читать вывод EXPLAIN ANALYZE
EXPLAIN показывает только план и оценки. EXPLAIN ANALYZE реально выполняет запрос и добавляет к каждому узлу фактическое время и количество строк. BUFFERS показывает, сколько страниц было прочитано (начиная с PostgreSQL 18 включается автоматически вместе с ANALYZE, в 16–17 его нужно указывать явно). Из Django это вызывается напрямую:
from billing.selectors import latest_paid
qs = latest_paid(42)
print(qs.query) # approximate SQL, parameters are not quoted
print(qs.explain(analyze=True, buffers=True))
Раз запрос действительно выполняется, UPDATE/DELETE проверяйте только внутри транзакции с откатом:
-- EXPLAIN ANALYZE really executes the statement.
-- For UPDATE/DELETE always wrap it in a transaction and roll back:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE billing_payment SET status = 'refunded' WHERE id = 1;
ROLLBACK;
Теперь план для крупного мерчанта (второстепенные строки сокращены):
Limit (cost=28546.98..28549.32 rows=20 width=35) (actual time=117.443..119.435 rows=20 loops=1)
Buffers: shared hit=1008 read=15993 written=1
-> Gather Merge (cost=28546.98..59875.34 rows=268510 width=35) (actual time=117.441..119.430 rows=20 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=27546.96..27882.60 rows=134255 width=35) (actual time=112.583..112.592 rows=15 loops=3)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 27kB
-> Parallel Bitmap Heap Scan on billing_payment (cost=4789.98..23974.48 rows=134255 width=35) (actual time=13.114..102.134 rows=106657 loops=3)
Recheck Cond: (merchant_id = 42)
Filter: ((status)::text = 'paid'::text)
Rows Removed by Filter: 26632
-> Bitmap Index Scan on billing_payment_merchant_id_6d2f0c1a (cost=0.00..4709.43 rows=402800 width=0) (actual time=12.912..12.912 rows=399869 loops=1)
Index Cond: (merchant_id = 42)
Planning Time: 0.380 ms
Execution Time: 119.498 ms
Правила чтения:
- Читайте дерево изнутри наружу. Самый глубокий узел со стрелкой
->выполняется первым и отдаёт результат родителю. Здесь порядок такой: Bitmap Index Scan → Bitmap Heap Scan → Sort → Gather Merge → Limit. cost=A..B— оценка планировщика в условных единицах, не в миллисекундах.A— стоимость до выдачи первой строки (startup),B— стоимость выдачи всех строк.rows— ожидаемое число строк,width— средний размер строки в байтах.actual time=A..B— реальное время в миллисекундах, но на один loop.rowsтоже на один loop.loops=3означает, что узел выполнялся трижды (здесь — лидер и два параллельных воркера), то есть реально строк106657 × 3 ≈ 320 тысяч.- Сравнивайте ожидаемые и фактические
rows. Здесь 402800 против 399869 — очень близко, статистика хорошая. Если расхождение в десятки и сотни раз, планировщик, скорее всего, принимает неверные решения; начните сANALYZE. Rows Removed by Filter— строки, которые прочитали и выбросили. Один из главных сигналов: индекс не покрывает условие полностью.Buffers— страницы по 8 КБ.hit— найдены в shared buffers,read— прочитаны извне (кэш ОС или диск). Около 17 тысяч страниц, то есть порядка 130 МБ данных — ради 20 строк.
Диагноз очевиден. Индекс обслуживает только условие merchant_id = 42 и возвращает почти 400 тысяч TID. Heap scan читает все эти строки, отбрасывает около 80 тысяч по status, оставшиеся 320 тысяч сортирует по created_at и берёт 20 самых свежих. Ни условие по status, ни ORDER BY индекс не использует.
Тот же запрос для маленького мерчанта:
Limit (cost=1151.95..1152.00 rows=20 width=35) (actual time=0.799..0.802 rows=20 loops=1)
-> Sort (cost=1151.95..1152.59 rows=256 width=35) (actual time=0.798..0.799 rows=20 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 27kB
-> Bitmap Heap Scan on billing_payment (cost=6.88..1145.14 rows=256 width=35) (actual time=0.115..0.763 rows=241 loops=1)
Recheck Cond: (merchant_id = 1777)
Filter: ((status)::text = 'paid'::text)
Rows Removed by Filter: 68
-> Bitmap Index Scan on billing_payment_merchant_id_6d2f0c1a (cost=0.00..6.82 rows=319 width=0) (actual time=0.075..0.075 rows=309 loops=1)
Index Cond: (merchant_id = 1777)
Execution Time: 0.824 ms
Форма плана та же, но индекс возвращает всего 309 строк, и всё укладывается меньше чем в миллисекунду. Вот почему локально всё летает: там просто нет «крупного мерчанта».
Важно не наличие индекса, а то, сколько строк запрос читает и сколько из них выбрасывает.
Rows Removed by Filterи большиеrowsподSort— первое, на что стоит смотреть.
Исправление: составной индекс под форму запроса
Главное правило для составного индекса: сначала колонки из условий равенства (=), затем колонка сортировки или диапазона. В нашем запросе merchant_id = ?, status = ? и ORDER BY created_at DESC. Значит, индекс должен быть (merchant_id, status, created_at DESC). В таком индексе все paid-платежи 42-го мерчанта лежат в листьях рядом и уже отсортированы по created_at. PostgreSQL спускается в нужное место, берёт первые 20 записей и останавливается.
# billing/models.py
class Payment(models.Model):
# ... fields as above ...
class Meta:
indexes = [
models.Index(
fields=["merchant", "status", "-created_at"],
name="pay_merch_status_created_idx",
),
]
makemigrations сгенерирует обычный AddIndex, а он выполняет обычный CREATE INDEX. Обычный CREATE INDEX берёт на таблицу блокировку SHARE: чтение работает, но INSERT/UPDATE/DELETE ждут, пока индекс не построится. Для таблицы платежей это неприемлемо. Поэтому заменяем операцию в сгенерированной миграции на AddIndexConcurrently:
# billing/migrations/0008_payment_merchant_status_created.py
from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models
class Migration(migrations.Migration):
atomic = False # CREATE INDEX CONCURRENTLY cannot run inside a transaction
dependencies = [
("billing", "0007_previous"),
]
operations = [
AddIndexConcurrently(
"payment",
models.Index(
fields=["merchant", "status", "-created_at"],
name="pay_merch_status_created_idx",
),
),
]
Миграция выполнит такой SQL:
CREATE INDEX CONCURRENTLY "pay_merch_status_created_idx"
ON "billing_payment" ("merchant_id", "status", "created_at" DESC);
Результат:
Limit (cost=0.43..5.36 rows=20 width=35) (actual time=0.045..0.104 rows=20 loops=1)
Buffers: shared hit=15 read=11
-> Index Scan using pay_merch_status_created_idx on billing_payment (cost=0.43..79470.67 rows=322213 width=35) (actual time=0.043..0.100 rows=20 loops=1)
Index Cond: ((merchant_id = 42) AND ((status)::text = 'paid'::text))
Buffers: shared hit=15 read=11
Planning Time: 0.233 ms
Execution Time: 0.124 ms
Что изменилось:
- Узел
Sortисчез: строки приходят из индекса уже в нужном порядке. - Нет
FilterиRows Removed by Filter: оба условия внутриIndex Cond. - Полная стоимость Index Scan — 79470, а у
Limit— 5.36. Планировщик знает, что из-заLIMIT 20сканирование остановится рано, и выбирает план с маленькой стартовой стоимостью. Buffers: 26 страниц вместо 17 тысяч.
Обратите внимание: Buffers здесь надёжнее времени выполнения. Время зависит от состояния кэша и нагрузки на сервер, а число прочитанных страниц прямо показывает, сколько работы делает запрос.
Когда индекс не помогает
Низкая селективность
Добавим отдельный индекс на status. paid — это 80% таблицы:
-- index on (status) exists; 'paid' is ~80% of the table
Seq Scan on billing_payment (cost=0.00..41667.00 rows=1591800 width=35) (actual time=0.017..300.312 rows=1600569 loops=1)
Filter: ((status)::text = 'paid'::text)
Rows Removed by Filter: 399431
-- same index, 'refunded' is ~3% of the table
Bitmap Heap Scan on billing_payment (cost=663.75..18071.58 rows=59267 width=35) (actual time=7.013..81.979 rows=59797 loops=1)
Recheck Cond: ((status)::text = 'refunded'::text)
-> Bitmap Index Scan on billing_payment_status_idx (cost=0.00..648.93 rows=59267 width=0) (actual time=3.815..3.817 rows=59797 loops=1)
Index Cond: ((status)::text = 'refunded'::text)
Индекс есть, но для paid планировщик сознательно его не использует: прочитать всю таблицу последовательно дешевле, чем 1,6 миллиона раз прыгать по TID. А для refunded (3%) тот же индекс используется. О распределении планировщик знает из most_common_vals в pg_stats. Вывод: если вы ищете только по редкому значению, логичнее поставить не полный индекс на такую колонку, а частичный (models.Index(..., condition=Q(status="refunded"))).
Функция над колонкой
B-tree отсортирован по собственным значениям колонки. Если условие наложено не на колонку, а на выражение от неё, обычный индекс не сработает. В Django это легко пропустить, потому что функцию добавляет сам ORM:
User.objects.filter(email__iexact="User777@Example.com").first()
-- what Django sends to PostgreSQL:
SELECT "accounts_user"."id", "accounts_user"."email" -- and the other columns
FROM "accounts_user"
WHERE UPPER("accounts_user"."email"::text) = UPPER('User777@Example.com')
ORDER BY "accounts_user"."id" ASC
LIMIT 1;
-- email has a UNIQUE index, but the plan is:
Parallel Seq Scan on accounts_user (cost=0.00..6802.00 rows=1042 width=30) (actual time=62.314..95.115 rows=0 loops=3)
Filter: (upper((email)::text) = 'USER777@EXAMPLE.COM'::text)
Rows Removed by Filter: 166666
На email есть UNIQUE-индекс, но он построен по значениям email, а не по UPPER(email). Решение — индекс по выражению:
# accounts/models.py
from django.contrib.auth.models import AbstractUser
from django.db import models
from django.db.models.functions import Upper
class User(AbstractUser):
email = models.EmailField(unique=True)
class Meta:
indexes = [
models.Index(Upper("email"), name="user_email_upper_idx"),
]
Index Scan using user_email_upper_idx on accounts_user (cost=0.42..8.44 rows=1 width=30) (actual time=0.031..0.032 rows=1 loops=1)
Index Cond: (upper((email)::text) = 'USER777@EXAMPLE.COM'::text)
Execution Time: 0.039 ms
То же самое с лукапами __date, __year, __month: Django превращает их в AT TIME ZONE ... и ::date. В таких случаях лучше переписать условие в диапазон по самой колонке:
from datetime import date, datetime, timedelta
from zoneinfo import ZoneInfo
# Wrong: becomes ("created_at" AT TIME ZONE 'Asia/Tashkent')::date = '2026-05-01'
Payment.objects.filter(merchant_id=42, created_at__date=date(2026, 5, 1))
# Right: a plain range on the column itself, the index can be used
start = datetime(2026, 5, 1, tzinfo=ZoneInfo("Asia/Tashkent"))
Payment.objects.filter(
merchant_id=42,
created_at__gte=start,
created_at__lt=start + timedelta(days=1),
)
Нет первой колонки составного индекса
Индекс (merchant_id, status, created_at) плохо помогает запросу WHERE status = 'paid', потому что листья отсортированы в первую очередь по merchant_id. В PostgreSQL 18 для B-tree появился skip scan, который иногда выручает, когда в первой колонке мало различных значений, но проектировать индексы в расчёте на него не стоит. Правило работает и в обратную сторону: раз новый индекс начинается с merchant_id, он может обслуживать и запросы только по merchant_id.
Wildcard в начале
Для LIKE '%foo%' (в Django __contains/__icontains) B-tree бесполезен. Для __startswith, если collation базы не C, нужен индекс с varchar_pattern_ops. Django создаёт его автоматически для CharField с db_index=True или unique=True (имя заканчивается на _like). Для поиска по подстроке нужен pg_trgm с GIN-индексом — это тема отдельной статьи.
Грабли в продакшене
EXPLAIN ANALYZEвыполняет запрос. Запустить так тяжёлый запрос на проде — значит выполнить его ещё раз. По возможности делайте это на реплике или на копии с данными продакшен-объёма.- Dev-данные врут про план. На таблице в 1000 строк планировщик справедливо выберет Seq Scan, и вы решите, что индекс не нужен. План зависит от объёма и распределения данных.
- Устаревшая статистика. После большого импорта или миграции autovacuum мог ещё не сделать
ANALYZE. Если ожидаемые и фактическиеrowsсильно расходятся, выполнитеANALYZE table_name;вручную. CONCURRENTLYможет упасть, например, если при построении уникального индекса нашёлся дубликат. Тогда в таблице остаётсяINVALID-индекс: он обновляется при записи, но не используется при чтении. Удалите его черезDROP INDEX CONCURRENTLYи создайте заново.- Каждый индекс замедляет запись и занимает место на диске. Неиспользуемые индексы ищите через
pg_stat_user_indexes, но помните, что статистика своя у каждого сервера и считается с момента последнего сброса: индекс, который используют запросы на реплике, на primary может показыватьidx_scan = 0. - Первый запуск — с холодным кэшем. Запустите несколько раз и смотрите на
Buffers, а не только на время.
Чтобы понять, какой запрос смотреть первым, удобнее всего pg_stat_statements:
-- requires shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round(total_exec_time::numeric) AS total_ms,
left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- indexes that are never used (on this server, since the last stats reset)
SELECT relname, indexrelname, idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC
LIMIT 20;
Сортировка по total_exec_time важна: запрос по 50 мс, который вызывается на каждой странице тысячи раз, нагружает базу сильнее, чем отчёт, который раз в день работает 2 секунды.
Чек-лист
- Находите медленные запросы через
pg_stat_statementsилиlog_min_duration_statement, а не угадывайте. - Запускайте
qs.explain(analyze=True, buffers=True)на данных, близких к продакшену. - Читайте план изнутри наружу; считайте
actual rows × loops. - Ищите
Rows Removed by Filter, большойSortи расхождение ожидаемых и фактическихrows. - Составной индекс: сначала колонки с
=, затем колонкаORDER BYили диапазона. - Функция над колонкой (
iexact,__date) — индекс по выражению или переписанный запрос. - Добавляйте индексы через
AddIndexConcurrentlyиatomic = False. - После изменения перепроверьте план и сравните
Buffers.
Итог
Причина медленного запроса почти всегда написана в плане: EXPLAIN ANALYZE точно показывает, где читается много строк и где много выбрасывается. Если помнить, что B-tree — это отсортированные ключи и TID, легко заранее сказать, какой индекс поможет какому запросу. Проектируйте индекс под конкретную форму запроса, а не под колонку, и проверяйте результат планом, а не догадками.
// комментарии
Загружаем комментарии...