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

Коротко

Медленный запрос сначала находят — через pg_stat_statements или лог с log_min_duration_statement, — затем смотрят его план командой EXPLAIN (ANALYZE, BUFFERS). В плане ищут узел, где уходит больше всего времени и страниц, и сравнивают оценку строк (rows) с реальностью (actual rows): расхождение в разы почти всегда объясняет плохой выбор планировщика. Типичные исправления — индекс под условие или соединение, переписывание условия без функции над столбцом, NOT EXISTS вместо NOT IN, свежая статистика через ANALYZE и расширенная статистика для зависимых столбцов.

РуководствоУровень: ПродвинутыйОптимизация запросовПрактика: ~2–4 ч

После материала вы сможете

найти медленные и частые запросы через pg_stat_statements и log_min_duration_statement
прочитать план EXPLAIN (ANALYZE, BUFFERS): стоимость, реальное время, loops, буферы и узлы сканирования и соединения
найти расхождение оценки и реальности и исправить его через ANALYZE или CREATE STATISTICS
применить типовые исправления: индекс на внешний ключ, условие без функции над столбцом, NOT EXISTS, устранение N+1

Что знать заранее

Индексы: как ускорить запрос и не замедлить запись

Проверено на

PostgreSQL 17 (17.11, postgres:17-alpine)pg_stat_statements 1.11

Сверено с документацией: 11 октября 2026 г.

Содержание

01Как найти медленные запросы
02Как читать план EXPLAIN
03Запросы 2 и 3: условие без функции и NOT EXISTS вместо NOT IN
04Оценки и реальность: статистика и ANALYZE
05N+1 из ORM и pg_stat_statements
06Типовые исправления и порядок работы
07Упражнения

Как найти медленные запросы

Оптимизировать вслепую бессмысленно: сначала нужен список запросов, которые действительно тратят время базы. У PostgreSQL для этого два штатных инструмента — расширение pg_stat_statements и журнал сервера.

Инструмент	Что даёт	Как включить
`pg_stat_statements`	Сводку по всем запросам: число вызовов, суммарное и среднее время, строки, буферы. Константы заменяются на `$1`, поэтому одинаковые запросы с разными значениями складываются в одну строку	`shared_preload_libraries = 'pg_stat_statements'`, перезапуск сервера, затем `CREATE EXTENSION pg_stat_statements` в базе
`log_min_duration_statement`	Запись в лог каждого запроса дольше порога вместе с его текстом	Например, `250ms`; `0` логирует всё, `-1` (по умолчанию) — выключено
`auto_explain`	Планы медленных запросов прямо в логе	Модуль из стандартной поставки, загружается так же, через `shared_preload_libraries` или `LOAD`

pg_stat_statements

Что даёт: Сводку по всем запросам: число вызовов, суммарное и среднее время, строки, буферы. Константы заменяются на $1, поэтому одинаковые запросы с разными значениями складываются в одну строку
Как включить: shared_preload_libraries = 'pg_stat_statements', перезапуск сервера, затем CREATE EXTENSION pg_stat_statements в базе

log_min_duration_statement

Что даёт: Запись в лог каждого запроса дольше порога вместе с его текстом
Как включить: Например, 250ms; 0 логирует всё, -1 (по умолчанию) — выключено

auto_explain

Что даёт: Планы медленных запросов прямо в логе
Как включить: Модуль из стандартной поставки, загружается так же, через shared_preload_libraries или LOAD

Учебный стенд — магазин на 100 000 клиентов и миллион заказов в PostgreSQL 17. Контейнер сразу запускаем с pg_stat_statements и порогом лога 200 мс.

bash

docker run -d --rm --name kb-pg-explain -e POSTGRES_PASSWORD=test postgres:17-alpine \
  -c shared_preload_libraries=pg_stat_statements -c log_min_duration_statement=200
docker exec -i kb-pg-explain psql -U postgres -f - < 01-seed.sql

Стенд. Пароль test — только для локального одноразового контейнера; перенаправление < работает в bash и Git Bash

01-seed.sql

-- Учебная база магазина: 100 000 клиентов, 1 000 000 заказов
DROP TABLE IF EXISTS orders, customers;

CREATE TABLE customers (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  city       text NOT NULL,
  region     text NOT NULL,           -- однозначно определяется городом
  created_at timestamptz NOT NULL
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),  -- индекс на FK сам не создаётся
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
CREATE INDEX orders_created_at_idx ON orders (created_at);

SELECT setseed(0.17);

-- 50 городов в 10 регионах: город n принадлежит региону n % 10
INSERT INTO customers (email, city, region, created_at)
SELECT 'user' || g || '@example.com',
       'city-' || c,
       'region-' || (c % 10),
       timestamptz '2024-01-01' + random() * interval '700 days'
FROM (SELECT g, floor(random() * 50)::int AS c FROM generate_series(1, 100000) AS g) AS s;

