PostgreSQL: полный гайд

Запрос работает медленно, и первый вопрос всегда один: что именно делает база? Ответ даёт EXPLAIN. Он показывает план: в каком порядке PostgreSQL читает таблицы, какие индексы берёт и сколько строк ждёт на каждом шаге.

В примерах ниже две таблицы: 10 000 клиентов и 200 000 заказов. Скрипт для создания данных лежит в конце главы, примеры можно запускать кнопкой «Выполнить». Время у вас будет другим. Фактические числа строк совпадут с текстом, потому что данные генерируются с фиксированным зерном, а оценки планировщика (rows= в скобках cost) могут немного отличаться: статистика собирается по случайной выборке. Смотрите на соотношения, а не на абсолютные значения.

EXPLAIN и EXPLAIN ANALYZE — две разные вещи

EXPLAIN только строит план и ничего не выполняет. Цифры в нём оценочные.

Seq Scan on orders  (cost=0.00..3999.00 rows=50647 width=28)
  Filter: (status = 'paid'::text)

EXPLAIN ANALYZE запрос выполняет по-настоящему и добавляет к плану фактические числа: сколько строк вернулось и сколько заняло времени.

Осторожно: EXPLAIN ANALYZE выполняет INSERT, UPDATE и DELETE на самом деле. Чтобы посмотреть план такого запроса и ничего не изменить, оберните его в транзакцию: BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;.

Из чего состоит строка плана

Seq Scan on orders  (cost=0.00..3999.00 rows=50647 width=28)

• Seq Scan on orders — узел плана. Здесь база читает таблицу целиком, страницу за страницей.
• cost=0.00..3999.00 — оценка стоимости в условных единицах. Первое число — сколько стоит получить первую строку, второе — всю выдачу. Это не миллисекунды. Единицы нужны, чтобы сравнивать планы между собой.
• rows=50647 — сколько строк, по мнению планировщика, вернёт узел.
• width=28 — средний размер строки в байтах.

С ANALYZE появляется вторая скобка:

(actual time=1.066..16.477 rows=12 loops=1)

• actual time — реальное время в миллисекундах: до первой строки и до последней.
• rows — сколько строк узел вернул на самом деле.
• loops — сколько раз узел запускался. Если loops больше единицы, actual time и rows показаны в среднем на один запуск. Для общего времени умножайте.

План читается как дерево. Отступ означает вложенность. Исполнение идёт изнутри наружу: сначала самые вложенные узлы отдают строки родителям.

Пример 1. Последовательное чтение

Seq Scan on orders  (cost=0.00..3999.00 rows=20 width=28) (actual time=1.066..16.477 rows=12 loops=1)
  Filter: (customer_id = 777)
  Rows Removed by Filter: 199988
  Buffers: shared hit=1499
Execution Time: 16.496 ms

Важны две строки. Rows Removed by Filter: 199988 говорит, что база прочитала почти 200 тысяч строк, чтобы вернуть двенадцать. А Buffers: shared hit=1499 — сколько страниц по 8 КБ она для этого подняла из кэша. Когда на выходе мало строк, а отброшено много, это первый сигнал: не хватает индекса.

Опция BUFFERS показывает чтение страниц: hit — из кэша PostgreSQL, read — пришлось читать с диска или из кэша ОС. Число страниц часто полезнее времени: оно не зависит от загрузки машины.

Пример 2. Тот же запрос с индексом

Bitmap Heap Scan on orders  (cost=4.45..77.77 rows=20 width=28) (actual time=0.075..0.100 rows=12 loops=1)
  Recheck Cond: (customer_id = 777)
  Heap Blocks: exact=12
  ->  Bitmap Index Scan on orders_customer_idx  (cost=0.00..4.45 rows=20 width=0) (actual time=0.065..0.066 rows=12 loops=1)
        Index Cond: (customer_id = 777)
Execution Time: 0.184 ms

Было 16.5 мс, стало 0.18 мс, примерно в 90 раз быстрее. Страниц теперь не 1499, а 12.

Почему Bitmap, а не обычный Index Scan? Строки клиента разбросаны по таблице. Сначала Bitmap Index Scan собирает по индексу карту нужных страниц. Потом Bitmap Heap Scan читает эти страницы по порядку, по одному разу каждую. Нижний узел вложен глубже, значит, он выполняется первым и передаёт результат наверх.

Пример 3. Соединение и группировка

