26. PostgreSQL Production Deep: Planner + Indexes

Зачем знать на Middle 3: Senior должен читать EXPLAIN ANALYZE как карту, понимать, почему planner выбрал Seq Scan вместо Index Scan, корректировать random_page_cost и effective_cache_size под реальное железо, выбирать правильный тип индекса (B-tree / GIN / BRIN / GiST) под нагрузку и видеть, где partial / covering / expression индексы экономят гигабайты IO. Без этого Postgres превращается в «чёрный ящик» — а в проде запрос, который локально летит за 5ms, на проде падает в Seq Scan на 200M строк.

---

Содержание

1. Концепция
2. Production-deep dive
3. Gotchas
4. Real cases
5. Вопросы
6. Practice
7. Источники

---

1. Концепция

PostgreSQL — это не «MySQL на стероидах», а полноценная СУБД с cost-based optimizer (CBO). Planner для каждого запроса считает стоимость нескольких альтернативных планов и выбирает дешевейший. Без понимания, как считается cost и откуда planner берёт статистику, любая тонкая оптимизация — гадание.

Pipeline запроса

SQL → Parser → Rewriter → Planner/Optimizer → Executor → Result
            (VIEW expand)  (paths, costs)    (Vol-Iter)

• Parser строит дерево разбора (parse tree).
• Rewriter раскрывает VIEW, применяет правила (RULE).
• Planner генерирует возможные планы выполнения (paths), считает cost для каждого и выбирает лучший. Для join’ов это NP-hard, поэтому для большого числа таблиц включается GEQO.
• Executor идёт по плану «volcano-style»: каждый узел плана — итератор, который при вызове next() возвращает кортеж.

Cost model

cost = startup_cost + run_cost
run_cost = pages_fetched * page_cost + tuples * cpu_cost + operator_cost

GUC, влияющие на cost:

Параметр	Default	Что значит
seq_page_cost	1.0	Стоимость одной последовательной страницы
random_page_cost	4.0	Стоимость одной random страницы (для SSD ставят 1.1)
cpu_tuple_cost	0.01	Стоимость обработки одного кортежа
cpu_index_tuple_cost	0.005	Стоимость одного индексного кортежа
cpu_operator_cost	0.0025	Стоимость одного оператора
effective_cache_size	4GB	Сколько данных условно в FS-кэше (для index scan cost)
work_mem	4MB	Память на одну операцию (sort/hash) — критично

Минимум для middle 3

Должен с ходу читать:

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using idx_orders_user_id on orders  (cost=0.43..8.45 rows=1 width=120)
   Index Cond: (user_id = 42)
   Buffers: shared hit=4

И сразу видеть: cost=startup..total, rows=ожидаемое число строк, width=средняя ширина строки в байтах, Buffers: shared hit/read = из shared_buffers/с диска.

---

2. Production-deep dive

2.1. Статистика и pg_stats

Planner полагается на статистику, собранную ANALYZE (запускается автовакуумом). Без свежей статистики оценки rows= врут — а на них строится выбор плана.

-- посмотреть статистику на колонку
SELECT
    attname,
    n_distinct,         -- сколько уникальных значений (или -doli, если > 0.1)
    most_common_vals,   -- MCV: топ-N самых частых значений
    most_common_freqs,  -- их частоты
    histogram_bounds,   -- гистограмма для распределения (default 100 bins)
    correlation         -- корреляция логического и физического порядка [-1; 1]
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders' AND attname = 'status';

Что важно понимать:

1. n_distinct: если planner думает, что у user_id 100 уникальных значений, а реально 10M — он выберет Hash Join вместо Nested Loop и проиграет.
2. most_common_vals + most_common_freqs: для skewed данных planner использует MCV отдельно от гистограммы. Если в status 95% строк — 'completed', planner это знает и не пойдёт по индексу.
3. histogram_bounds: 100 границ по умолчанию (default_statistics_target = 100). Для больших таблиц / неравномерных данных увеличивают:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

4. Extended statistics (PG 10+): корреляция между несколькими колонками.

