PostgreSQL: оптимизация запросов и индексы

EXPLAIN ANALYZE: читаем план запроса

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

sql-- Полный анализ с буферами и форматом JSON
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 100;

-- Что искать в выводе:
-- "Seq Scan" на большой таблице = нужен индекс
-- "Rows Removed by Filter: 99847" = плохая селективность
-- rows= vs actual rows= — большое расхождение = устаревшая статистика

Типы индексов и когда их применять

PostgreSQL поддерживает несколько типов индексов. Выбор неправильного типа — частая ошибка.

B-tree (по умолчанию)

Подходит для операций =, <, >, BETWEEN, LIKE 'prefix%', ORDER BY. Это 95% всех индексов.

sql-- Составной индекс: порядок колонок важен!
-- Хорошо для: WHERE status = 'active' AND created_at > '2025-01-01'
-- Хорошо для: WHERE status = 'active' (только первая колонка)
-- Плохо для: WHERE created_at > '2025-01-01' (без status)
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at DESC);

-- Частичный индекс — только для нужных строк
-- Если 95% заказов completed, индексируем только активные
CREATE INDEX CONCURRENTLY idx_orders_active
ON orders (user_id, created_at)
WHERE status IN ('pending', 'processing');

GIN — для полнотекстового поиска и массивов

sql-- Полнотекстовый поиск
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector =
  to_tsvector('russian', coalesce(title,'') || ' ' || coalesce(body,''));

CREATE INDEX idx_articles_fts ON articles USING GIN(search_vector);

-- Поиск
SELECT title FROM articles
WHERE search_vector @@ plainto_tsquery('russian', 'оптимизация postgresql');

-- Для JSONB
CREATE INDEX idx_events_data ON events USING GIN(data jsonb_path_ops);
SELECT * FROM events WHERE data @> '{"type": "click", "page": "home"}';

BRIN — для последовательных данных

sql-- BRIN очень маленький (в 1000 раз меньше B-tree)
-- Отлично для временных рядов, логов, событий
-- Работает только если данные физически отсортированы на диске
CREATE INDEX idx_logs_created_brin ON logs USING BRIN(created_at)
WITH (pages_per_range = 128);

Статистика и автовакуум

Планировщик PostgreSQL принимает решения на основе статистики. Если статистика устарела — планы будут неоптимальными.

sql-- Проверить актуальность статистики
SELECT schemaname, relname,
  n_live_tup, n_dead_tup,
  last_vacuum, last_autovacuum,
  last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Принудительно обновить статистику
ANALYZE VERBOSE orders;

-- Увеличить точность статистики для колонки с плохой селективностью
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Поиск медленных запросов

sql-- Включить pg_stat_statements в postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

-- Запросы с наибольшим количеством seq scan
SELECT relname, seq_scan, idx_scan,
  round(seq_scan::numeric / (seq_scan + idx_scan + 1) * 100, 1) AS seq_pct
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_scan DESC;

Ключевые настройки производительности

ini# postgresql.conf — базовые настройки для сервера с 16 ГБ RAM

# Память
shared_buffers = 4GB              # 25% RAM
effective_cache_size = 12GB       # 75% RAM
work_mem = 64MB                   # для сортировок, увеличить осторожно
maintenance_work_mem = 1GB        # для VACUUM, CREATE INDEX

# WAL
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# Параллелизм
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Логирование медленных запросов
log_min_duration_statement = 1000  # ms