HashAggregate  (actual time=57.241..57.252 rows=5 loops=1)
  Group Key: c.city
  ->  Hash Join  (actual time=5.919..49.502 rows=16412 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Seq Scan on orders o  (actual time=0.017..36.207 rows=16412 loops=1)
              Filter: (created_at >= ... AND created_at < ...)
              Rows Removed by Filter: 183588
        ->  Hash  (actual time=5.767..5.769 rows=10000 loops=1)
              ->  Seq Scan on customers c  (actual time=0.015..2.585 rows=10000 loops=1)
Execution Time: 57.660 ms

Читаем снизу вверх.

1. База читает всех клиентов (Seq Scan on customers) и строит из них хэш-таблицу (Hash).
2. Читает заказы за июнь (Seq Scan on orders). Отбросила 183 588 строк, оставила 16 412.
3. Для каждого заказа ищет клиента в хэш-таблице (Hash Join).
4. Группирует по городу (HashAggregate), получает 5 строк.

Больше всего времени, 36 мс из 57, уходит на чтение заказов. Индекса по дате здесь нет. Но не торопитесь его создавать: выбран июнь, это около 8% таблицы. Когда запрос затрагивает такую долю строк, последовательное чтение иногда выгоднее индекса. Планировщик сравнивает стоимости, а не следует правилу «индекс всегда быстрее».

Пример 4. Функция на колонке отключает индекс

Создадим индекс по дате и посмотрим два способа написать один и тот же фильтр.

Seq Scan on orders  (actual time=0.234..48.545 rows=540 loops=1)
  Filter: (date_trunc('day', created_at) = '2025-03-15 00:00:00+00')
  Rows Removed by Filter: 199460
Execution Time: 48.663 ms

Индекс построен по created_at, а в условии стоит date_trunc(created_at). Для базы это другое выражение, поэтому индекс не подходит, и она читает всю таблицу. Перепишем условие диапазоном:

Bitmap Heap Scan on orders  (actual time=0.237..0.822 rows=540 loops=1)
  Recheck Cond: (created_at >= ... AND created_at < ...)
  ->  Bitmap Index Scan on orders_created_idx  (actual time=0.188..0.188 rows=540 loops=1)
Execution Time: 0.959 ms

Результат тот же, 540 строк. Время 48.7 мс против 0.96 мс, примерно в 50 раз быстрее. Правило: колонка в условии должна стоять сама, без функций и арифметики. Если функция нужна, создайте индекс по самому выражению: CREATE INDEX ON orders (date_trunc('day', created_at)).

Пример 5. Когда оценка и факт расходятся

Сравнивайте rows в оценке и rows в actual. Это главный способ найти причину плохого плана. Большой разрыв, в десятки и сотни раз, означает, что планировщик выбирал путь по неверным данным.

Seq Scan on skew  (cost=0.00..2318.40 rows=569 width=36) (actual time=18.627..19.961 rows=10000 loops=1)

Ожидал 569 строк, вернулось 10 000. Таблица только что создана, статистики по ней нет, и планировщик угадывал. Соберём статистику:

Seq Scan on skew  (cost=0.00..3396.00 rows=9867 width=6) (actual time=18.162..19.443 rows=10000 loops=1)

Теперь оценка близка к факту: 9867 против 10 000. При каждом ANALYZE статистика собирается по случайной выборке строк, поэтому у вас число будет чуть другим, но около десяти тысяч. Здесь план остался тем же, а на сложных запросах от точности оценки зависит выбор алгоритма соединения и порядка таблиц. Если оценки сильно мимо, первым делом запустите ANALYZE на таблице.

Чек-лист чтения плана

1. Найдите самый дорогой узел по actual time. Не по cost, по реальному времени, с учётом loops.
2. Сравните rows в оценке и в факте. Разрыв в разы — повод для ANALYZE или для вопроса к запросу.
3. Посмотрите Rows Removed by Filter. Огромное число при малом результате означает отсутствие подходящего индекса.
4. Проверьте, нет ли функции или приведения типов на колонке в WHERE.
5. Не считайте Seq Scan ошибкой. На маленьких таблицах и при выборке больших долей он оптимален.

Частые ошибки

• Смотреть только EXPLAIN без ANALYZE. Оценки могут врать, а факт покажет реальную картину.
• Мерить один запуск. Первый запуск часто идёт по холодному кэшу. Повторите два-три раза.
• Запускать EXPLAIN ANALYZE на DELETE в рабочей базе без ROLLBACK. Данные удалятся.
• Сравнивать cost между разными запросами. Стоимость сравнима только между планами одного запроса.
• Добавлять индекс, не посмотрев план. Без плана непонятно, поможет ли он.

Данные для примеров

Этот блок выполняется перед примерами главы. SET max_parallel_workers_per_gather = 0 отключает параллельное чтение: так планы остаются простыми, без узлов Gather. На своей базе вы можете увидеть параллельные планы, это нормально.

SET max_parallel_workers_per_gather = 0;

CREATE TABLE customers (
  id serial PRIMARY KEY,
  name text NOT NULL,
  city text NOT NULL
);
CREATE TABLE orders (
  id serial PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers(id),
  status text NOT NULL,
  amount numeric(10,2) NOT NULL,
  created_at timestamptz NOT NULL
);
INSERT INTO customers (name, city)
SELECT 'Клиент ' || g, (ARRAY['Москва','Казань','Омск','Пермь','Тверь'])[1 + g % 5]
FROM generate_series(1, 10000) g;

SELECT setseed(0.42);
INSERT INTO orders (customer_id, status, amount, created_at)
SELECT 1 + (random()*9999)::int,
       (ARRAY['new','paid','shipped','done','cancelled'])[1 + (random()*4)::int],
       round((random()*5000)::numeric, 2),
       timestamptz '2025-01-01' + (random()*365) * interval '1 day'
FROM generate_series(1, 200000);

ANALYZE customers;
ANALYZE orders;

Закрепить на практике можно в курсе «Оптимизация и индексы»: там чтение планов разбирается на заданиях с проверкой.