🟢 Junior Level

Оптимизация запросов — это процесс ускорения медленных SQL-запросов.

Простой алгоритм:

1. Найти медленный запрос
2. Посмотреть его план через EXPLAIN
3. Понять, что тормозит
4. Исправить (чаще всего — добавить индекс)

Пример:

-- 1. Найти медленный запрос
-- (логи приложения, pg_stat_statements)

-- 2. Посмотреть план
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 3. Видим проблему
-- Seq Scan on users  ← читает всю таблицу!
--   Rows Removed by Filter: 999999

-- 4. Исправить
CREATE INDEX idx_users_email ON users(email);

-- Теперь:
-- Index Scan using idx_users_email  ← быстро!
-- Execution Time: 0.05 ms (было 500 ms)

Самые частые причины медленных запросов:

• ❌ Нет индекса на поле в WHERE
• ❌ Индекс есть, но не используется (функция в WHERE)
• ❌ Выборка слишком большая (нет LIMIT)
• ❌ JOIN без индексов на полях связи

---

🟡 Middle Level

Алгоритм оптимизации

Шаг 1: Найти проблемные запросы

-- pg_stat_statements — главный инструмент
CREATE EXTENSION pg_stat_statements;

-- Топ-10 самых медленных запросов
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Топ-10 запросов с наибольшим чтением с диска
SELECT query, shared_blks_read, mean_exec_time
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

Шаг 2: Проанализировать план

EXPLAIN (ANALYZE, BUFFERS) 
SELECT ...;

-- Ищите:
-- Seq Scan на больших таблицах → нужен индекс
-- Sort → Disk Spill → увеличьте work_mem
-- Nested Loop на больших таблицах → плохой план
-- rows ≠ actual rows → устаревшая статистика

Шаг 3: Исправить

Частые проблемы и решения

1. Нет индекса

-- ❌ Seq Scan на 1 млн строк
SELECT * FROM users WHERE email = 'test@example.com';

-- ✅ Создать индекс
CREATE INDEX idx_users_email ON users(email);

2. Функция в WHERE убивает индекс

-- ❌ Индекс не используется
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- ✅ Функциональный индекс
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

3. Неправильный тип данных

-- ❌ Implicit cast блокирует индекс
SELECT * FROM users WHERE phone = 89001234567;  -- phone = text

-- ✅ Правильный тип
SELECT * FROM users WHERE phone = '89001234567';

4. Не хватает work_mem

-- ❌ Сортировка на диске
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY created_at DESC;
-- Sort Method: external merge  Disk: 5000kB

-- ✅ Увеличить work_mem
SET work_mem = '256MB';
-- Теперь: Sort Method: quicksort  Memory: 50000kB

5. JOIN без индексов

-- ❌ Hash Join без индексов
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

-- ✅ Индекс на поле связи
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Теперь: Nested Loop + Index Scan → быстрее

Типичные ошибки

1. Слишком много индексов
   • Каждый индекс замедляет INSERT/UPDATE
   • Удаляйте неиспользуемые: idx_scan = 0
2. SELECT *
   • Выбирайте только нужные колонки
   • Меньше данных → быстрее передача
3. N+1 проблема

   -- ❌ 1000 запросов в цикле
   for user in users:
       SELECT * FROM orders WHERE user_id = user.id
      
   -- ✅ 1 запрос с JOIN
   SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id
   

---

🔴 Senior Level

Системный подход к оптимизации

Уровни оптимизации:

1. SQL Level (запрос)
   ├── Индексы
   ├── Рефакторинг запроса
   └── Статистика

2. Конфигурация
   ├── work_mem
   ├── shared_buffers
   ├── effective_cache_size
   └── random_page_cost

3. Архитектура
   ├── Денормализация
   ├── Партицирование
   ├── Materialized Views
   └── Read Replicas

4. Инфраструктура
   ├── SSD vs HDD
   ├── RAM
   ├── Connection Pooling
   └── Huge Pages

Plan Cache проблемы

Generic vs Custom Plans:

-- Проблема: запрос быстрый в psql, медленный в приложении
-- Причина: JDBC использует Prepared Statements → Generic Plan

-- Диагностика
EXPLAIN (ANALYZE) SELECT * FROM users WHERE email = $1;
-- Generic Plan: Seq Scan

-- Решение 1: plan_cache_mode
SET plan_cache_mode = 'force_custom_plan';

-- Решение 2: отключить Prepared Statements
-- JDBC URL: jdbc:postgresql://...?prepareThreshold=0

Implicit Casts

-- Проверка неявных приведений типов
EXPLAIN (ANALYZE) SELECT * FROM users WHERE phone = 89001234567;
-- Filter: (phone = '89001234567'::text)  ← cast!
-- → Index не используется

