Все заметки
Программирование

Индексы 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 BY PostgreSQL один раз спускается в нужное место, а дальше идёт по листьям последовательно. Идти можно и в обратную сторону, поэтому сортировка 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

Правила чтения:

  1. Читайте дерево изнутри наружу. Самый глубокий узел со стрелкой -> выполняется первым и отдаёт результат родителю. Здесь порядок такой: Bitmap Index Scan → Bitmap Heap Scan → Sort → Gather Merge → Limit.
  2. cost=A..B — оценка планировщика в условных единицах, не в миллисекундах. A — стоимость до выдачи первой строки (startup), B — стоимость выдачи всех строк. rows — ожидаемое число строк, width — средний размер строки в байтах.
  3. actual time=A..B — реальное время в миллисекундах, но на один loop. rows тоже на один loop. loops=3 означает, что узел выполнялся трижды (здесь — лидер и два параллельных воркера), то есть реально строк 106657 × 3 ≈ 320 тысяч.
  4. Сравнивайте ожидаемые и фактические rows. Здесь 402800 против 399869 — очень близко, статистика хорошая. Если расхождение в десятки и сотни раз, планировщик, скорее всего, принимает неверные решения; начните с ANALYZE.
  5. Rows Removed by Filter — строки, которые прочитали и выбросили. Один из главных сигналов: индекс не покрывает условие полностью.
  6. 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, легко заранее сказать, какой индекс поможет какому запросу. Проектируйте индекс под конкретную форму запроса, а не под колонку, и проверяйте результат планом, а не догадками.

// комментарии

Загружаем комментарии...

    // следующий шаг

    Давайте построим что-нибудь вместе.

    Написать в Telegram
    aslbek@asldev: ~
    ❯