-- примерно 15 % клиентов без заказов
INSERT INTO orders (customer_id, status, total, created_at)
SELECT 1 + floor(random() * 85000)::int,
       (ARRAY['new', 'paid', 'shipped', 'done', 'done', 'done', 'cancelled'])[1 + floor(random() * 7)::int],
       round((500 + random() * 49500)::numeric, 2),
       timestamptz '2025-01-01' + random() * interval '365 days'
FROM generate_series(1, 1000000);

VACUUM ANALYZE customers, orders;

SELECT relname, n_live_tup, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables ORDER BY relname;

Клиенты, заказы и индекс по дате; данные воспроизводимы через setseed

terminal

$ docker logs kb-pg-explain 2>&1 | grep -E 'duration|ERROR'
LOG:  duration: 277.542 ms  statement: INSERT INTO customers (email, city, region, created_at)
LOG:  duration: 16909.251 ms  statement: INSERT INTO orders (customer_id, status, total, created_at)
ERROR:  canceling statement due to statement timeout
LOG:  duration: 1186.990 ms  statement: INSERT INTO orders (customer_id, status, total, created_at)

Фрагмент лога после всех примеров: медленные запросы попадают туда сами (дата и PID убраны)

Как читать план EXPLAIN

План — дерево узлов. Внизу — узлы сканирования, которые достают строки из таблиц; выше — соединения, сортировки и агрегаты. Читать его удобно изнутри наружу: самый глубоко вложенный узел выполняется первым, а стоимость и время каждого узла включают его дочерние узлы.

02-join-fk.sql

-- Запрос 1: заказы клиента по email
\echo '=== ДО: на orders.customer_id нет индекса'
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.status, o.total, o.created_at
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.email = 'user4242@example.com';

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

\echo '=== ПОСЛЕ: индекс на внешний ключ'
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.status, o.total, o.created_at
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.email = 'user4242@example.com';

Запрос 1: заказы клиента по email — до и после индекса на внешний ключ

terminal

$ docker exec -i kb-pg-explain psql -U postgres -f - < 02-join-fk.sql
=== ДО: на orders.customer_id нет индекса
 Gather  (cost=1008.45..14880.91 rows=10 width=30) (actual time=3.742..29.184 rows=8 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=8720 read=3
   ->  Hash Join  (cost=8.45..13879.91 rows=4 width=30) (actual time=3.414..23.601 rows=3 loops=3)
         Hash Cond: (o.customer_id = c.id)
         Buffers: shared hit=8720 read=3
         ->  Parallel Seq Scan on orders o  (cost=0.00..12777.67 rows=416667 width=38) (actual time=0.005..11.550 rows=333333 loops=3)
               Buffers: shared hit=8611
         ->  Hash  (cost=8.44..8.44 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=3)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               Buffers: shared hit=11 read=3
               ->  Index Scan using customers_email_key on customers c  (cost=0.42..8.44 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=3)
                     Index Cond: (email = 'user4242@example.com'::text)
                     Buffers: shared hit=11 read=3
 Planning:
   Buffers: shared hit=264 read=9
 Planning Time: 1.164 ms
 Execution Time: 29.264 ms

План до исправления. Время — пример на нашей машине

Фрагмент	Что означает
`cost=1008.45..14880.91`	Оценка стоимости в условных единицах: до первой строки и до последней. Сравнивать её с миллисекундами нельзя
`rows=10 width=30`	Сколько строк узел, по оценке, вернёт и их средняя ширина в байтах
`actual time=3.742..29.184`	Реальное время в миллисекундах: до первой строки и до последней
`rows=3 loops=3`	Реальные строки и число выполнений узла. Время и строки — средние за одно выполнение: умножайте на `loops`
`Buffers: shared hit=8720 read=3`	Страницы по 8 КБ: `hit` — из кэша PostgreSQL, `read` — прочитаны с диска или из кэша ОС
`Rows Removed by Filter`	Сколько строк узел прочитал и отбросил — признак того, что условию не хватает индекса
`Planning Time` / `Execution Time`	Время построения плана и выполнения запроса без передачи результата клиенту

cost=1008.45..14880.91

Что означает: Оценка стоимости в условных единицах: до первой строки и до последней. Сравнивать её с миллисекундами нельзя

rows=10 width=30

Что означает: Сколько строк узел, по оценке, вернёт и их средняя ширина в байтах

actual time=3.742..29.184

Что означает: Реальное время в миллисекундах: до первой строки и до последней

rows=3 loops=3

Что означает: Реальные строки и число выполнений узла. Время и строки — средние за одно выполнение: умножайте на loops

Buffers: shared hit=8720 read=3

Что означает: Страницы по 8 КБ: hit — из кэша PostgreSQL, read — прочитаны с диска или из кэша ОС

Rows Removed by Filter

Что означает: Сколько строк узел прочитал и отбросил — признак того, что условию не хватает индекса

Planning Time / Execution Time

Что означает: Время построения плана и выполнения запроса без передачи результата клиенту

В плане до исправления весь запрос тратит время на Parallel Seq Scan on orders: три процесса читают по 333 тысячи строк, чтобы Hash Join нашёл восемь заказов. Внешний ключ не создаёт индекс автоматически — документация прямо говорит, что индекс на ссылающиеся столбцы нужно объявить самому. После CREATE INDEX план становится Nested Loop: для одной найденной строки клиента выполняется точечный поиск по индексу заказов.

terminal

=== ПОСЛЕ: индекс на внешний ключ
 Nested Loop  (cost=4.94..63.74 rows=10 width=30) (actual time=0.035..0.047 rows=8 loops=1)
   Buffers: shared hit=14 read=1
   ->  Index Scan using customers_email_key on customers c  (cost=0.42..8.44 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)
         Index Cond: (email = 'user4242@example.com'::text)
         Buffers: shared hit=4
   ->  Bitmap Heap Scan on orders o  (cost=4.53..55.17 rows=13 width=38) (actual time=0.021..0.032 rows=8 loops=1)
         Recheck Cond: (c.id = customer_id)
         Heap Blocks: exact=8
         Buffers: shared hit=10 read=1
         ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.52 rows=13 width=0) (actual time=0.012..0.012 rows=8 loops=1)
               Index Cond: (customer_id = c.id)
               Buffers: shared hit=2 read=1
 Planning:
   Buffers: shared hit=24 read=6
 Planning Time: 0.269 ms
 Execution Time: 0.076 ms

