Введение: почему запросы вдруг тормозят?

Представьте ситуацию: вчера приложение работало нормально, сегодня получили звонок – «Сайт грузит 10 секунд!». Заглядываете в логи, видите, что SQL-запрос выполняется 8 секунд вместо обычных 0.2 секунды. Но база данных, таблицы – всё то же самое. Что изменилось?

Дело в том, что PostgreSQL Query Planner – оптимизатор запросов – принимает решения на основе статистики. Когда статистика устаревает или таблица растёт в 10 раз, планировщик может выбрать неэффективный план выполнения. Например, вместо быстрого Index Scan он выберет медленный Seq Scan всей таблицы. Это не баг – это следствие неправильной информации о данных.

Статья полезна:

• Новичкам в SQL: разберётесь, как работают запросы

• Разработчикам на Symfony/Doctrine, Go с pgx: увидите интеграцию с вашим кодом

• Опытным DBA: найдёте оптимизации для таблиц в миллионы строк

Не требуется: специального опыта. Нужны базовые знания SQL и доступ к PostgreSQL.

Подготовка: создание тестовой БД для примеров

Все примеры в этой статье могут быть воспроизведены на вашей машине. Вот полный скрипт для создания тестовых таблиц и данных.

CREATE DATABASE test_planner;

-- Таблица клиентов
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    status VARCHAR(20),  -- 'active', 'inactive', 'suspended'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица заказов
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    status VARCHAR(20) NOT NULL,  -- 'pending', 'completed', 'cancelled'
    amount NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Тысяча клиентов
INSERT INTO customers (name, email, status, created_at)
SELECT 
    'Customer ' || i,
    'customer' || i || '@example.com',
    CASE 
        WHEN i % 3 = 0 THEN 'active'
        WHEN i % 3 = 1 THEN 'inactive'
        ELSE 'suspended'
    END,
    NOW() - INTERVAL '2 years' + (RANDOM() * INTERVAL '2 years')
FROM generate_series(1, 1000) AS t(i);

-- 1,100,000 заказов
INSERT INTO orders (customer_id, status, amount, created_at, updated_at)
SELECT 
    (RANDOM() * 999 + 1)::BIGINT AS customer_id,
    CASE 
        WHEN RANDOM() < 0.5 THEN 'completed'
        WHEN RANDOM() < 0.8 THEN 'pending'
        ELSE 'cancelled'
    END AS status,
    (RANDOM() * 10000 + 100)::NUMERIC(12, 2) AS amount,
    NOW() - INTERVAL '2 years' + (RANDOM() * INTERVAL '2 years') AS created_at,
    NOW() - INTERVAL '2 years' + (RANDOM() * INTERVAL '2 years') AS updated_at
FROM generate_series(1, 1100000) AS t(i);

-- Обновим статистику
VACUUM ANALYZE customers;
VACUUM ANALYZE orders;

-- Проверка данных
SELECT 
    (SELECT COUNT(*) FROM customers) as customers_total,
    (SELECT COUNT(*) FROM orders) as orders_total,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') as orders_completed,
    (SELECT COUNT(*) FROM orders WHERE status = 'pending') as orders_pending,
    (SELECT COUNT(*) FROM orders WHERE created_at > '2025-01-01') as orders_after_2025;

PgAdmin выполняет операции внутри блока BEGIN…COMMIT, поэтому выделяем нужную команду и выполняем только ее:

Часть 1: как PostgreSQL выполняет запросы

1.1 Три стадии обработки запроса

Когда вы выполняете запрос:

SELECT * FROM orders 
JOIN customers ON orders.customer_id = customers.id 
WHERE orders.created_at > '2025-01-01' 
ORDER BY orders.amount DESC 
LIMIT 10;

PostgreSQL проходит три стадии:

1. Парсинг – проверка синтаксиса SQL (~1 мс). При ошибке останавливается.