CREATE STATISTICS orders_user_status (dependencies, ndistinct, mcv)
    ON user_id, status FROM orders;
ANALYZE orders;

Без CREATE STATISTICS planner считает колонки независимыми и сильно ошибается при WHERE по двум-трём колонкам.

2.2. Операторы плана

Scan operators:

Seq Scan         — полный последовательный прочит таблицы. Дёшев для маленьких таблиц
                   и больших процентных выборок (> ~5% строк), дорог для точечного.

Index Scan       — обход индекса + поход в heap за каждой строкой. Дорогой random IO,
                   если correlation низкая (данные «рассыпаны» по страницам).

Index-Only Scan  — обход индекса без хода в heap. Возможен, если visibility map
                   говорит «всё видимо», и все нужные колонки есть в индексе (или INCLUDE).
                   Требует регулярного VACUUM для актуальности VM.

Bitmap Index Scan + Bitmap Heap Scan
                 — строится bitmap страниц по индексу, потом heap читается ОДНИМ
                   проходом в порядке физического хранения (sequential-friendly).
                   Идеально, когда строк много, но не «полтаблицы».

Tid Scan         — по физическому идентификатору ctid (редко руками).

Join operators:

Nested Loop      — outer × inner. Дёшев, если outer мал, inner проиндексирован.
                   Без индекса O(N*M).

Hash Join        — строим hash inner-таблицы (in-memory если влезает в work_mem,
                   иначе пишем во временные файлы — batches), пробегаем outer.
                   Лучший выбор для равенств на больших таблицах.

Merge Join       — обе стороны отсортированы, идём parallel cursor'ами.
                   Выигрывает, когда сортировка уже есть (например, по индексу).

Aggregate:

HashAggregate    — строим хэш по группам. In-memory. С PG 13+ spill-to-disk.
GroupAggregate   — требует отсортированного входа, идёт O(N) одним проходом.

Прочие:

• Sort — внешняя/внутренняя сортировка (если sort_mem work_mem мало → on-disk).
• Materialize — кэширование результата для re-сканов в Nested Loop.
• Memoize (PG 14+) — LRU-кэш для параметризованных подзапросов в Nested Loop.
• CTE Scan — для WITH (с PG 12 — может быть inline’нут, было WITH ... AS MATERIALIZED).
• Subquery Scan — оборачивает подзапрос.
• Append / Merge Append — для partition / UNION ALL.

2.3. EXPLAIN — что включать

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT u.email, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > now() - interval '7 days'
GROUP BY u.email;

• ANALYZE — реально выполняет запрос, показывает actual time, actual rows, loops.
• BUFFERS — shared hit / read / dirtied / written, temp read / written (если sort на диске), local hit / read (для временных таблиц). Главный инструмент Senior’а — IO виден явно.
• VERBOSE — schema-qualified имена, output columns.
• FORMAT JSON — для парсинга в Grafana / своих скриптах.
• SETTINGS (PG 12+) — какие non-default GUC влияли на план.
• WAL (PG 13+) — сколько WAL сгенерировано (для DML).

⚠️ ANALYZE реально выполняет запрос. Для UPDATE/DELETE/INSERT оберни в транзакцию с ROLLBACK:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'archived' WHERE id < 1000;
ROLLBACK;

Чтение Buffers:

Buffers: shared hit=12345 read=67 dirtied=10 written=0
                     ↑           ↑          ↑
                     │           │          └ грязные страницы (DML)
                     │           └ из bgwriter / checkpoint
                     └ из shared_buffers (быстро)
       read=67 — пришлось читать с диска (либо FS-cache, либо реальный IO)

2.4. Типы индексов

B-tree (default):

• Для =, <, >, BETWEEN, IN, IS NULL, сортировка по индексу.
• Для LIKE 'foo%' — да (prefix), для LIKE '%foo%' — нет.
• Сбалансированное дерево, ~3-4 уровня для миллиардных таблиц.

CREATE INDEX idx_orders_created_at ON orders (created_at);
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