План после: 15 страниц вместо 8 723

Узел	Когда планировщик его выбирает
Seq Scan	Нужна большая доля таблицы, нет подходящего индекса или таблица маленькая
Index Scan / Index Only Scan	Мало строк или нужен порядок индекса; Only — если все столбцы есть в индексе
Bitmap Index Scan + Bitmap Heap Scan	Средняя выборка: адреса строк собираются в битовую карту и читаются в физическом порядке
Nested Loop	Внешний вход маленький, а для внутреннего есть индекс по условию соединения
Hash Join	Оба входа большие, условие — равенство; меньший вход превращается в хеш-таблицу в памяти
Merge Join	Оба входа уже отсортированы по ключу соединения (например, индексами)

Seq Scan

Когда планировщик его выбирает: Нужна большая доля таблицы, нет подходящего индекса или таблица маленькая

Index Scan / Index Only Scan

Когда планировщик его выбирает: Мало строк или нужен порядок индекса; Only — если все столбцы есть в индексе

Bitmap Index Scan + Bitmap Heap Scan

Когда планировщик его выбирает: Средняя выборка: адреса строк собираются в битовую карту и читаются в физическом порядке

Nested Loop

Когда планировщик его выбирает: Внешний вход маленький, а для внутреннего есть индекс по условию соединения

Hash Join

Когда планировщик его выбирает: Оба входа большие, условие — равенство; меньший вход превращается в хеш-таблицу в памяти

Merge Join

Когда планировщик его выбирает: Оба входа уже отсортированы по ключу соединения (например, индексами)

Запросы 2 и 3: условие без функции и NOT EXISTS вместо NOT IN

Второй медленный запрос считает выручку за день и приводит created_at к дате. Индекс по created_at при этом не используется: в нём лежат метки времени, а не результат created_at::date. Тот же день, записанный диапазоном, использует индекс напрямую.

03-sargable.sql

-- Запрос 2: выручка за день
\echo '=== ДО: функция над столбцом — индекс по created_at не подходит'
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), sum(total)
FROM orders
WHERE created_at::date = '2025-06-01';

\echo '=== ПОСЛЕ: тот же день как диапазон'
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), sum(total)
FROM orders
WHERE created_at >= '2025-06-01' AND created_at < '2025-06-02';

-- Результат не изменился (сессия в UTC, как и границы диапазона)
SELECT (SELECT count(*) FROM orders WHERE created_at::date = '2025-06-01') AS by_cast,
       (SELECT count(*) FROM orders
        WHERE created_at >= '2025-06-01' AND created_at < '2025-06-02') AS by_range;

Запрос 2: функция над столбцом против диапазона

terminal

$ docker exec -i kb-pg-explain psql -U postgres -f - < 03-sargable.sql
=== ДО: функция над столбцом — индекс по created_at не подходит
 Finalize Aggregate  (cost=15871.65..15871.66 rows=1 width=40) (actual time=24.543..27.143 rows=1 loops=1)
   Buffers: shared hit=8611
   ->  Gather  (cost=15871.42..15871.63 rows=2 width=40) (actual time=24.389..27.131 rows=3 loops=1)
         Buffers: shared hit=8611
         ->  Partial Aggregate  (cost=14871.42..14871.43 rows=1 width=40) (actual time=22.536..22.537 rows=1 loops=3)
               Buffers: shared hit=8611
               ->  Parallel Seq Scan on orders  (cost=0.00..14861.00 rows=2083 width=8) (actual time=0.016..22.422 rows=892 loops=3)
                     Filter: ((created_at)::date = '2025-06-01'::date)
                     Rows Removed by Filter: 332442
                     Buffers: shared hit=8611
 Planning Time: 0.534 ms
 Execution Time: 27.193 ms