2. Планирование (Query Planner) – генерирует несколько возможных планов и выбирает самый быстрый по оценке (~10–100 мс для сложных запросов). Это где возникают проблемы.

3. Выполнение – запрос крутится по диску или кэшу (~10 мс – несколько секунд).

Ошибки обычно происходят на втором этапе. Если Planner неправильно оценит стоимость, выберет медленный план – вы получите 10-секундный запрос вместо 0.1 секунды.

1.2 Cost (Стоимость) – язык планировщика

Planifier оценивает cost (стоимость) каждой операции. Это не реальные миллисекунды, а условные единицы.

По умолчанию за единицу принят обход одной страницы диска. Например:

• Seq Scan (последовательное чтение таблицы) стоит ~1.0 за каждую страницу.

• Index Scan дороже на первый взгляд, но часто дешевле в целом, потому что читает меньше данных.

Planifier всегда выбирает план с минимальной total cost.

Ключевая идея: планировщик работает с оценками. Если оценки неточны – выбор плана неправильный.

1.3 Статистика – фундамент решений

Planifier не волшебник. Он знает о данных только то, что вы ему сказали командой ANALYZE. Эта команда собирает статистику:

• Сколько строк в таблице? (pg_class.reltuples)

• Как часто встречается каждое значение в колонке?

• Есть ли индексы?

• Какова корреляция между физическим и логическим порядком данных?

Если статистика старая или её нет вообще, Planifier гадает. И гадает обычно неправильно.

Вот почему первый совет по оптимизации:

VACUUM ANALYZE table_name;

По умолчанию включён autovacuum, который автоматически обновляет статистику при значительных изменениях данных. Но проверить не помешает:

SHOW autovacuum;  -- должно быть 'on'

Часть 2: как читать EXPLAIN ANALYZE

2.1 Ваша первая EXPLAIN

Чтобы посмотреть план выполнения, добавьте EXPLAIN ANALYZE перед запросом:

INSERT INTO orders (id, customer_id, status, amount, created_at, updated_at)
VALUES (1, 1, 'completed', 100.00, NOW(), NOW());
EXPLAIN ANALYZE 
SELECT * FROM orders WHERE id = 1;

Вывод может быть таким:

Что это означает:

• Index Scan – используется индекс orders_pkey

• cost = 0.43..8.45 – оценённая стоимость: 0.43 до первой строки, 8.45 всего

• rows = 1 – планировщик ожидает 1 строку

• Actual time = 0.017..0.018 – реально: 0.017-0.018 мс

• rows = 1 – реально вернулось 1 строка

• loops = 1 – узел выполнился 1 раз

Хорошо: когда rows (оценка) ≈ rows (реальность).

Плохо: когда расходятся сильно. Например, rows = 10, но actual = 1,000,000 – это красный флаг. Статистика неправильная, и это может привести к неэффективному плану в будущем.

2.2 Сложный запрос: JOIN + WHERE + ORDER BY

Вот более реальный пример:

EXPLAIN ANALYZE
SELECT o.id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2025-01-01'
ORDER BY o.amount DESC
LIMIT 10;

Упрощённый вывод (читать снизу вверх):

Читаем снизу вверх (что выполняется первым):

• Seq Scan on customers – читаем всех клиентов

• Hash – строим хэш-таблицу из клиентов

• Seq Scan on orders – читаем все заказы, фильтруем по дате

• Hash Join – объединяем заказы и клиентов через хэш-таблицу

• Sort – сортируем по amount DESC

• Limit – берём 10 первых строк

Правило: смотрите на cost (высокие значения – тяжёлые операции), на rows и actual rows (расхождения = проблема).

2.3 Три типа сканирования таблиц

Seq Scan (Sequential Scan) без индекса

EXPLAIN ANALYZE 
SELECT * FROM customers WHERE email = 'customer500@example.com';

Что: PostgreSQL читает таблицу построчно, проверяя условие.