-- Решение: совпадающие типы
-- 1. Изменить тип колонки
ALTER TABLE users ALTER COLUMN phone TYPE bigint;

-- 2. Или передавать правильный тип
SELECT * FROM users WHERE phone = '89001234567';  -- text

Волатильность функций

-- Функции маркируются:
-- IMMUTABLE  → всегда один результат для тех же аргументов
-- STABLE     → один результат в рамках запроса
-- VOLATILE   → может меняться (по умолчанию)

-- ❌ VOLATILE функция не позволяет оптимизации
CREATE OR REPLACE FUNCTION get_status(id int) RETURNS text AS $$
  SELECT status FROM users WHERE id = $1;
$$ LANGUAGE sql;  -- По умолчанию VOLATILE

-- ✅ STABLE позволяет кэширование
CREATE OR REPLACE FUNCTION get_status(id int) RETURNS text AS $$
  SELECT status FROM users WHERE id = $1;
$$ LANGUAGE sql STABLE;

-- STABLE: в рамках ОДНОГО запроса функция вернёт тот же результат
-- для тех же аргументов. Планировщик вызовет её один раз и подставит.
-- Кэш живёт до конца запроса.

Архитектурные решения

Денормализация:

-- ❌ JOIN 10 таблиц для каждого запроса
SELECT u.name, o.amount, p.title, c.name as category
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE ...

-- ✅ Denormalized колонка
ALTER TABLE orders ADD COLUMN product_title text;
ALTER TABLE orders ADD COLUMN category_name text;

-- Обновлять через триггер
-- Запрос: без JOIN → в 10 раз быстрее

Materialized Views:

-- Для сложных аналитических запросов
CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT 
    department,
    COUNT(*) as orders_count,
    SUM(amount) as total_sales,
    AVG(amount) as avg_order
FROM orders
GROUP BY department;

-- Обновлять периодически
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_summary;

-- Запрос: мгновенно
SELECT * FROM mv_sales_summary;

Partitioning:

-- Для таблиц > 100 млн строк
CREATE TABLE orders (
    id serial,
    created_at date,
    amount numeric
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_y2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_y2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Partition Pruning (отсечение партиций): планировщик исключает лишние
-- партиции из плана. Данные из них даже не читаются с диска.
-- WHERE created_at > '2024-01-01' → сканирует только orders_y2024

Системный тюнинг

Параметры для SSD:

-- Планировщик должен доверять кэшу
SET random_page_cost = 1.1;       -- По умолчанию 4.0 (для HDD)
SET effective_cache_size = '24GB'; -- 75% RAM
SET shared_buffers = '8GB';       -- 25% RAM

Connection Pooling:

pgBouncer в режиме transaction:
  - 1000 соединений приложения → 50 соединений к БД
  - Экономия 30% CPU на переключении контекста
  - Меньше памяти на сессии

Huge Pages:

ОС + PostgreSQL:
  - Huge Pages = 2MB страницы вместо 4KB
  - Меньше TLB misses
    (TLB — Translation Lookaside Buffer, кэш процессора для адресов.
    При 4KB для 8GB RAM → 2 млн записей TLB (не влезает).
    При 2MB → 4000 записей (всё в TLB))
  - +10-20% производительности на высоких нагрузках

Алгоритм Senior-разработчика

1. pg_stat_statements → найти запросы с высоким shared_blks_read
2. EXPLAIN (ANALYZE, BUFFERS) → найти bottleneck узел
3. Проверить блокировки: pg_blocking_pids()
4. Проверить статистику: rows ≠ actual → ANALYZE
5. Проверить work_mem: Disk Spill → увеличить
6. Проверить plan_cache_mode: Generic Plan проблемы
7. Рефакторинг: денормализация, партицирование, кэширование
8. Если SQL оптимален → шардирование (Citus)

Когда НЕ использовать архитектурные решения

• Денормализация: НЕ используйте при частых UPDATE денормализованных полей (конфликты триггеров, рассинхронизация)
• Materialized Views: НЕ используйте для данных, которые должны быть актуальными в реальном времени (задержка между REFRESH)
• Partitioning: НЕ используйте для таблиц < 10 млн строк — overhead управления партициями перевешивает выгоду

Production Experience

Реальный сценарий #1: Комплексная оптимизация

• SaaS: API > 2 секунд (p95)
• Анализ:
  1. N+1 запросы (100 запросов на страницу)
  2. Seq Scan на таблице 50 млн строк
  3. work_mem = 4MB → spill на диск
  4. Generic Plan в Hibernate
• Решение:
  1. DataLoader → 5 запросов вместо 100
  2. Индекс на hot полях
  3. work_mem = 256MB
  4. plan_cache_mode = force_custom_plan
• Результат: p95 < 200ms (ускорение в 10 раз)

Реальный сценарий #2: Архитектурные изменения

• Аналитика: отчёт 5 минут (JOIN 12 таблиц)
• Решение:
  • Materialized View с REFRESH каждые 5 минут
  • Denormalized колонки для hot полей
  • Партицирование по дате
• Результат: отчёт < 1 секунда

Monitoring Dashboard

-- 1. Топ медленных запросов
SELECT 
    query,
    calls,
    mean_exec_time,
    total_exec_time,
    shared_blks_read,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 2. Блокировки
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 3. Bloat
SELECT 
    relname,
    n_dead_tup,
    n_live_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY dead_pct DESC;

-- 4. Cache Hit Rate
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    ROUND(100.0 * sum(heap_blks_hit) / NULLIF(sum(heap_blks_read) + sum(heap_blks_hit), 0), 2) as cache_hit_pct
FROM pg_statio_user_tables;
-- > 99% → отлично

Best Practices

1. Мониторьте через pg_stat_statements постоянно
2. EXPLAIN (ANALYZE, BUFFERS) для каждого проблемного запроса
3. Индексы — первое решение, но не единственное
4. work_mem контролируйте при Sort/Hash
5. plan_cache_mode проверяйте при использовании ORM
6. Денормализация для hot-path запросов
7. Materialized Views для аналитики
8. pgBouncer рекомендован при > 50 подключений. Для малых проектов (< 10 пользователей) можно обойтись без него.
9. Partitioning для таблиц > 100 млн строк
10. Cache Hit Ratio > 99% — цель

Резюме для Senior

• Оптимизация — не только индексы, это системный процесс
• Plan Cache (Generic vs Custom) — частая причина prod/dev расхождений
• Implicit Casts блокируют индексы → совпадающие типы
• Денормализация для hot-path, MV для аналитики
• work_mem контролируйте → Disk Spill = катастрофа
• pgBouncer в transaction mode → экономия 30% CPU
• Cache Hit Ratio > 99% → главный метрик здоровья БД
• Partitioning + Partition Pruning для больших таблиц
• Write Amplification (усиление записи): каждый INSERT/UPDATE записывает не только в таблицу, но и во ВСЕ индексы. 10 индексов = запись × 10. Физически записывается в 10x больше данных, чем изменил пользователь.

---

🎯 Шпаргалка для интервью

Обязательно знать:

• Алгоритм: pg_stat_statements → EXPLAIN (ANALYZE, BUFFERS) → исправить → проверить
• Частые проблемы: нет индекса, функция в WHERE, неявный cast, spill на диск, N+1
• Generic vs Custom Plans: Prepared Statements → plan_cache_mode = force_custom_plan
• Implicit Casts: phone = 8900... (int) vs phone = '8900...' (text) → cast блокирует индекс
• Волатильность функций: IMMUTABLE, STABLE, VOLATILE → влияет на оптимизацию
• Денормализация: горячие колонки → триггер для обновления
• Materialized Views: сложные аналитические запросы → REFRESH CONCURRENTLY
• Partitioning: таблицы > 100 млн строк → Partition Pruning
• Connection Pooling: pgBouncer в transaction mode → 1000 соединений → 50 к БД
• Системный тюнинг: random_page_cost = 1.1 (SSD), shared_buffers = 25% RAM
• Cache Hit Ratio > 99% — цель; < 95% → проблема

Частые уточняющие вопросы:

• «Как найти медленные запросы?» → pg_stat_statements ORDER BY total_exec_time DESC
• «Почему запрос быстрый в консоли, медленный в приложении?» → Generic Plan
• «Что делать при Disk Spill?» → SET work_mem = ‘256MB’ для сессии
• «Когда денормализация оправдана?» → Hot-path запросы, JOIN 10+ таблиц

Красные флаги (НЕ говорить):

• ❌ «Оптимизация = только индексы» (системный процесс: SQL, конфиг, архитектура, инфраструктура)
• ❌ «plan_cache_mode не важен» (Generic Plan — частая причина расхождений)
• ❌ «Materialized Views для OLTP» (только для аналитики!)
• ❌ «pgBouncer не нужен» (1000 соединений = 30% CPU на переключении)

Связанные темы:

• [[Для чего нужны индексы]] → первое решение для оптимизации
• [[Что такое explain plan]] → EXPLAIN (ANALYZE, BUFFERS)
• [[Зачем нужен ANALYZE]] → статистика для планировщика
• [[Как работает MVCC в PostgreSQL]] → Bloat и длинные транзакции