=== ПОСЛЕ: тот же день как диапазон
 Aggregate  (cost=5615.89..5615.90 rows=1 width=40) (actual time=2.811..2.811 rows=1 loops=1)
   Buffers: shared hit=2301
   ->  Bitmap Heap Scan on orders  (cost=66.54..5603.15 rows=2548 width=8) (actual time=0.463..2.560 rows=2675 loops=1)
         Recheck Cond: ((created_at >= '2025-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-06-02 00:00:00+00'::timestamp with time zone))
         Heap Blocks: exact=2288
         Buffers: shared hit=2301
         ->  Bitmap Index Scan on orders_created_at_idx  (cost=0.00..65.91 rows=2548 width=0) (actual time=0.291..0.291 rows=2675 loops=1)
               Index Cond: ((created_at >= '2025-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-06-02 00:00:00+00'::timestamp with time zone))
               Buffers: shared hit=13
 Planning Time: 0.129 ms
 Execution Time: 2.851 ms

 by_cast | by_range
---------+----------
    2675 |     2675
(1 row)

Вывод сокращён: 8 611 страниц против примерно 2 300 и одинаковый результат

Приведение к дате зависит от часового пояса сессии, поэтому границы диапазона должны быть в том же поясе — проверка в конце скрипта это подтверждает. Альтернатива — индекс по выражению, но он подходит, только если запрос пишет выражение ровно так же.

Третий запрос ищет клиентов без заказов через NOT IN (подзапрос). Когда результат подзапроса не помещается в work_mem для хешированного варианта, PostgreSQL проверяет каждую строку клиентов перебором — оценка стоимости уходит в сотни миллионов.

04-not-in.sql

-- Запрос 3: клиенты без заказов
\echo '=== ДО: NOT IN с подзапросом — план без ANALYZE'
EXPLAIN
SELECT count(*)
FROM customers c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders o);

SET statement_timeout = '10s';
SELECT count(*)
FROM customers c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders o);
RESET statement_timeout;

\echo '=== ПОСЛЕ: NOT EXISTS'
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Запрос 3: NOT IN против NOT EXISTS; statement_timeout страхует от бесконечного ожидания

terminal

$ docker exec -i kb-pg-explain psql -U postgres -f - < 04-not-in.sql
=== ДО: NOT IN с подзапросом — план без ANALYZE
 Finalize Aggregate  (cost=625378177.42..625378177.43 rows=1 width=8)
   ->  Gather  (cost=625378177.21..625378177.42 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=625377177.21..625377177.22 rows=1 width=8)
               ->  Parallel Index Only Scan using customers_pkey on customers c  (cost=0.29..625377125.13 rows=20833 width=0)
                     Filter: (NOT (ANY (id = (SubPlan 1).col1)))
                     SubPlan 1
                       ->  Materialize  (cost=0.00..27518.00 rows=1000000 width=8)
                             ->  Seq Scan on orders o  (cost=0.00..18611.00 rows=1000000 width=8)
 JIT:
   Functions: 8
   Options: Inlining true, Optimization true, Expressions true, Deforming true

SET
psql:<stdin>:11: ERROR:  canceling statement due to statement timeout
RESET

=== ПОСЛЕ: NOT EXISTS
 Finalize Aggregate  (cost=20931.19..20931.20 rows=1 width=8) (actual time=73.489..78.491 rows=1 loops=1)
   Buffers: shared hit=9808
   ->  Gather  (cost=20930.97..20931.18 rows=2 width=8) (actual time=70.581..78.484 rows=3 loops=1)
         Buffers: shared hit=9808
         ->  Partial Aggregate  (cost=19930.97..19930.98 rows=1 width=8) (actual time=58.105..58.107 rows=1 loops=3)
               Buffers: shared hit=9808
               ->  Parallel Hash Right Anti Join  (cost=2354.54..19909.58 rows=8555 width=0) (actual time=57.248..57.943 rows=5001 loops=3)
                     Hash Cond: (o.customer_id = c.id)
                     Buffers: shared hit=9808
                     ->  Parallel Seq Scan on orders o  (cost=0.00..12777.67 rows=416667 width=8) (actual time=0.009..15.220 rows=333333 loops=3)
                           Buffers: shared hit=8611
                     ->  Parallel Hash  (cost=1619.24..1619.24 rows=58824 width=8) (actual time=5.286..5.286 rows=33333 loops=3)
                           Buckets: 131072  Batches: 1  Memory Usage: 4960kB
                           Buffers: shared hit=1031
                           ->  Parallel Seq Scan on customers c  (cost=0.00..1619.24 rows=58824 width=8) (actual time=0.012..5.686 rows=100000 loops=1)
                                 Buffers: shared hit=1031
 Planning Time: 0.939 ms
 Execution Time: 78.579 ms