⚠️ Порядок колонок в multicolumn критичен — WHERE created_at > X не использует индекс (status, created_at).

Hash (rarely used):

• Только =. С PG 10 WAL-logged (до этого не выживал crash). Размер меньше B-tree, но diff небольшой; B-tree почти всегда лучше из-за range queries.

GIN (Generalized Inverted Index):

• Для массивов, JSONB, полнотекста, trigram (LIKE ‘%foo%’).
• Хранит inverted map: значение → список tid.
• Медленнее на UPDATE/INSERT (есть fastupdate — отложенные вставки в pending list, потом мерж).

-- JSONB
CREATE INDEX idx_events_data ON events USING GIN (data);
CREATE INDEX idx_events_data_jsonb_ops ON events USING GIN (data jsonb_path_ops);
-- jsonb_path_ops меньше и быстрее для @> запросов, но НЕ поддерживает ?, ?|, ?&

-- full-text
CREATE INDEX idx_posts_fts ON posts USING GIN (to_tsvector('english', body));

-- LIKE '%foo%'
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_email_trgm ON users USING GIN (email gin_trgm_ops);
SELECT * FROM users WHERE email LIKE '%@example.com';  -- работает по GIN

GiST (Generalized Search Tree):

• Для геометрии (PostGIS), полнотекста, range types, k-NN.
• Lossy index — после индексного шага нужен recheck.
• Хорош для intersect/contains queries.

CREATE INDEX idx_geo_loc ON points USING GIST (location);
CREATE INDEX idx_range_period ON bookings USING GIST (period);  -- tstzrange

SP-GiST (space-partitioned GiST):

• Quadtree / KD-tree / radix tree.
• IP-адреса (inet), точки в 2D, тексты с префиксами.

BRIN (Block Range Index):

• Хранит min/max по диапазону блоков (default 128 страниц).
• Идеален для отсортированных данных: time series, append-only логи.
• Микроразмер (KB вместо GB), очень дёшев на write.
• Lossy — на каждой странице будет recheck.

CREATE INDEX idx_logs_ts_brin ON logs USING BRIN (ts) WITH (pages_per_range = 32);

⚠️ BRIN бесполезен, если данные неупорядочены: planner поедет в Seq Scan или будет читать почти всё.

Bloom (extension):

• Probabilistic, для multi-column equality. Полезен, когда нужно индексировать 5+ колонок без B-tree на каждую.

CREATE EXTENSION bloom;
CREATE INDEX idx_events_bloom ON events USING bloom (a, b, c, d, e)
  WITH (length=80, col1=2, col2=2, col3=4, col4=2, col5=4);

2.5. Специальные техники индексирования

Partial index — индекс с WHERE:

CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';
-- Размер в 10x меньше, чем full index, если pending — 5% строк.
-- Используется только для запросов с WHERE status='pending'.

Covering index (INCLUDE, PG 11+):

CREATE INDEX idx_orders_user_inc ON orders (user_id) INCLUDE (status, total);
-- Колонки status, total доступны через index-only scan без heap fetch,
-- но не участвуют в сортировке / уникальности.

Functional / expression index:

CREATE INDEX idx_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'foo@bar.com';  -- использует индекс

CREATE INDEX idx_events_day ON events (date_trunc('day', ts));

⚠️ Запросы должны точно повторять выражение, иначе planner не свяжет.

Concurrent / Reindex Concurrently:

CREATE INDEX CONCURRENTLY idx_x ON orders (x);
-- Не блокирует writes (но дольше, в 2-3x проходов).
-- Может оставить INVALID индекс при ошибке — проверяй pg_index.indisvalid.

REINDEX INDEX CONCURRENTLY idx_x;  -- PG 12+
REINDEX TABLE CONCURRENTLY orders;  -- PG 12+

2.6. Query rewriting и подсказки

Planner делает массу преобразований:

• VIEW expansion — VIEW inlin’ится в запрос.
• Subquery flattening — SELECT * FROM (SELECT * FROM t WHERE x) sub WHERE y → SELECT * FROM t WHERE x AND y.
• Predicate pushdown — для UNION ALL, partition, FDW.
• JOIN reordering — пробует разные порядки, пока join_collapse_limit (default 8) не превышен. Дальше — GEQO.

