PostgreSQL: индексы и EXPLAIN — как ускорить медленный запрос

Коротко о статье

Страница «Мои заказы» открывалась мгновенно, пока заказов было десять тысяч. Через год их два миллиона, и та же страница грузится полсекунды. Код не менялся. Изменились данные, и база начала читать всю таблицу ради двадцати строк.

Разбираем, как в такой ситуации действовать по шагам: прочитать план запроса через EXPLAIN, понять, где база тратит время, подобрать индекс и убедиться, что он помог. Примеры на PostgreSQL, условная таблица заказов на 2 млн строк.

Как читать EXPLAIN

Перед выполнением запроса планировщик PostgreSQL перебирает варианты: читать таблицу целиком или по индексу, в каком порядке соединять таблицы. Для каждого он оценивает стоимость по статистике и выбирает самый дешёвый. Команда EXPLAIN показывает выбранный план, а EXPLAIN ANALYZE ещё и выполняет запрос и добавляет фактические цифры.

• cost=0.43..80.12. Оценка стоимости в условных единицах: сколько до первой строки и сколько до последней. Это не миллисекунды, сравнивать её имеет смысл только между планами одного запроса.
• rows. Сколько строк ожидает планировщик. Рядом в ANALYZE видно, сколько получилось на самом деле. Если они расходятся в десятки раз, статистика врёт, и план, скорее всего, неудачный.
• actual time. Фактическое время в миллисекундах на один проход узла. Умножайте на loops, если узел выполнялся несколько раз.
• Порядок чтения. План читается изнутри наружу: самые вложенные узлы выполняются первыми.

Пример: до и после индекса

Запрос страницы «Мои заказы»: двадцать последних заказов клиента. Добавим BUFFERS, чтобы видеть, сколько страниц данных прочитано.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821
ORDER BY created_at DESC
LIMIT 20;

Без подходящего индекса план выглядит так:

Limit  (cost=48251.12..48251.17 rows=20 width=24) (actual time=412.533..412.538 rows=20 loops=1)
  ->  Sort  (cost=48251.12..48251.37 rows=98 width=24) (actual time=412.531..412.534 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Seq Scan on orders  (cost=0.00..48248.50 rows=98 width=24) (actual time=0.412..412.401 rows=103 loops=1)
              Filter: (customer_id = 4821)
              Rows Removed by Filter: 1999897
              Buffers: shared hit=1204 read=22041
Execution Time: 412.571 ms

Главная строка здесь Rows Removed by Filter: 1999897. Чтобы найти 103 заказа одного клиента, база прочитала все два миллиона строк и выбросила почти все. Индекс по клиенту и дате решает обе проблемы сразу, и поиск, и сортировку:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
    ON orders (customer_id, created_at DESC);

Limit  (cost=0.43..80.12 rows=20 width=24) (actual time=0.031..0.074 rows=20 loops=1)
  ->  Index Scan using orders_customer_created_idx on orders  (cost=0.43..391.05 rows=98 width=24) (actual time=0.030..0.070 rows=20 loops=1)
        Index Cond: (customer_id = 4821)
        Buffers: shared hit=23
Execution Time: 0.093 ms

Сортировка исчезла: строки в индексе уже лежат в нужном порядке, и база останавливается на двадцатой. Вместо 23 тысяч страниц данных прочитано 23. Цифры в примере условные, но соотношение типичное.

Узлы плана, которые встречаются чаще всего

Seq Scan сам по себе не ошибка. Тревожный признак другой: большая таблица, маленький результат и огромное число в Rows Removed by Filter.

Почему индекс есть, а PostgreSQL его не использует

1. Функция над колонкой. Индекс по email не поможет условию lower(email) = …. Нужен индекс по выражению: CREATE INDEX ON users (lower(email));
2. LIKE с процентом в начале. LIKE '%petrov' обычному B-tree не по силам. Для такого поиска есть расширение pg_trgm и индексы GIN.
3. Не тот порядок колонок. Индекс по (customer_id, created_at) почти бесполезен для условия только по created_at.
4. Условие выбирает слишком много. Если под фильтр попадает половина таблицы, прочитать её подряд дешевле, чем прыгать по индексу. Планировщик прав.
5. Устаревшая статистика. После массовой загрузки данных запустите ANALYZE orders; Автоматическая сборка статистики может ещё не успеть.
6. Несовпадение типов. Сравнение колонки bigint со строкой или неявное приведение типов может помешать использовать индекс.

Составные, частичные и покрывающие индексы

• Составной. Сначала колонки, которые сравниваются на равенство, потом колонка для диапазона или сортировки. Так устроен индекс из примера выше.
• Частичный. Индексирует только нужные строки, например WHERE status = 'active'. Он меньше и быстрее обновляется, если запросы почти всегда смотрят на активные записи.
• Покрывающий. С PostgreSQL 11 в индекс можно добавить колонки через INCLUDE. Тогда запрос получит все данные из индекса без чтения таблицы, и в плане появится Index Only Scan.

У каждого индекса есть цена. Он обновляется при каждой вставке и изменении, занимает место и место в кэше. Неиспользуемые индексы видно в pg_stat_user_indexes: у них idx_scan остаётся нулевым неделями.

Порядок разбора медленного запроса

Что почитать дальше

• Kafka простыми словами: партиции, гарантии доставки и кейсы финтеха
• Системный аналитик: чем занимается и что спрашивают на собеседовании
• Теоретические задачи System Design: полный разбор для собеседования
• Повышение производительности приложений: стратегии оптимизации сетевых запросов