Вывод сокращён: NOT IN не уложился в 10 секунд, NOT EXISTS превратился в Hash Anti Join

Оценки и реальность: статистика и ANALYZE

Планировщик выбирает план по оценкам числа строк, а оценки берёт из статистики: reltuples и relpages в pg_class и распределения значений в pg_statistic (удобнее смотреть через pg_stats). Статистику собирает ANALYZE — вручную или фоновым autovacuum. Документация называет главное, на что смотреть в EXPLAIN ANALYZE: насколько оценка строк близка к реальности.

Опыт 1: после массовой загрузки 60 000 заказов со статусом refund статистика ещё не знает о новом значении. Autovacuum для таблицы выключен только затем, чтобы он не успел запустить ANALYZE сам.

05-stats.sql

-- Оценки и реальность 1: устаревшая статистика после массовой загрузки
ALTER TABLE orders SET (autovacuum_enabled = off);   -- только для воспроизводимости опыта
SELECT setseed(0.5);

INSERT INTO orders (customer_id, status, total, created_at)
SELECT 1 + floor(random() * 85000)::int, 'refund', 990.00,
       timestamptz '2025-12-01' + random() * interval '30 days'
FROM generate_series(1, 60000);

\echo '=== ДО ANALYZE: о статусе refund планировщик ещё не знает'
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.city, count(*)
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'refund'
GROUP BY c.city;

ANALYZE orders;

\echo '=== ПОСЛЕ ANALYZE'
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.city, count(*)
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'refund'
GROUP BY c.city;

ALTER TABLE orders RESET (autovacuum_enabled);

Устаревшая статистика после массовой загрузки

terminal

$ docker exec -i kb-pg-explain psql -U postgres -f - < 05-stats.sql
=== ДО ANALYZE: о статусе refund планировщик ещё не знает
 GroupAggregate  (cost=15630.18..15630.20 rows=1 width=15) (actual time=73.236..77.260 rows=50 loops=1)
   Group Key: c.city
   Buffers: shared hit=189114
   ->  Sort  (cost=15630.18..15630.18 rows=1 width=7) (actual time=73.145..74.659 rows=60000 loops=1)
         Sort Key: c.city
         Sort Method: quicksort  Memory: 1537kB
         Buffers: shared hit=189114
         ->  Nested Loop  (cost=1000.29..15630.17 rows=1 width=7) (actual time=12.064..61.700 rows=60000 loops=1)
               Buffers: shared hit=189111
               ->  Gather  (cost=1000.00..15621.86 rows=1 width=8) (actual time=12.035..15.321 rows=60000 loops=1)
                     Buffers: shared hit=9111
                     ->  Parallel Seq Scan on orders o  (cost=0.00..14621.76 rows=1 width=8) (actual time=10.219..13.308 rows=20000 loops=3)
                           Filter: (status = 'refund'::text)
                           Rows Removed by Filter: 333333
                           Buffers: shared hit=9111
               ->  Index Scan using customers_pkey on customers c  (cost=0.29..8.31 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=60000)
                     Index Cond: (id = o.customer_id)
                     Buffers: shared hit=180000
 Planning Time: 0.697 ms
 Execution Time: 77.378 ms

=== ПОСЛЕ ANALYZE
 Finalize GroupAggregate  (cost=18179.59..18192.26 rows=50 width=15) (actual time=25.477..28.630 rows=50 loops=1)
   Group Key: c.city
   Buffers: shared hit=10188
   ->  Gather Merge  (cost=18179.59..18191.26 rows=100 width=15) (actual time=25.471..28.613 rows=150 loops=1)
         Buffers: shared hit=10188
         ->  Sort  (cost=17179.57..17179.69 rows=50 width=15) (actual time=23.496..23.500 rows=50 loops=3)
               Sort Key: c.city
               Sort Method: quicksort  Memory: 26kB
               Buffers: shared hit=10188
               ->  Partial HashAggregate  (cost=17177.66..17178.16 rows=50 width=15) (actual time=23.447..23.451 rows=50 loops=3)
                     Group Key: c.city
                     Buffers: shared hit=10172
                     ->  Parallel Hash Join  (cost=2354.54..17052.22 rows=25087 width=7) (actual time=16.102..21.541 rows=20000 loops=3)
                           Hash Cond: (o.customer_id = c.id)
                           Buffers: shared hit=10172
                           ->  Parallel Seq Scan on orders o  (cost=0.00..14631.83 rows=25087 width=8) (actual time=9.531..12.243 rows=20000 loops=3)
                                 Filter: (status = 'refund'::text)
                                 Rows Removed by Filter: 333333
                                 Buffers: shared hit=9111
                           ->  Parallel Hash  (cost=1619.24..1619.24 rows=58824 width=15) (actual time=6.338..6.339 rows=33333 loops=3)
                                 Buffers: shared hit=1031
                                 ->  Parallel Seq Scan on customers c  (cost=0.00..1619.24 rows=58824 width=15) (actual time=0.007..1.625 rows=33333 loops=3)
                                       Buffers: shared hit=1031
 Planning Time: 0.336 ms
 Execution Time: 28.700 ms