Hints через pg_hint_plan (extension):

/*+ SeqScan(orders) IndexScan(users idx_users_pk) */
SELECT * FROM users u JOIN orders o ON o.user_id = u.id WHERE u.id = 1;

⚠️ Postgres community официально не любит хинты («исправь статистику»). Но pg_hint_plan широко используется в РФ и Asia в проде. На AWS RDS — нужно подключать руками (только Aurora не поддерживает).

auto_explain — обязательно в проде:

# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000    # запросы >1s
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_format = json
auto_explain.log_nested_statements = on

Это даёт реальные планы slow queries без необходимости воспроизводить.

2.7. Visualisation cost-cycle для планера

                  ┌─────────────────────────────┐
                  │   1. Сгенерировать paths    │
                  │     (Scan, Join, Agg)       │
                  └──────────────┬──────────────┘
                                 ▼
                  ┌─────────────────────────────┐
                  │ 2. Для каждой пары таблиц — │
                  │   попробовать все join'ы    │
                  │ (NL, Hash, Merge × порядки) │
                  └──────────────┬──────────────┘
                                 ▼
       join_collapse_limit ── 8 ────► full search
                                 │
                              иначе
                                 ▼
                            GEQO (genetic)
                                 │
                                 ▼
                  ┌─────────────────────────────┐
                  │  3. Выбрать min(cost)       │
                  └──────────────┬──────────────┘
                                 ▼
                            EXECUTE

2.8. GEQO (Genetic Query Optimizer)

При большом числе таблиц (default ≥ geqo_threshold = 12) полный перебор слишком дорог. GEQO применяет генетический алгоритм:

• Случайные «гены» = последовательности join’ов.
• Мутации + кроссовер.
• Не гарантирует оптимума, но укладывается в адекватное время.

⚠️ GEQO не детерминирован — план может меняться от запуска к запуску. Для критичных аналитических запросов отключают set geqo = off; и поднимают join_collapse_limit.

2.9. pg_buffercache + pg_stat_statements

-- сколько каких блоков в shared_buffers
SELECT relname, count(*) AS buffers
FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY relname ORDER BY buffers DESC LIMIT 20;

pg_stat_statements — top по cumulative cost / time / IO. Включается через shared_preload_libraries = 'pg_stat_statements'.

SELECT query, calls, total_exec_time, mean_exec_time, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

2.10. Tuning под железо

Базовый набор для 64 GB RAM, NVMe SSD, 16 CPU, OLTP-нагрузка:

shared_buffers = 16GB              # 25% RAM
effective_cache_size = 48GB        # ~75% RAM (для planner'а, не аллокация)
work_mem = 32MB                    # на операцию; умножай на N сессий × сложность
maintenance_work_mem = 2GB         # для VACUUM, CREATE INDEX
wal_buffers = 64MB
random_page_cost = 1.1             # для NVMe (default 4.0 — для HDD)
effective_io_concurrency = 200     # для NVMe
max_worker_processes = 16
max_parallel_workers = 16
max_parallel_workers_per_gather = 4
default_statistics_target = 200    # больше точности для planner
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 16GB
min_wal_size = 4GB

---

3. Gotchas (12+)

⚠️ 1. Свежесть статистики

После массивного INSERT/DELETE/UPDATE planner работает по старой статистике. Autovacuum запускает ANALYZE автоматически, но порог — autovacuum_analyze_threshold + 0.1 * pg_class.reltuples. Для большой таблицы 0.1 — это много. После bulk-load руками:

ANALYZE VERBOSE orders;

⚠️ 2. n_distinct underestimation

PG считает n_distinct по выборке (default ≈ 300 × default_statistics_target). Для очень разнообразных колонок (UUID, timestamp_ns) часто промазывает в разы. Костыль:

ALTER TABLE users ALTER COLUMN id SET (n_distinct = -1);  -- говорим "все уникальны"
ANALYZE users;