Стоимость: O(n), где n — размер таблицы.

Когда используется:

- Нет подходящего индекса

- Выборка большая (значит индекс не поможет)

Когда плохо: на таблицах в миллионы строк, если условие фильтра могло бы быть выполнено индексом.

Index Scan с индексом

CREATE INDEX idx_customers_email ON customers(email);
ANALYZE customers;
EXPLAIN ANALYZE 
SELECT * FROM customers WHERE email = 'customer500@example.com';

Что: PostgreSQL использует B-tree индекс для быстрого поиска строк. Как оглавление в книге.

Стоимость: O (log n) для поиска + O (k) для чтения результатов, где k – количество строк.

Когда используется:

- WHERE column = value (точное совпадение)

- WHERE column > value (диапазон)

- Индекс существует

Ускорение: вместо чтения 1 млн строк делаем несколько операций по индексу   до 10,000 × быстрее.

Bitmap Index Scan

CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_orders_status ON orders(status);
VACUUM ANALYZE orders;
EXPLAIN ANALYZE SELECT COUNT(*) FROM orders
WHERE created_at > '2025-01-01' AND status = 'completed';

Что: PostgreSQL использует индекс для поиска приблизительного набора строк (bitmap), потом фильтрует этот набор дополнительно.

Когда используется: сложные условия (несколько фильтров по разным индексам).

Стоимость: между Seq Scan и Index Scan.

2.4 Три типа JOIN

Nested Loop Join (маленькая внешняя таблица)

-- Nested Loop: 10 клиентов → много заказов у каждого
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.amount, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id <= 10  -- МАЛЕНЬКАЯ внешняя таблица
ORDER BY o.amount DESC
LIMIT 20;

Логика:

для каждого customer (N строк):

 для каждого order с этим customer_id:

    выдай результат

Стоимость: O (N × M), где N – количество строк во внешней таблице, M – среднее количество строк во внутренней таблице на каждого клиента.

Когда хорош: внешняя таблица маленькая или внутренняя очень хорошо индексирована.

Когда плох: внешняя таблица большая (млн строк) и каждый поиск дорогой.

Hash Join (классический случай)

-- Hash Join: большая таблица orders + меньшая customers
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'  -- ~550k строк
LIMIT 1000;

Логика:

1. Прочитай меньшую таблицу в RAM как хэш-таблицу

2. Для каждой строки большой таблицы ищи совпадение в хэш-таблице

3. Выдай результат для совпадающих пар

Стоимость: O (N+M), если данные влезают в work_mem (параметр памяти).

Когда хорош: почти всегда. Самый быстрый JOIN при нормальных условиях.

Когда плохо: таблица не влезает в work_mem, происходит spill на диск (очень медленно).

Merge Join (с сортировкой)

-- Merge Join: обе таблицы сортируем по customer_id
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.amount > 5000  -- ~10% заказов
ORDER BY o.customer_id, c.id;  -- СОРТИРОВКА по join-ключу!

Ожидаемый результат:

Merge Join  (cost=87680.69..96071.18 rows=559069 width=36) (actual time=293.179..386.570 rows=560072 loops=1)

Логика:

1. Отсортируй обе таблицы по join-ключу

2. Merge: одновременно читай обе, как в сортировке слиянием

Стоимость: O ((N log N) + (M log M) + (N+M)).

Когда хорош: обе таблицы уже отсортированы (есть индекс).

Когда плохо: обычно медленнее Hash Join.

Правило: Hash Join обычно лучше. Merge Join редко нужен.

Важно: почему у вас мог получиться «не тот» JOIN

Если вы протестировали код по примерам выше, то могли заметить, что:

- запрос, для которого «по идее» ждёте Nested Loop, может дать Hash Join;

- а запрос, в котором ожидаете Hash Join, иногда даёт Nested Loop или даже Merge Join.