Вывод сокращён. До ANALYZE: оценка rows=1, реально 60 000 строк и 60 000 повторов Index Scan

Планировщик ждал одну строку и выбрал Nested Loop: для каждой строки заказа — поиск клиента по индексу. На деле строк 60 000, и узел Index Scan выполнился 60 000 раз (loops=60000), прочитав 180 000 страниц. После ANALYZE оценка стала близкой к реальной, и план переключился на Hash Join: таблица клиентов читается один раз.

Опыт 2: планировщик по умолчанию считает условия независимыми и перемножает их селективности. Если регион однозначно определяется городом, оценка занижается примерно в 10 раз. Расширенная статистика CREATE STATISTICS … (dependencies) сообщает ему о зависимости.

06-extended-stats.sql

-- Оценки и реальность 2: зависимые столбцы (регион определяется городом)
\echo '=== ДО: планировщик считает условия независимыми'
EXPLAIN (ANALYZE)
SELECT id FROM customers WHERE city = 'city-7' AND region = 'region-7';

CREATE STATISTICS customers_city_region (dependencies) ON city, region FROM customers;
ANALYZE customers;

\echo '=== ПОСЛЕ: расширенная статистика о зависимости'
EXPLAIN (ANALYZE)
SELECT id FROM customers WHERE city = 'city-7' AND region = 'region-7';

Расширенная статистика для зависимых столбцов

terminal

$ docker exec -i kb-pg-explain psql -U postgres -f - < 06-extended-stats.sql
=== ДО: планировщик считает условия независимыми
 Seq Scan on customers  (cost=0.00..2531.00 rows=194 width=8) (actual time=0.014..4.145 rows=1991 loops=1)
   Filter: ((city = 'city-7'::text) AND (region = 'region-7'::text))
   Rows Removed by Filter: 98009
 Planning Time: 0.415 ms
 Execution Time: 4.213 ms

=== ПОСЛЕ: расширенная статистика о зависимости
 Seq Scan on customers  (cost=0.00..2531.00 rows=1930 width=8) (actual time=0.010..3.743 rows=1991 loops=1)
   Filter: ((city = 'city-7'::text) AND (region = 'region-7'::text))
   Rows Removed by Filter: 98009
 Planning Time: 0.175 ms
 Execution Time: 3.790 ms

Оценка строк до и после CREATE STATISTICS при одинаковом реальном числе

Оценка отличается от реальности в разы на нижних узлах — сначала ANALYZE таблицы, затем проверка autovacuum (pg_stat_user_tables.last_autoanalyze).
Неравномерное распределение — увеличить детализацию статистики столбца: ALTER TABLE … ALTER COLUMN … SET STATISTICS 500 (по умолчанию 100) и снова ANALYZE.
Связанные столбцы в одном условии или GROUP BY — CREATE STATISTICS с dependencies или ndistinct.
После массовой загрузки — ANALYZE сразу, не дожидаясь autovacuum; документация советует это в разделе о наполнении базы.

N+1 из ORM и pg_stat_statements

N+1 — когда код сначала получает список (1 запрос), а потом для каждого элемента отдельно догружает связанные данные (N запросов). ORM делает это незаметно при «ленивой» загрузке связей. Каждый такой запрос быстрый, поэтому в логе медленных запросов его не видно, зато в pg_stat_statements он выдаёт себя числом вызовов.

07-n-plus-one.sql

-- Как ORM порождает N+1 и как это видно в pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT pg_stat_statements_reset() IS NOT NULL AS reset;

-- N+1: сначала список клиентов, потом по запросу на каждого (100 запросов)
\o /dev/null
SELECT format('SELECT id, total FROM orders WHERE customer_id = %s', id)
FROM customers ORDER BY id LIMIT 100 \gexec
\o

-- То же одним запросом
\o /dev/null
SELECT o.customer_id, o.id, o.total
FROM orders o
WHERE o.customer_id = ANY (ARRAY(SELECT id FROM customers ORDER BY id LIMIT 100));
\o

SELECT calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 3)  AS mean_ms,
       rows,
       regexp_replace(query, '\s+', ' ', 'g') AS query
FROM pg_stat_statements
WHERE query LIKE '%FROM orders%' AND query NOT LIKE '%pg_stat_statements%'
ORDER BY calls DESC;

