PostgreSQL для разработчика: индексы, EXPLAIN и оптимизация запросов без DBA

Введение

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

В этой статье разберем три ключевых инструмента для оптимизации запросов PostgreSQL: индексы, команду EXPLAIN ANALYZE и практические приемы ускорения SQL-запросов. Все примеры — на реальных сценариях, с которыми сталкивается бэкенд-разработчик.

Как работают индексы в PostgreSQL

Индекс в PostgreSQL — это отдельная структура данных, которая ускоряет поиск строк в таблице. Без индекса база выполняет Seq Scan — последовательно читает каждую строку таблицы. С индексом PostgreSQL переключается на Index Scan и находит нужные данные за логарифмическое время.

Рассмотрим таблицу заказов:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL,
    total NUMERIC(10, 2),
    created_at TIMESTAMP DEFAULT now()
);

-- Вставим тестовые данные: 500 000 заказов
INSERT INTO orders (user_id, status, total, created_at)
SELECT
    (random() * 10000)::int,
    (ARRAY['pending', 'paid', 'shipped', 'delivered'])[ceil(random() * 4)],
    (random() * 1000)::numeric(10,2),
    now() - (random() * interval '365 days')
FROM generate_series(1, 500000);

Запрос без индекса по полю user_id:

SELECT * FROM orders WHERE user_id = 42;

PostgreSQL выполнит Seq Scan по всей таблице — на 500 000 строк это займет десятки миллисекунд. Создадим индекс:

CREATE INDEX idx_orders_user_id ON orders(user_id);

Теперь тот же запрос использует Index Scan и выполняется менее чем за миллисекунду.

Какой тип индекса выбрать

PostgreSQL поддерживает несколько типов индексов. Вот основные:

• B-tree (по умолчанию) — подходит для операций сравнения (=, <, >, BETWEEN), сортировки и поиска по диапазону. Покрывает 90% задач.
• Hash — только для операции =. Занимает меньше места, но не поддерживает сортировку и диапазоны.
• GIN — для полнотекстового поиска, массивов и JSONB-полей.
• GiST — для геометрических и географических данных.

Если вы сомневаетесь, какой индекс создать — используйте B-tree. Это значение по умолчанию, и оно работает для большинства сценариев.

Как читать EXPLAIN ANALYZE в PostgreSQL

EXPLAIN ANALYZE — главный инструмент для анализа производительности запросов. Он показывает план выполнения запроса с реальными замерами времени.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Результат выглядит примерно так:

Index Scan using idx_orders_user_id on orders  (cost=0.42..8.44 rows=50 width=52)
  (actual time=0.028..0.045 rows=48 loops=1)
  Index Cond: (user_id = 42)
Planning Time: 0.085 ms
Execution Time: 0.062 ms

Разберем ключевые поля:

• cost=0.42..8.44 — оценка стоимости планировщиком. Первое число — стоимость получения первой строки, второе — всех строк.
• rows=50 — сколько строк планировщик ожидает получить.
• actual time=0.028..0.045 — реальное время в миллисекундах.
• rows=48 — сколько строк получено на самом деле.
• loops=1 — сколько раз выполнялся узел.

Если rows в оценке сильно отличается от actual rows, планировщик принимает неоптимальные решения. В таком случае обновите статистику:

ANALYZE orders;

Когда EXPLAIN показывает Seq Scan вместо Index Scan

Распространенная ситуация: вы создали индекс, но PostgreSQL все равно делает Seq Scan. Причины:

1. Запрос возвращает слишком много строк. Если PostgreSQL оценивает, что нужно вернуть более 10-15% таблицы, Seq Scan оказывается быстрее.
2. Неактуальная статистика. Выполните ANALYZE имя_таблицы.
3. Приведение типов. Если колонка INTEGER, а вы передаете строку — индекс не используется.
4. Функция на колонке. WHERE LOWER(email) = 'test@test.com' не задействует обычный индекс. Нужен функциональный индекс.

Составные и частичные индексы для сложных запросов

Для запросов с несколькими условиями одиночных индексов недостаточно. Используйте составные индексы:

-- Запрос: найти оплаченные заказы пользователя
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';

-- Составной индекс
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Порядок колонок в составном индексе важен. PostgreSQL использует индекс слева направо. Индекс (user_id, status) поможет запросу WHERE user_id = 42, но не поможет запросу WHERE status = 'paid'.

Если вы часто фильтруете по конкретному значению, создайте частичный индекс:

-- Индекс только для неоплаченных заказов
CREATE INDEX idx_orders_pending ON orders(user_id)
WHERE status = 'pending';

Такой индекс занимает меньше места и работает быстрее, потому что содержит только подмножество строк.

Практические приемы ускорения медленных SQL-запросов

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

Включите расширение pg_stat_statements для сбора статистики по всем запросам:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Топ-10 самых медленных запросов
SELECT
    query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Это первое, что нужно сделать, когда приложение начинает тормозить. Вы увидите, какие именно запросы съедают время.

Избегайте SELECT * в продакшн-коде

-- Плохо: читает все колонки, включая ненужные
SELECT * FROM orders WHERE user_id = 42;

-- Хорошо: только нужные поля
SELECT id, status, total FROM orders WHERE user_id = 42;

Если все нужные колонки есть в индексе, PostgreSQL выполнит Index Only Scan — вообще не обращаясь к таблице. Это самый быстрый вариант.

Пагинация: OFFSET — не всегда хорошее решение

-- Медленно на больших смещениях: PostgreSQL читает и отбрасывает строки
SELECT * FROM orders ORDER BY created_at DESC OFFSET 100000 LIMIT 20;

-- Быстрая альтернатива: keyset pagination
SELECT * FROM orders
WHERE created_at < '2026-01-15 10:30:00'
ORDER BY created_at DESC
LIMIT 20;

Keyset pagination работает за постоянное время независимо от номера страницы, потому что PostgreSQL использует индекс для позиционирования.

Частые ошибки при оптимизации запросов

1. Создание индексов на все подряд. Каждый индекс замедляет INSERT и UPDATE. Создавайте индексы только под конкретные запросы.
2. Игнорирование EXPLAIN ANALYZE. Не гадайте — измеряйте. Запускайте EXPLAIN ANALYZE перед и после оптимизации.
3. Оптимизация на маленьких данных. На таблице из 100 строк все запросы быстрые. Тестируйте на объемах, близких к продакшну.
4. Забытый ANALYZE. После массовой загрузки данных или миграции статистика устаревает. Планировщик начинает выбирать неоптимальные планы.
5. N+1 запросы из ORM. Один запрос с JOIN всегда лучше, чем цикл из одиночных SELECT. Проверяйте, что генерирует ваш ORM.

Заключение

Оптимизация запросов в PostgreSQL не требует глубокой экспертизы DBA. Достаточно освоить три вещи: создание правильных индексов, чтение вывода EXPLAIN ANALYZE и систематический подход к поиску узких мест. Используйте pg_stat_statements для выявления медленных запросов, EXPLAIN ANALYZE для их анализа и индексы для ускорения. Этих инструментов достаточно, чтобы решить 90% проблем производительности на типичном проекте.

Если хотите глубже разобраться в работе с PostgreSQL и научиться строить производительные бэкенды, обратите внимание на курсы NestJS и FastAPI на PurpleSchool — в них разбираются реальные паттерны работы с базами данных в продакшн-проектах.