⚠️ 3. random_page_cost = 4 для SSD

Default 4.0 заточен под HDD. На NVMe random IO почти бесплатен — оставь 1.1. Иначе planner избегает Index Scan, любит Seq Scan и плачет.

⚠️ 4. work_mem × N сессий = OOM

work_mem — на каждую sort/hash в каждой сессии. При сотнях коннектов и сложных запросах легко съесть всю RAM. Сначала pgbouncer / снижай число коннектов, потом увеличивай work_mem.

⚠️ 5. LIKE ‘%foo%’ без trgm

Подзапрос с LIKE '%foo%' всегда Seq Scan на B-tree индексе. Нужен pg_trgm + GIN.

⚠️ 6. Index-only scan требует VACUUM

Без свежего VM (visibility map) даже covering index пойдёт в heap fetch.

SELECT relname, heap_blks_read, idx_blks_read FROM pg_statio_user_tables;
-- большое idx_blks_read + большое heap_blks_read = не получается IOS
VACUUM (VERBOSE, ANALYZE) orders;

⚠️ 7. ORDER BY … LIMIT без индекса под порядок

SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 10;
-- без идекса (user_id, created_at DESC) — будет Index Scan по user_id + Sort
-- Sort на миллионах строк = катастрофа.
CREATE INDEX ON orders (user_id, created_at DESC);

⚠️ 8. NULL в B-tree

NULL индексируется (в конце, по умолчанию). Но WHERE col != 'x' не идёт по B-tree — planner не верит селективности.

⚠️ 9. Функция в WHERE «съедает» индекс

WHERE date(created_at) = '2026-01-01'   -- не идёт по индексу на created_at
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'  -- идёт

⚠️ 10. Implicit type cast блокирует индекс

-- col text, передаём int → cast → Seq Scan
WHERE phone = 79991234567
-- правильно:
WHERE phone = '79991234567'

Особенно болезненно для UUID, INET, MAC, NUMERIC.

⚠️ 11. CTE как fence (до PG 12)

В PG ≤ 11 CTE (WITH x AS (...)) — всегда materialize’дный «барьер»: planner не пушит предикаты. В PG 12+ можно использовать WITH ... AS NOT MATERIALIZED, и planner inline’ит. Старый код после апгрейда внезапно может поменять план.

⚠️ 12. Lateral join без индекса

SELECT u.id, t.* FROM users u, LATERAL (
  SELECT * FROM orders o WHERE o.user_id = u.id ORDER BY o.id DESC LIMIT 1
) t;

Без индекса на (user_id, id DESC) — Seq Scan для каждого пользователя. На миллион юзеров — катастрофа.

⚠️ 13. Generic plan vs custom plan в prepared statements

PG до 5-го выполнения prepared строит custom plan (с фактическими значениями), потом переключается на generic. Если параметр сильно скошен (status=‘pending’), generic план оптимизирован под среднюю селективность и проигрывает. Лечение — plan_cache_mode = force_custom_plan для сессии.

⚠️ 14. Boolean column index

B-tree на bool обычно бесполезен — селективность 50/50, planner идёт в Seq Scan. Решение — partial index WHERE is_active = true.

⚠️ 15. Toasted columns в индексе

Большие текстовые/jsonb колонки уходят в TOAST. Индексировать целиком — плохо (большие keys). Лучше — expression index по hash / md5.

---

4. Real cases

Case 1: Slow EXPLAIN из-за неверного n_distinct

Симптом: SELECT … WHERE user_id = $1 AND status = 'active' стабильно 30ms, иногда 5s.

Расследование: EXPLAIN ANALYZE показывает rows=1 (estimate), actual rows=2_000_000. Planner выбрал Nested Loop, ушёл в Seq Scan inner-таблицы.

Решение:

CREATE STATISTICS users_orders_ndist (ndistinct, dependencies)
    ON user_id, status FROM orders;
ANALYZE orders;

После — planner оценивает корректно, переключается на Hash Join. Latency обратно 30ms.

Case 2: BRIN для логов