Это нормально: в реальной базе PostgreSQL сам выбирает тип JOIN на основе статистики и настроек cost. Один и тот же запрос на разных данных, объёмах таблиц, значениях work_mem и параметрах enable_* может порождать разные планы.​

Если вы хотите специально посмотреть, как выглядит конкретный тип JOIN, можно временно отключить альтернативы:

-- Показать именно Hash Join
SET enable_nestloop = off;
SET enable_mergejoin = off;

-- Показать именно Nested Loop
SET enable_hashjoin = off;
SET enable_mergejoin = off;

-- Показать именно Merge Join
SET enable_hashjoin = off;
SET enable_nestloop = off;

В боевом окружении так делать не нужно – это только учебный приём, чтобы увидеть, как выглядят разные типы JOIN и научиться их читать.

Часть 3: четыре типичные проблемы и их решение 

Ниже – типичные проблемы и причины, которые вы можете увидеть у себя. Конкретные цифры (стоимость, время, количество строк) – иллюстрации, а не гарантированный результат на демо-базе.

Проблема 1: неправильная статистика (rows ≠ actual rows)

Признак:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

Seq Scan on orders 

  (cost=0.00..5000.00 rows=100 width=100)

  Filter: (status = 'pending')

  Rows actually scanned: 1000000,

  actual rows returned: 900000

Проблема: планировщик оценил 100 строк, на самом деле 900,000! Ошибка в 9,000 раз.

Причина: статистика старая. Может быть:

- Последний ANALYZE был месяц назад

- В таблице много UPDATE/DELETE, мертвые строки искажают данные

- Autovacuum отключен.

Решение:

-- Обновить статистику
VACUUM ANALYZE orders;

Выполните EXPLAIN снова. Если rows ≈ actual rows – проблема решена.

совет: проверьте настройки autovacuum:

SELECT datname, 
       current_setting('autovacuum') as autovacuum_on,
       current_setting('autovacuum_naptime') as naptime
FROM pg_database;

Если autovacuum выключен – включите его.

Проблема 2: Seq Scan вместо Index Scan (нет индекса)

Признак:

EXPLAIN SELECT * FROM customers WHERE email = 'test@example.com';

Seq Scan on customers 

  (cost=0.00..50000.00 rows=1 width=100)

  Filter: (email = 'test@example.com')

  Rows actually scanned: миллионы строк

Проблема: ищем конкретный email, а PostgreSQL читает млн строк подряд.

Причина: нет индекса на email.

Решение:

-- Создать индекс (CONCURRENTLY = без блокировки таблицы)
CREATE INDEX CONCURRENTLY idx_customers_email 
ON customers(email);

-- Обновить статистику
ANALYZE customers;

-- Повторить EXPLAIN
EXPLAIN SELECT * FROM customers WHERE email = 'test@example.com';

Ожидаемый результат:

Index Scan using idx_customers_email on customers 

  (cost=0.29..1.00 rows=1 width=100)

  Index Cond: (email = 'test@example.com')

Ускорение: 5,000,000 строк vs несколько сотен – 10,000× быстрее.

Проблема 3: Плохой JOIN (Nested Loop вместо Hash)

Признак:

EXPLAIN ANALYZE
SELECT o.id, o.amount, c.name 
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Nested Loop 

  (cost=0.29..5000000.00 rows=1000000 width=200)

  ->  Index Scan on customers c 

        (cost=0.29..1000.00 rows=100000)

  ->  Index Scan on orders o 

        Index Cond: (customer_id = c.id)

  Execution Time: 8234.567 ms

Проблема: выполняется 8 секунд! Для каждого из 100к customer ищем заказы (100к поисков).

Причина: планировщик выбрал Nested Loop. Может быть:

- work_mem слишком мал для Hash Join

- enable_hashjoin отключен

- неправильная статистика.

Решение 1: увеличить work_mem

-- На сессию
SET work_mem = '256MB';

