Планировщик запросов, статистика, стоимость операций и факторы выбора.
Оптимизатор PostgreSQL — это «мозг» базы данных. Он решает, какой индекс использовать, как соединять таблицы и в каком порядке выполнять операции.
SQL запрос
↓
┌─────────────────┐
│ Parser │ ← Проверка синтаксиса
└────────┬────────┘
↓
┌─────────────────┐
│ Rewriter │ ← Применение правил (views, rules)
└────────┬────────┘
↓
┌─────────────────┐
│ Optimizer │ ← Выбор плана выполнения
│ (Planner) │
└────────┬────────┘
↓
┌─────────────────┐
│ Executor │ ← Выполнение плана
└─────────────────┘
| Параметр | Описание | Значение по умолчанию |
|---|---|---|
seq_page_cost | Стоимость последовательного чтения страницы | 1.0 |
random_page_cost | Стоимость случайного чтения страницы | 4.0 |
cpu_tuple_cost | Стоимость обработки строки | 0.01 |
cpu_index_tuple_cost | Стоимость обработки строки из индекса | 0.005 |
cpu_operator_cost | Стоимость оператора (WHERE, JOIN) | 0.0025 |
Total Cost =
(страницы × random_page_cost) +
(строки × cpu_index_tuple_cost) +
(операторы × cpu_operator_cost)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';Index Scan:
Index Scan using idx_users_email on users
cost=0.43..8.45 rows=1 width=32
-- Просмотр статистики
SELECT
attname as column_name,
null_frac,
avg_width,
n_distinct,
correlation
FROM pg_stats
WHERE tablename = 'users';| Столбец | Описание |
|---|---|
null_frac | Доля NULL значений (0-1) |
avg_width | Средняя ширина значения в байтах |
n_distinct | Количество уникальных значений (>0) или доля (-1..0) |
correlation | Корреляция между физической и логической сортировкой |
Селективность — доля строк, удовлетворяющих условию.
-- Таблица с 1 млн записей
-- WHERE id = 100 → селективность 0.000001 (одна строка)
-- WHERE status = 'active' → селективность 0.5 (50% строк)-- Для = (равенство)
селективность = 1 / n_distinct
-- Для > < (диапазон)
селективность = (значение - min) / (max - min)
-- Для LIKE с префиксом
селективность = статистика по первым символамEXPLAIN SELECT * FROM users WHERE status = 'active';Seq Scan on users
Filter: (status = 'active'::text)
-- Оптимизатор знает из статистики:
-- n_distinct = 4 (active, pending, cancelled, completed)
-- селективность = 1/4 = 0.25
-- ожидается 250K строк из 1 млн
Seq Scan on users
Filter: (status = 'active')
Rows Removed by Filter: 750000Когда выбирается:
Index Scan using idx_users_email on users
Index Cond: (email = 'test@example.com')Когда выбирается:
Bitmap Heap Scan on users
Recheck Cond: (status = 'active')
-> Bitmap Index Scan on idx_users_status
Index Cond: (status = 'active')Когда выбирается:
Index Only Scan using idx_users_covering on users
Index Cond: (email = 'test@example.com')Когда выбирается:
-- Высокая селективность → Index Scan
SELECT * FROM users WHERE id = 100; -- 1 строка из 1 млн
-- Низкая селективность → Seq Scan
SELECT * FROM users WHERE gender = 'M'; -- 50% строк-- Маленькая таблица → Seq Scan (дешевле)
SELECT * FROM small_table WHERE id = 100;
-- Большая таблица → Index Scan (дешевле)
SELECT * FROM large_table WHERE id = 100;-- B-дерево: =, <, >, BETWEEN, ORDER BY
SELECT * FROM users WHERE email = 'test@example.com';
-- Hash: только =
SELECT * FROM users WHERE email = 'test@example.com';
-- GIN: @>, &&, ?
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];
-- BRIN: диапазоны для упорядоченных данных
SELECT * FROM logs WHERE created_at > NOW() - INTERVAL '1 hour';-- Индекс может устранить сортировку
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- Index Scan с ORDER BY создан_at DESC
-- Без индекса — сортировка + Top-N heapsort
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- Sort + LimitSELECT * FROM users
WHERE status = 'active' AND created_at > '2026-01-01';Bitmap Heap Scan on users
Recheck Cond: (status = 'active') AND (created_at > ...)
-> BitmapAnd
-> Bitmap Index Scan on idx_users_status
Index Cond: (status = 'active')
-> Bitmap Index Scan on idx_users_created_at
Index Cond: (created_at > '2026-01-01')
SELECT * FROM users
WHERE status = 'active' OR status = 'pending';Bitmap Heap Scan on users
-> BitmapOr
-> Bitmap Index Scan on idx_users_status
Index Cond: (status = 'active')
-> Bitmap Index Scan on idx_users_status
Index Cond: (status = 'pending')
-- Запретить Seq Scan
SET enable_seqscan = off;
SELECT * FROM users WHERE status = 'active';
-- Принудительный Index Scan
-- Запретить Index Scan
SET enable_indexscan = off;
SELECT * FROM users WHERE id = 100;
-- Bitmap Scan или Seq Scan
-- Запретить Bitmap Scan
SET enable_bitmapscan = off;Важно: Используйте только для тестирования! Не в production.
-- Для SSD: случайное чтение почти так же быстро, как последовательное
SET random_page_cost = 1.1;
-- Для HDD: случайное чтение медленное
SET random_page_cost = 4.0;
-- Для CPU-интенсивных запросов
SET cpu_tuple_cost = 0.02;-- Проверка последнего ANALYZE
SELECT
relname,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'users';
-- Ручное обновление статистики
ANALYZE users;
-- Настройка автовакуума
ALTER TABLE users SET (
autovacuum_analyze_threshold = 50,
autovacuum_analyze_scale_factor = 0.1
);-- Базовый вывод
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- С метриками
EXPLAIN (ANALYZE) SELECT * FROM users WHERE email = 'test@example.com';
-- С буферами
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'test@example.com';
-- В формате JSON
EXPLAIN (FORMAT JSON, ANALYZE) SELECT * FROM users WHERE email = 'test@example.com';Bitmap Heap Scan on users (cost=100.00..500.00 rows=1000 width=32)
Actual Time=0.5..1.2 ms rows=1000 loops=1
Recheck Cond: (status = 'active'::text)
Heap Blocks: exact=50
Buffers: shared hit=50
-> Bitmap Index Scan on idx_users_status (cost=0.00..100.00 rows=1000 width=0)
Actual Time=0.3..0.3 ms rows=1000 loops=1
Index Cond: (status = 'active'::text)
Buffers: shared hit=20
Ключевые метрики:
cost — оценочная стоимостьActual Time — реальное время (с ANALYZE)rows — оценочное / фактическое количество строкBuffers — чтения из буфера-- Seq Scan на большой таблице
EXPLAIN SELECT * FROM large_table WHERE indexed_column = 'value';
-- Если Seq Scan вместо Index Scan — проблема!
-- Sort вместо Index Scan
EXPLAIN SELECT * FROM users ORDER BY created_at LIMIT 10;
-- Если Sort — нет индекса для сортировки
-- Высокий Heap Fetch
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- Heap Fetch > 0 — проблема с visibility map-- Проблема: оптимизатор выбирает неверный план
EXPLAIN SELECT * FROM users WHERE status = 'active';
-- Ожидается 1000 строк, фактически 500K
-- Решение: обновить статистику
ANALYZE users;-- Проблема: LIKE с переменной
EXPLAIN SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Оптимизатор не знает селективность '%@gmail.com'
-- Решение: статистика расширений
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_email_trgm ON users USING GIN (email gin_trgm_ops);-- Проблема: план кэшируется для первых параметров
PREPARE stmt(text) AS SELECT * FROM users WHERE email = $1;
EXECUTE stmt('rare@example.com'); -- План для редкого значения
EXECUTE stmt('common@gmail.com'); -- Тот же план, но неоптимальный
-- Решение: ANALYZE или перепланирование
EXECUTE stmt('common@gmail.com'); -- После ANALYZE план обновится-- Проблема: подзапрос выполняется для каждой строки
SELECT u.*, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;
-- Решение: JOIN с GROUP BY
SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;-- Статистика для выражений
CREATE STATISTICS stats_users_email_lower ON (dependencies)
FROM users (LOWER(email));
ANALYZE users;-- Статистика зависимостей между колонками
CREATE STATISTICS stats_orders_user_status (dependencies)
ON user_id, status FROM orders;
ANALYZE orders;
-- Проверка
SELECT * FROM pg_stats_ext WHERE tablename = 'orders';PostgreSQL не поддерживает hints напрямую, но можно влиять через:
-- Конфигурация
SET random_page_cost = 1.1;
-- Временные индексы
CREATE TEMP INDEX idx_temp ON table(column);
-- CTE для материализации
WITH filtered AS (
SELECT * FROM large_table WHERE complex_condition
)
SELECT * FROM filtered WHERE simple_condition;pg_stat_statements для медленных запросовenable_seqscan = off в productionВопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.