Контекст: Таблица events 5TB, time-series, append-only. B-tree на (created_at) — 80GB.

Решение: заменили B-tree на BRIN:

DROP INDEX idx_events_created_at;
CREATE INDEX idx_events_created_at_brin ON events USING BRIN (created_at)
    WITH (pages_per_range = 16);

Размер индекса упал до 30MB. Запросы WHERE created_at > now() - interval '1 day' — те же 200ms.

Case 3: Партиционирование по месяцам

Контекст: Таблица logs росла на 50GB / месяц, VACUUM не успевал.

Решение: перешли на native partitioning:

CREATE TABLE logs (
    id bigserial,
    ts timestamptz NOT NULL,
    payload jsonb,
    PRIMARY KEY (id, ts)
) PARTITION BY RANGE (ts);

CREATE TABLE logs_2026_01 PARTITION OF logs
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
-- pg_partman автоматизирует создание + retention

DELETE старых партиций = O(1) (DROP TABLE logs_2025_01), VACUUM не нужен.

Case 4: Index bloat

Симптом: Index Scan стал медленнее в 5 раз.

Расследование: pgstattuple показал bloat 70%. Слишком много dead tuples из-за апдейтов колонок индекса.

Решение: REINDEX INDEX CONCURRENTLY idx_x; + перешли на HOT-updates где можно (не апдейтить indexed columns).

Case 5: pg_stat_statements top query

В первый день включения pg_stat_statements обнаружили, что 40% времени БД тратится на один запрос — SELECT 1 от health-check’а каждого пода (1000 подов × раз в секунду). Перевели health-check на pg_is_in_recovery() через replica-route, нагрузка на master упала на 35%.

---

5. Вопросы (30)

1. Что такое cost-based optimizer и из чего складывается cost в Postgres?
2. Какие GUC влияют на выбор Index Scan vs Seq Scan и какие значения ставить для NVMe?
3. Чем отличается EXPLAIN от EXPLAIN ANALYZE?
4. Что показывает опция BUFFERS и как читать shared hit / read / dirtied?
5. Когда planner выберет Seq Scan вместо Index Scan на индексированной колонке?
6. В чём разница между Index Scan, Index-Only Scan и Bitmap Index Scan?
7. Когда используется Bitmap Heap Scan и почему он эффективнее обычного Index Scan?
8. Чем Hash Join отличается от Merge Join и Nested Loop? Когда какой?
9. Что такое work_mem и какие операции от него зависят?
10. Что такое pg_stats, MCV, гистограммы? Как planner их использует?
11. Что такое n_distinct = -1 и когда его ставят?
12. Что такое extended statistics (CREATE STATISTICS) и зачем нужны?
13. Какие есть типы индексов в Postgres и для каких задач?
14. Чем отличается GIN от GiST? Когда какой выбрать?
15. Когда оправдан BRIN индекс? Почему он крошечный?
16. Что такое partial / covering / functional индексы? Приведи примеры.
17. Почему LIKE '%foo%' не использует B-tree индекс? Как сделать, чтобы использовал?
18. Зачем нужен CREATE INDEX CONCURRENTLY? Какие у него ограничения?
19. Что такое HOT-update и почему он важен для индекс bloat?
20. Что показывает correlation в pg_stats?
21. Почему index-only scan может «не сработать» даже при подходящем индексе?
22. Что такое prepared statement, generic vs custom plan?
23. Что такое CTE fence и как это поменялось в PG 12?
24. Что такое GEQO и при каких условиях он включается?
25. Что делает auto_explain и почему его включают в проде?
26. Что такое pg_stat_statements и какие топ-метрики смотреть?
27. Как diagnose’ить медленные запросы в проде без воспроизведения?
28. Что такое pg_hint_plan, когда применять, какие риски?
29. Какие настройки postgresql.conf критичны для tuning’а под OLTP?
30. Как партиционирование помогает оптимизатору и при каких условиях оно «pruning»-ит партиции?

---

6. Practice

6.1. EXPLAIN-чтение