-- Повторить запрос
EXPLAIN ANALYZE SELECT o.id, o.amount, c.name 
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Если план изменится на Hash Join и время упадёт до 0.5 сек – решено!

Production:  в postgresql.conf:

work_mem = 256MB

Рестарт не требуется:

SELECT pg_reload_conf();

Решение 2: проверить enable параметры

-- Запретить Nested Loop, заставить Hash
SET enable_nestloop = OFF;

EXPLAIN ANALYZE SELECT ...;

Если результат быстро – Nested Loop был ошибкой.

Решение 3: обновить статистику

VACUUM ANALYZE orders;
VACUUM ANALYZE customers;

Ускорение: 8 сек → 0.5 сек.

Проблема 4: Параллельный запрос не работает

Признак:

EXPLAIN ANALYZE
SELECT COUNT(*) FROM orders WHERE status = 'active';

Finalize Aggregate 

  ->  Gather 

        Workers Planned: 4

        Workers Launched: 0  -- ПРОБЛЕМА!

        ->  Seq Scan on huge_table

Проблема: planifier хотел использовать 4 worker-процесса, но не нашёл их.

Причина:

- max_parallel_workers_per_gather = 0

- max_parallel_workers ограничено

- Система загружена

Решение:

-- Проверить текущие настройки
SHOW max_parallel_workers_per_gather;
SHOW max_parallel_workers;

-- На сессию
SET max_parallel_workers_per_gather = 4;
SET work_mem = '512MB';

-- Повторить запрос

Production в postgresql.conf:

max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

Ускорение: 10 сек (1 ядро) → 3 сек (4 ядра).

Часть 4: реальный кейс – таблица 10 млн строк

Сценарий:

Приложение на Symfony/Doctrine. Таблица orders ~10 млн строк:

-- Добавим данных в старую таблицу
INSERT INTO orders (customer_id, status, amount, created_at, updated_at)
SELECT
    (RANDOM() * 999 + 1)::BIGINT AS customer_id,
    CASE
        WHEN RANDOM() < 0.5 THEN 'completed'
        WHEN RANDOM() < 0.8 THEN 'pending'
        ELSE 'cancelled'
    END AS status,
    (RANDOM() * 10000 + 100)::NUMERIC(12, 2) AS amount,
    -- часть данных "старые", часть — "новые"
    NOW() - INTERVAL '3 years' + (RANDOM() * INTERVAL '3 years') AS created_at,
    NOW() - INTERVAL '3 years' + (RANDOM() * INTERVAL '3 years') AS updated_at
FROM generate_series(1, 9000000) AS t(i);

VACUUM ANALYZE orders;

Запрос, который неделю назад был быстрым, теперь стал тяжелее:

SELECT o.id, o.amount, o.status, c.name

FROM orders o

JOIN customers c ON o.customer_id = c.id

WHERE o.status = 'completed'

  AND o.created_at > '2025-01-01'

ORDER BY o.created_at DESC

LIMIT 100;

Диагностика

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.id, o.amount, o.status, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
  AND o.created_at > '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

Вывод (упрощённо): 

Limit

  -> Gather Merge

       -> Sort (rows≈707k, Sort Key: o.created_at DESC)

            -> Hash Join

                 -> Parallel Seq Scan on orders

                       Filter: created_at > '2025-01-01' AND status = 'completed'

                       Rows Removed by Filter: ~2.8M

                 -> Seq Scan on customers

Execution Time: ~708 ms

Анализ проблемы

Что здесь не так:

1. Paraller Seq Scan on orders – база читает большой объем данных, фильтруя миллионы строк по status и created_at, вместо того чтобы использовать индекс по этим полям.

2. Sort поверх ~700к строк – дорого, особенно когда все это происходит на фоне уже тяжелого сканирования.

3. Execution Time: 708 мс