Имитация N+1 через \gexec: psql выполняет каждую строку результата как отдельный запрос

terminal

$ docker exec -i kb-pg-explain psql -U postgres -f - < 07-n-plus-one.sql
 calls | total_ms | mean_ms | rows |                                                               query
-------+----------+---------+------+------------------------------------------------------------------------------------------------------------------------------------
   100 |     4.17 |   0.042 | 1254 | SELECT id, total FROM orders WHERE customer_id = $1
     1 |     1.08 |   1.083 | 1254 | SELECT o.customer_id, o.id, o.total FROM orders o WHERE o.customer_id = ANY (ARRAY(SELECT id FROM customers ORDER BY id LIMIT $1))
(2 rows)

Вывод сокращён: 100 вызовов одного шаблона против одного запроса с тем же числом строк

Внутри базы сто коротких запросов заняли несколько миллисекунд, и это обманчиво: pg_stat_statements не учитывает сетевые задержки, а каждый из ста запросов — это отдельный круг «приложение — база». Исправление — загрузить связи заранее: JOIN, WHERE id = ANY($1) или режим жадной загрузки в ORM (например, include, select_related, JOIN FETCH — в зависимости от библиотеки).

SQL

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Ежедневный запрос: 10 запросов, которые суммарно тратят больше всего времени (по образцу из документации)

Типовые исправления и порядок работы

Найти
Топ по total_exec_time в pg_stat_statements или лог log_min_duration_statement. Оптимизируют то, что суммарно дороже, а не самый медленный одиночный запрос.
Воспроизвести
Тот же запрос с реальными параметрами и на данных близкого объёма: документация предупреждает, что план на игрушечной таблице не переносится на большую.
Прочитать план
EXPLAIN (ANALYZE, BUFFERS): самый дорогой узел (время × loops, буферы) и расхождение rows с actual rows.
Исправить одну причину
Индекс, переписывание или статистика — по одному изменению за раз, чтобы видеть эффект каждого.
Проверить
Снова план и снова pg_stat_statements через время: среднее время и буферы должны снизиться, а запись — не пострадать.

Симптом в плане	Вероятная причина	Исправление
`Seq Scan` и большое `Rows Removed by Filter`	Нет индекса под условие	Индекс; см. урок об индексах
`Hash Join` с `Seq Scan` большой таблицы ради нескольких строк	Нет индекса на столбец соединения (часто FK)	Индекс на внешний ключ
Индекс есть, но `Filter: (func(col) = …)`	Функция или приведение типа над столбцом	Диапазон без функции или индекс по выражению
`rows=1`, а `actual rows` — тысячи; `Nested Loop` с огромным `loops`	Устаревшая статистика	`ANALYZE`
Оценка занижена при нескольких условиях на связанные столбцы	Независимость условий	`CREATE STATISTICS`
`SubPlan` в фильтре и стоимость в миллионах	`NOT IN` или коррелированный подзапрос	`NOT EXISTS`, `JOIN`
`Sort Method: external merge Disk`	Сортировке не хватило `work_mem`	Индекс под `ORDER BY`, меньше строк или `work_mem` для сессии — с замером

Seq Scan и большое Rows Removed by Filter

Вероятная причина: Нет индекса под условие
Исправление: Индекс; см. урок об индексах

Hash Join с Seq Scan большой таблицы ради нескольких строк

Вероятная причина: Нет индекса на столбец соединения (часто FK)
Исправление: Индекс на внешний ключ

Индекс есть, но Filter: (func(col) = …)

Вероятная причина: Функция или приведение типа над столбцом
Исправление: Диапазон без функции или индекс по выражению

rows=1, а actual rows — тысячи; Nested Loop с огромным loops

Вероятная причина: Устаревшая статистика
Исправление: ANALYZE

Оценка занижена при нескольких условиях на связанные столбцы

Вероятная причина: Независимость условий
Исправление: CREATE STATISTICS

SubPlan в фильтре и стоимость в миллионах

Вероятная причина: NOT IN или коррелированный подзапрос
Исправление: NOT EXISTS, JOIN

Sort Method: external merge Disk

Вероятная причина: Сортировке не хватило work_mem
Исправление: Индекс под ORDER BY, меньше строк или work_mem для сессии — с замером

pg_stat_statementsEXPLAIN (ANALYZE, BUFFERS)ПричинаОдно исправлениеЗамер

Цикл оптимизации

Упражнения

ПовториНайдите дорогой узел

Поднимите стенд и выполните 02-join-fk.sql. В плане до исправления найдите узел с наибольшим actual time с учётом loops и объясните, почему Hash построен по таблице клиентов, а не заказов.

Подсказка

Сравните оценки rows двух входов Hash Join.

Решение

Дорогой узел — Parallel Seq Scan on orders: около 12 мс в каждом из трёх процессов и 8 611 страниц из 8 723. Хеш-таблицу строят по меньшему входу: по оценке это одна строка клиента против 416 667 строк заказов на процесс.