-- 1. Создай таблицу
CREATE TABLE t (id bigserial PRIMARY KEY, k int, v text, ts timestamptz);
INSERT INTO t (k, v, ts)
SELECT
    (random()*1000)::int,
    md5(random()::text),
    now() - (random() * interval '90 days')
FROM generate_series(1, 1_000_000);

-- 2. Получи план без индекса
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM t WHERE k = 42;

-- 3. Добавь индекс
CREATE INDEX ON t (k);
ANALYZE t;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM t WHERE k = 42;

-- 4. Сравни Buffers, cost, actual time

6.2. Test BRIN vs B-tree

CREATE TABLE logs_btree (ts timestamptz, msg text);
CREATE TABLE logs_brin (ts timestamptz, msg text);
INSERT INTO logs_btree SELECT now() - (i || ' seconds')::interval, 'msg' FROM generate_series(1, 10_000_000) i;
INSERT INTO logs_brin SELECT now() - (i || ' seconds')::interval, 'msg' FROM generate_series(1, 10_000_000) i;
CREATE INDEX ON logs_btree (ts);
CREATE INDEX ON logs_brin USING BRIN (ts);
SELECT pg_size_pretty(pg_relation_size('logs_btree_ts_idx'));
SELECT pg_size_pretty(pg_relation_size('logs_brin_ts_idx'));
-- query сравни
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM logs_btree WHERE ts > now() - interval '1 hour';
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM logs_brin WHERE ts > now() - interval '1 hour';

6.3. Найди топ запросы

CREATE EXTENSION pg_stat_statements;
-- через минуту работы:
SELECT queryid, calls, mean_exec_time, total_exec_time, rows
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

6.4. Trigram index

CREATE EXTENSION pg_trgm;
CREATE TABLE u (id serial, email text);
INSERT INTO u (email) SELECT 'user' || i || '@example.com' FROM generate_series(1, 500_000) i;
CREATE INDEX ON u USING GIN (email gin_trgm_ops);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM u WHERE email LIKE '%12345%';

6.5. auto_explain

Включи в postgresql.conf, перезапусти, выполни slow query, найди план в логах.

6.6. Партиционирование

Создай таблицу events, партиционированную по месяцу, вставь данные за 3 месяца, проверь partition pruning в плане:

EXPLAIN (ANALYZE) SELECT count(*) FROM events WHERE ts >= '2026-03-01' AND ts < '2026-04-01';
-- должен сканировать только events_2026_03

---

7. Источники

1. PostgreSQL Documentation — Chapter 14 «Performance Tips», 11 «Indexes», 70 «How the Planner Uses Statistics». https://www.postgresql.org/docs/current/
2. «PostgreSQL 14 Internals» — Egor Rogov (Postgres Pro). Полный разбор архитектуры, MVCC, индексов, планера. https://postgrespro.com/community/books/internals
3. Bruce Momjian — «Mastering PostgreSQL Administration» slides. Постоянно обновляются. https://momjian.us/main/presentations/
4. «Use the Index, Luke!» — Markus Winand. Книга по B-tree и оптимизации, идиоматично объясняет ORDER BY, paging, multicolumn. https://use-the-index-luke.com/
5. explain.depesz.com — визуализатор и анализатор планов. Обязательный инструмент.
6. explain.dalibo.com — альтернатива, лучше показывает Buffers.
7. pg_stat_statements docs — https://www.postgresql.org/docs/current/pgstatstatements.html
8. pg_hint_plan — https://github.com/ossc-db/pg_hint_plan
9. POSETTE / PostgresWorld talks — записи 2024-2025 по index-internals и query planning. https://www.youtube.com/@Postgresworld
10. «PostgreSQL High Performance Cookbook» — Chitij Chauhan, Dinesh Kumar. Практические рецепты по tuning’у.
11. The Internals of PostgreSQL — Hironobu Suzuki. http://www.interdb.jp/pg/
12. pg_buffercache docs — https://www.postgresql.org/docs/current/pgbuffercache.html
13. Citus / Crunchy Data blogs — много статей про индексы, partial, BRIN, JSONB.