Гипотеза: нет составного индекса на (status, created_at DESC). Planifier не может быстро выбрать нужные заказы и вынужден сканировать всю таблицу + затем сортировать.

Решение: составной индекс

-- Создать составной индекс для фильтра + сортировки
CREATE INDEX CONCURRENTLY idx_orders_status_created 
  ON orders(status, created_at DESC);

-- Обновить статистику
VACUUM ANALYZE orders;

Новый план

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.id, o.amount, o.status, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
  AND o.created_at > '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

Результат

Limit

  -> Nested Loop

       -> Index Scan using idx_orders_status_created on orders

            Index Cond: (status = 'completed' AND created_at > '2025-01-01')

       -> Memoize

            -> Index Scan using customers_pkey on customers

Execution Time: ~1.1 ms

Результат:

1. Вместо параллельного Seq Scan по всей таблице orders появился Index Scan по составному индексу – база сразу находит нужные заказы в нужном порядке.

2. Отдельная тяжёлая сортировка по created_at DESC исчезает: порядок уже обеспечен самим индексом.

3. План стал проще, а главное – время выполнения упало примерно с 708 ms до 1.1 ms.

Код: Symfony/Doctrine

<?php
// OrderRepository.php

namespace App\Repository;

use Doctrine\ORM\EntityRepository;

class OrderRepository extends EntityRepository
{
    public function findCompletedOrders($sinceDate, $limit = 100)
    {
        $qb = $this->createQueryBuilder('o')
            ->innerJoin('o.customer', 'c')
            ->where('o.status = :status')
            ->andWhere('o.createdAt > :date')
            ->setParameter('status', 'completed')
            ->setParameter('date', $sinceDate)
            ->orderBy('o.createdAt', 'DESC')
            ->setMaxResults($limit);
        
        $query = $qb->getQuery();
        
        // Для дебага в dev окружении можно вывести EXPLAIN
        if (getenv('APP_ENV') === 'dev') {
            $sql = $query->getSQL();
            $params = $query->getParameters();
            // Выполнить EXPLAIN для диагностики
            // $explainSql = 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' . $sql;
            // dump($explainSql);
        }
        
        return $query->getResult();
    }
}

Код: Go с pgx

package main

import (
	"context"
	"encoding/json"
	"fmt"
	"log"
	"time"

	"github.com/jackc/pgx/v4"
)

func main() {
	conn, err := pgx.Connect(context.Background(), 
		"postgres://user:pass@localhost/db")
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(context.Background())

	// Таймаут: если запрос > 5 сек, отменяем
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()

	// Выполняем запрос с EXPLAIN (JSON формат)
	query := `
		EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
		SELECT o.id, o.amount, o.status, c.name
		FROM orders o
		JOIN customers c ON o.customer_id = c.id
		WHERE o.status = 'completed' 
		  AND o.created_at > $1
		ORDER BY o.created_at DESC
		LIMIT 100
	`

	rows, err := conn.Query(ctx, query, "2025-01-01")
	if err != nil {
		log.Fatal(err)
	}

	for rows.Next() {
		var plan []byte
		err := rows.Scan(&plan)
		if err != nil {
			log.Fatal(err)
		}

		// Распечатать JSON красиво
		var result []interface{}
		json.Unmarshal(plan, &result)
		prettyJSON, _ := json.MarshalIndent(result, "", "  ")
		fmt.Println(string(prettyJSON))
	}
}

Мониторинг: pg_stat_statements

Чтобы не ловить проблемы вручную:

-- Подключить (один раз)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Найти медленные запросы
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    max_time
FROM pg_stat_statements
WHERE mean_time > 100  -- > 100 мс
ORDER BY mean_time DESC
LIMIT 10;

-- Очистить после анализа
SELECT pg_stat_statements_reset();

Раз в неделю мониторьте ТОП медленных запросов и разбирайтесь с ними.

Чтобы использовать pg_stat_statements, его нужно заранее загрузить через shared_preload_libraries и перезапустить PostgreSQL:

shared_preload_libraries = 'pg_stat_statements'

Затем в нужной БД выполнить:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

В PostgreSQL 16+ вместо total_time/mean_time используются поля total_exec_time и mean_exec_time, поэтому запрос может выглядеть так:

SELECT query, calls, total_exec_time, mean_exec_time, max_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC
LIMIT 10;

Часть 5: Чек-лист для production

Перед развёртыванием

- Autovacuum включен?

  SHOW autovacuum;  -- должно быть 'on'

- Нужные индексы есть?

EXPLAIN (ANALYZE) <ваш_запрос>;

Ищите Seq Scan на больших таблицах (>100k). Если WHERE/JOIN на колонке без индекса — добавьте.

- Статистика актуальна?

SELECT schemaname, tablename, last_autovacuum
  FROM pg_stat_user_tables
  WHERE last_autovacuum IS NULL 
     OR last_autovacuum < NOW() - INTERVAL '7 days';

- work_mem достаточно?

  SHOW work_mem;  -- рекомендуется 256MB–512MB

- shared_buffers настроен?

SHOW shared_buffers;  -- рекомендуется 25% ОЗУ

При проблемах

1. Берёте медленный запрос, выполняете EXPLAIN ANALYZE

2. Смотрите: rows vs actual rows совпадают?

   - Нет → VACUUM ANALYZE и повтор

   - Да → переходим к п.3

3. Есть ли Seq Scan где ему не быть?

   - Да → CREATE INDEX CONCURRENTLY и ANALYZE

   - Нет → переходим к п.4

4. Есть ли плохой JOIN (Nested Loop вместо Hash)?

   - Да → SET work_mem = '256MB' или CREATE INDEX на FK

   - Нет → переходим к п.5

5. Проверить Buffers: много read?

   - Да → увеличить shared_buffers

   - Нет → проблема может быть в приложении

Заключение: 10-ти шаговый алгоритм оптимизации

1. Включите EXPLAIN ANALYZE перед подозрительным запросом

2. Проверьте: rows = actual rows? Нет → VACUUM ANALYZE

3. Есть ли Seq Scan на больших таблицах в WHERE? Да → CREATE INDEX

4. Есть ли Nested Loop в JOIN? Да → CREATE INDEX на FK или SET work_mem

5. Проверьте Buffers: много read? Да → увеличьте shared_buffers или effective_cache_size

6. Включите pg_stat_statements и мониторьте раз в неделю

7. На production CREATE INDEX CONCURRENTLY (без блокировки)

8. После каждого изменения повтори EXPLAIN — сравните cost, rows, execution time

9. Документируйте: проблема → решение → ускорение

10. Автоматизируйте: пишите скрипты для поиска новых медленных запросов

Полезные команды для дебага

-- План + анализ
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>;

-- JSON формат для парсинга
EXPLAIN (ANALYZE, FORMAT JSON) <query>;

-- Только план (без выполнения)
EXPLAIN <query>;

-- Размер индексов
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Медленные запросы
SELECT query, mean_time FROM pg_stat_statements 
WHERE mean_time > 50 
ORDER BY mean_time DESC LIMIT 10;

Финальное слово

Query Planner PostgreSQL – не враг, а помощник. Он работает с информацией, которую вы ему даёте. Дайте ему точные данные (VACUUM ANALYZE), обеспечьте нужные индексы – и он сам выберет быстрый план.

Запомните:

- Медленный запрос – это почти всегда отсутствие индекса или старая статистика

- EXPLAIN ANALYZE – ваш лучший друг

- Расхождение rows vs actual rows – первый знак проблемы

- Еженедельный мониторинг pg_stat_statements спасает часы отладки

При первых подозрениях на медлительность: EXPLAIN ANALYZE. Это займёт 5 минут и спасёт часы отладки.

Удачи с оптимизацией!