ИзмениНеделя без функции

Отчёт за неделю написан как WHERE date_trunc('week', created_at) = '2025-06-02'. Перепишите условие так, чтобы использовался индекс orders_created_at_idx, и сравните планы и число страниц.

Подсказка

Неделя — это полуоткрытый интервал из семи дней.

Решение

SQL

SELECT count(*) FROM orders
WHERE created_at >= '2025-06-02' AND created_at < '2025-06-09';

ПримениУберите N+1

Страница «Последние заказы» загружает 20 заказов, а затем для каждого отдельным запросом — email клиента. Напишите один запрос с тем же результатом и подтвердите в pg_stat_statements, что он выполнился один раз.

Подсказка

Сбросьте статистику pg_stat_statements_reset() перед проверкой.

Решение

SQL

SELECT o.id, o.total, o.created_at, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.created_at DESC
LIMIT 20;

Дальше по теме — транзакции и изоляция: долгие запросы удлиняют транзакции, держат блокировки и увеличивают число конфликтов.

Как проверено4 · пройденосверено 11 октября 2026 г.

запущено, работает
01-seed.sql … 08-exercises.sql (RU и EN варианты комментариев)
docker postgres:17-alpine (17.11) с -c shared_preload_libraries=pg_stat_statements -c log_min_duration_statement=200, свежий контейнер на каждый язык, psql -f по порядку; 04-not-in намеренно содержит ошибку statement_timeout (ожидаемый результат). Выводы и фрагмент серверного лога вставлены в статью скриптом сборки из файлов out-ru/out-en
запущено, работает
утверждения о планах: Hash Join + Parallel Seq Scan → Nested Loop + Bitmap (FK-индекс); ::date → Seq Scan, диапазон → Bitmap Heap Scan, результаты равны; NOT IN — стоимость ~6,25e8 и таймаут 10 с, NOT EXISTS → Parallel Hash Right Anti Join; rows=1 vs 60000 и Nested Loop loops=60000 → Hash Join после ANALYZE; CREATE STATISTICS: оценка ≈190–210 → ≈1890–2100 при 1991 реальных (зависит от выборки ANALYZE); N+1: calls=100 против 1
сверено с выводом трёх прогонов (отладочный и два финальных); время — как пример на этой машине
запущено, работает
work_mem: внешняя сортировка при 4MB против quicksort при 64MB (утверждение в callout)
extra/08-work-mem.sql, три прогона: external merge ≈ 600–690 мс, quicksort ≈ 780–920 мс; в статью вынесен только вывод
запущено, работает
SQL-запрос «топ-10 по total_exec_time»
extra/09-top.sql на отдельном свежем контейнере с pg_stat_statements после 01-seed, 02-join-fk, 07-n-plus-one: 5 строк, без ошибок; extversion 1.11

«Запущено» — код выполнен во временном проекте или контейнере, вывод в статье взят из прогона. «Частично» — проверено с заглушками вместо внешних сервисов. «Не запускалось» — нужны устройство, аккаунт или ключ; шаги сверены с официальной документацией. Файлы проверочного проекта и логи запусков сохранены в архиве InterLAB.

Оптимизация запросов: EXPLAIN ANALYZE на практике

Как найти медленные запросы

Как читать план EXPLAIN

Запросы 2 и 3: условие без функции и NOT EXISTS вместо NOT IN

Оценки и реальность: статистика и ANALYZE

N+1 из ORM и pg_stat_statements

Типовые исправления и порядок работы

Упражнения

Источники

Оптимизация и развитие существующих продуктов

Индексы: как ускорить запрос и не замедлить запись

Транзакции и изоляция: ACID без мистики

ORM и миграции на примере Prisma

Кэширование и инвалидация

Рецепт: keyset-пагинация в SQL

Есть задача?
Обсудим и оценим — бесплатно.

01Как найти медленные запросы

02Как читать план EXPLAIN

03Запросы 2 и 3: условие без функции и NOT EXISTS вместо NOT IN

04Оценки и реальность: статистика и ANALYZE

05N+1 из ORM и pg_stat_statements

06Типовые исправления и порядок работы

07Упражнения

Источники

Оптимизация и развитие существующих продуктов

Индексы: как ускорить запрос и не замедлить запись

Транзакции и изоляция: ACID без мистики

ORM и миграции на примере Prisma

Кэширование и инвалидация

Рецепт: keyset-пагинация в SQL

Есть задача?Обсудим и оценим — бесплатно.

Как найти медленные запросы

Как читать план EXPLAIN

Запросы 2 и 3: условие без функции и NOT EXISTS вместо NOT IN

Оценки и реальность: статистика и ANALYZE

N+1 из ORM и pg_stat_statements

Типовые исправления и порядок работы

Упражнения

Есть задача?
Обсудим и оценим — бесплатно.