🎓 100 уроков EXPLAIN. Урок 3
Почему Postgres игнорирует индекс

Утро. Макс заходит в офис с двумя кружками кофе — одна для себя, вторая для Лены. Та уже сидит за компьютером и хмуро разглядывает EXPLAIN своего запроса.

— Ну что, готова узнать, почему твой запрос до сих пор тормозит? — Макс ставит кружку перед ней. — Вчера я обещал рассказать про индексы.

Лена (показывая на экран):
— Вот! Я сделала табличку на тесте, создала индекс по user_id, но постгрес всё равно делает Seq Scan! Он что, тупой?

Макс разворачивает монитор и смотрит на план запроса Лены:

EXPLAIN SELECT * FROM my_tasks WHERE user_id = 1;

— Да нет же, — Макс смеётся. — Он просто знает то, чего не знаешь ты. Есть четыре ключевых ситуации, когда оптимизатор сознательно выбирает полное сканирование таблицы вместо использования индекса:

1️⃣ Маленькие таблицы (Seq Scan выгоднее)
Почему: При малом объёме данных стоимость полного чтения всей таблицы часто ниже, чем прыжки по индексу с последующими обращениями к таблице.

2️⃣ Фильтрация по функции от поля (требуется специальный индекс - функциональный)
Примеры:

SELECT * FROM users WHERE concat(first_name, ' ', last_name) = 'Макс Логинов';
SELECT * FROM users WHERE age + 5 > 30;

Несмотря на то, что есть индексы по полям first_name, last_name и age - они не будет использованы.
Почему: Обычный индекс хранит исходные значения. Postgres не может сопоставить вычисляемое выражение с обычным индексом.
Есть и исключения, когда Postgres может преобразовать условие так, чтобы применялся индекс, либо используется Index Only Scan, но об этом в другой раз.
Решение: Создать функциональный индекс, который заранее вычисляет нужное преобразование:

CREATE INDEX idx_users_fullname ON users (concat(first_name, ' ', last_name));

3️⃣ Неселективные условия (индекс не даёт выгоды)
Примеры:

-- Плохо (нет селективности):
SELECT * FROM my_tasks WHERE status = 'closed'; -- 98% строк

-- Хорошо (высокая селективность):  
SELECT * FROM my_tasks WHERE status = 'new'; -- < 1% строк

Почему: Проще прочитать всю таблицу, чем выбирать все эти строки по одной по индексу. Postgres собирает и использует статистику о распределении данных в таблицах. При подготовке плана он проверяет селективность условий и выбирает оптимальный вариант.
Обычно индекс используется, если условия должны вернуть не больше чем 5-10% от общего числа строк.

4️⃣ Устаревшая статистика (оптимизатор "не в курсе")
Обычно Postgres самостоятельно обновляет статистику (через autovacuum). Но в некоторых ситуациях статистика может устареть или быть неактуальной. Например, если ты только что залила миллион строк — Postgres живёт в прошлом. Посмотреть можно, например, так:

SELECT schemaname, relname, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE relname = 'твоя_таблица';  -- или без условия для списка всех таблиц

Собрать статистику принудительно:

ANALYZE твоя_таблица;

Лена отставила кружку:
— Спасибо, теперь стало понятнее, где копать. А покажешь, что делать, если индекс есть, но запрос всё равно медленный?

— Конечно! — Макс ухмыляется. — Завтра разберём Partial Indexes и Index Only Scan.

#explain #индексы #статистика

P.S. Макс вечером записал в своем дневнике:
«Лена уже сама создает индексы . Теперь главное — не дать ей создать индекс на каждое поле...»

🎓 100 уроков EXPLAIN. Практика
join + group by против подзапроса

Переработав очередной алерт о долгом запросе, Макс задумался.
— Лене стоило бы это увидеть, — пробормотал он, подключаясь к тестовой базе.

Через 10 минут был готов демо-стенд для объяснения утренней проблемы. На экране красовались два одинаковых по смыслу запроса. Один — медленный, как сонный слак-бот, другой — стремительный, как "kill -9"

Когда Лена пришла в оговоренное время, Макс загадочно улыбнулся:
— Сегодня я покажу тебе, как JOINы превращают 10к строк в 150 миллионов, чтобы потом снова выдать 10к строк... и как это можно обойти. Это реальный случай, но мы разберем его на простом синтетическом примере.

— Ой, Макс, только не начинай снова про эти твои "простые примеры"! — Лена закатила глаза, но тут же заинтересованно придвинула стул. — В прошлый раз твой "синтетический пример" заставил мой комп 10 минут гудеть вентиляторами...

Макс улыбнулся и показал ей оригинальный запрос и план выполнения:

EXPLAIN ANALYZE
SELECT count(*), avg(t1.value), max(t2.value), avg(t3.value), u.id
FROM users u
  JOIN table1 t1 ON t1.user_id = u.id
  JOIN table2 t2 ON t2.user_id = u.id
  JOIN table3 t3 ON t3.user_id = u.id
GROUP BY u.id;

Лена внимательно посмотрела на экран, и её брови поползли вверх:

— Погоди-ка... Это что, серьёзно?! — Лена ткнула пальцем в строку с actual rows=15000000. — Ты хочешь сказать, что из-за одного кривого JOIN'а мы таскаем в памяти пятнадцать миллионов строк, чтобы потом найти какой-то дурацкий максимум и потом вернуть 1000 строк?!

Макс одобрительно кивнул, скрывая улыбку — Лена начинала мыслить как настоящий performance-инженер.

— Ладно, гений, — Лена скрестила руки на груди, — и какое же твоё волшебное решение?
В её глазах читался вызов: "Ну-ка, удиви меня!"

Макс переключился на следующую вкладку, показывая переработанный запрос:

EXPLAIN ANALYZE
SELECT count(*), avg(t1.value), avg(t3.value), u.id
  (SELECT max(t2.value) FROM table2 t2 WHERE t2.user_id = u.id) -- Вот он, фокус!  
FROM users u
  JOIN table1 t1 ON t1.user_id = u.id
  JOIN table3 t3 ON t3.user_id = u.id
GROUP BY u.id;

— Видишь разницу? — его глаза загорелись азартом:
+ Время выполнения сократилось в 73 раза (с 48 сек до 0.65 сек)
+ Потребление памяти уменьшилось в 6 раз (с 5.7 Мб до 1 Мб)
Исходный запрос использовал дорогой HashAggregate с полным сканированием (Parallel Seq Scan), тогда как оптимизированный:
+ Точечно обращается к данным через индексы (Bitmap Index Scan)
+ Использует кэширование (Memoize)
+ Работает без параллелизации, но эффективнее

Лена нахмурилась:
— То есть вместо того чтобы сваливать в кучу все транзакции всех клиентов, а потом их сортировать, теперь мы просто смотрим максимальный платеж каждого клиента в подзапросе?

— Именно! — Макс щелкнул пальцами. — Первый подход — это как взять чеки у всех клиентов и потом раскладывать 15 млн чеков. Второй — заглянуть в картотеку каждого клиента, где уже есть нужная информация.

— Поняла, — Лена кивнула, — но почему оптимизатор сам не догадается до такого простого решения?

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

Лена задумалась на секунду:
— Значит, когда нужно одно значение из большой таблицы — лучше использовать подзапрос. GROUP BY с огромными JOIN'ами — плохой вариант.

— Все именно так! — Макс удовлетворенно кивнул.

— Кстати, а если нужны будут несколько агрегатов из table2? Неужели придется делать кучу подзапросов?

Макс хитро улыбнулся:
— Об этом — поговорим в другой раз. А пока предлагаю поэкспериментировать с демо-стендом, так как практика в этом деле бесценна.

#explain

PS. Полный скрипт (таблицы, генерация данных и запросы) доступен по ссылке https://pastebin.com/yu4r8sb9
Попробуйте воссоздать ситуацию у себя в тестовой базе и пройтись по всем шагам самостоятельно.

🎓 100 уроков EXPLAIN. часть 5
CTE vs LATERAL

На следующий день Лена ворвалась в кабинет Макса с горящими глазами:
— Смотри, что я придумала! Тот случай с table2, я нашла быстрый способ получить несколько агрегатов без отдельных подзапросов!

На экране ее ноутбука красовался новый вариант запроса.

EXPLAIN ANALYZE
WITH aggregated_t2 AS (
    SELECT user_id,
        max(value) AS max_val,
        avg(new_numeric_field) AS avg_num,  -- Новое поле!
        min(new_date_field) AS min_date     -- И ещё одно!
    FROM table2
    GROUP BY user_id
)
SELECT avg(t1.value), t2.max_val, t2.avg_num, t2.min_date, avg(t3.value), u.id
FROM users u 
  JOIN table1 t1 ON t1.user_id = u.id
  JOIN aggregated_t2 t2 ON t2.user_id = u.id  -- Вот где магия!
  JOIN table3 t3 ON t3.user_id = u.id
GROUP BY u.id, t2.max_val, t2.avg_num, t2.min_date;

— Видишь? — торжествующе произнесла Лена. — Вместо трёх подзапросов — одно CTE! И план показывает, что данные из table2 берутся за один проход!

Макс одобрительно кивнул, скрывая улыбку. Лена явно демонстрировала успехи.
— Ты молодец! — сказал он, — Классный результат. Кстати, есть еще и альтернативный вариант.

— Есть еще варианты?

— Да, можно использовать LATERAL JOIN. Вот смотри.

EXPLAIN ANALYZE
SELECT avg(t1.value), t2_agg.max_val, t2_agg.avg_num, t2_agg.min_date, avg(t3.value), u.id
FROM users u 
  JOIN table1 t1 ON t1.user_id = u.id
  JOIN LATERAL (
    SELECT 
        max(value) AS max_val,
        avg(new_numeric_field) AS avg_num,
        min(new_date_field) AS min_date
    FROM table2 t2
    WHERE t2.user_id = u.id
) t2_agg ON true
JOIN table3 t3 ON t3.user_id = u.id
GROUP BY u.id, t2_agg.max_val, t2_agg.avg_num, t2_agg.min_date;

— Хм и чем это отличается от моего варианта? — Нахмурилась Лена. — Ты мой подзапрос из CTE убрал за этот странный JOIN.

— Почти. Главное отличие, что теперь вместо группировки по user_id используется фильтрация по этому полю внутри подзапроса. Это очень важное преимущество lateral join - возможность обращаться к полям других таблиц запроса до соединения результатов.

— Интересный вариант. Спасибо, я запомню его. Кстати, план запроса у тебя получается компактнее, однако время выполнения немного больше моего.

— Да, потому что твой вариант с CTE читает большую таблицу линейно (Parallel Seq Scan) и дальше использует агрегацию в памяти (HashAggregate), а LATERAL хоть и использует индекс по user_id (Bitmap Heap Scan), но использует множество отдельных чтений. В этом разница между ними.

— Но все говорят, что seqScan это плохо, индексы же специально созданы для ускорения запросов!

— Часто это так, но не всегда, есть и важные исключения. Предлагаю обсудить это в следующий раз. А пока подведем итоги:

🔹 CTE лучше подходит для массовой обработки
🔹 LATERAL JOIN - для точечных запросов с фильтрацией
⚠️ Золотое правило - Всегда смотри на план выполнения!

Лена задумчиво кивнула. В её глазах разгорался огонек настоящего исследователя баз данных.

#explain #lateral

PS. новые поля добавлены в скрипт генерации https://pastebin.com/yu4r8sb9. Рекомендую выполнить шаги из этого урока самостоятельно.

⏳Как избежать хаоса в параллельной обработке

В переговорке идет обсуждение новой доработки.
Таня (тимлид):
— Эту новую обработку поручений нужно обязательно распараллелить. Без дублей. Ваши предложения?

— Предлагаю добавить в таблицу payments поле locked_by. — оживилась Катя. — И каждый процесс будет обновлять это поле вот так:

BEGIN;
  -- Шаг 1: Читаем не обработанные данные.
  SELECT id FROM payments 
  WHERE status = 'new' AND locked_by IS NULL 
  -- Шаг 2: Захватываем запись
  UPDATE payments 
    SET locked_by = 'worker_123'
    WHERE id = <выбранная запись>;
  COMMIT; -- Чтобы другие процессы увидели, что запись обрабатывается!
  -- Шаг 3: Основная обработка ...
END;

— Мне не нравится промежуточный COMMIT — нахмурился Сергей — Если процесс упадёт на третьем шаге, запись останется заблокированной. Придется следить за "зависшими" платежками.

— И как быть с конкурентным доступом? — спросила Оля — Несколько процессов могут прочитать одинаковые записи с NULL до того, как один из них ее захватит!"

— Ребята, зачем изобретать велосипед? В Postgres уже есть готовые инструменты для этого. — перехватил инициативу Макс. — Вот так это будет выглядеть:

BEGIN;
  -- Берём  нужное число незаблокированных записей
  SELECT * FROM payments
  WHERE status = 'new'
  ORDER BY created_at  -- если нам важно для FIFO
  LIMIT 10
  FOR UPDATE SKIP LOCKED;  -- Магия здесь!

  -- Обрабатываем...
  COMMIT;  -- Записи автоматически разблокируются при завершении транзакции
END;

— И никаких флагов? — удивилась Катя. — Но как это работает?

— Тут простая магия — ответил Макс:

1. FOR UPDATE сразу блокирует прочитанные строки.
2. SKIP LOCKED пропускает уже заблокированные записи (они просто не попадут в результаты следующих запросов, пока действует блокировка)
3. LIMIT определяет количество блокируемых записей для каждого воркера.

— Чтение записей и блокировка выполняются одной командой и в одной транзакции, этим обеспечивается конкурентный доступ. К тому же нет риска "зависших" записей — если процесс упадёт, транзакция откатится автоматически и записи будут разблокированы. — подвел итог Макс.

— Хорошо! — похвалила Таня. — Есть какие-то особенности или ограничения, которые следует учесть?

— Конечно, они есть всегда — откликнулся Макс и начал загибать пальцы:
1. Нужно помнить, что SKIP LOCKED реально пропускает данные, т.е. это хороший инструмент для обработки событий, но совершенно не годится для анализа данных.
2. Этот подход не работает с некоторыми типами JOIN`ов и оконными функциями. При этом в сложных запросах можно явно указать блокируемую таблицу.
3. Если в обработке будут блокироваться другие таблицы в разном порядке - могут быть проблемы, вплоть до deadlock'ов. Но это скорее общая проблема параллельной обработки.

— Подожди, Макс. — задумалась Оля. — А что если обработка платежа занимает не 5 секунд, а 5 часов? Или если нужен ручной контроль менеджера?

— Да, для долгих операций SKIP LOCKED - не лучший выбор. — кивнул Макс. — Тогда лучше через поле locked_by. Для надежности тогда добавить поле locked_until, которое обновлять Heartbeat-механизмом. Это позволит отслеживать зависшие задачи.

— Не отвлекайтесь. — остановила их Таня. — у нас обработка каждого платежа будет очень быстрой, так что решено: используем FOR UPDATE SKIP LOCKED, быстро проверяем на тестовом стенде и идем в продакшн. Главное — ничего не сломать. Макс, проверка на тебе. Переходим к следующему пункту.

#best_practice #блокировки

🎓 100 уроков EXPLAIN. Часть 6
Как оптимизатор принимает решения

— Макс, ты уже говорил, что в некоторых случаях Seq Scan быстрее чем Index Scan! — напомнила Лена на следующий день. — Но везде пишут, что нужно избавляться от Seq Scan. Так как понять, когда Seq Scan хорошо, а когда плохо?

— А давай разберёмся, как Postgres вообще принимает такие решения. — отодвинул кружку с кофе Макс. — В этом нет никакой магии, обычная математика.

1. Как работает оптимизатор
Postgres оценивает планы через стоимость и статистику:

🔸 Стоимость: 1 cost = чтение 1 блока с диска - самая дорогая операция. Обработка в памяти в десятки и сотни тысяч раз быстрее
🔸 Статистика: для таблиц это прежде всего количество строк и информация о том, как распределены данные по столбцам:

-- Посмотрим статистику для таблицы orders:
SELECT relpages, reltuples FROM pg_class WHERE relname = 'orders';

→ relpages = 100 (блоков), reltuples = 10000 (строк)

2. Почему Seq Scan в ряде случаев быстрее
Пример запроса:

SELECT * FROM orders WHERE status = 'completed';  -- Допустим, 80% от всех строк

Seq Scan:
- Читает все 100 блоков (cost = 100).
- Фильтрует строки в памяти (быстро).

Index Scan:
1. Читает индекс (допустим, 20 блоков, cost = 20).
2. Для каждой найденной индексом строки:
- Читает блок таблицы (80% строк = 8000 обращений × 1 cost ≈ 8000).
- Итого: cost ≈ 8020!

Вывод: Seq Scan (100) «дешевле», чем Index Scan (8020).
Конечно в процессе такого выполнения почти все блоки таблицы окажутся в кэше в памяти, и операция не будет реально эквивалентна чтению 8000 блоков. Но это уже отдельная тема.

— Действительно все просто и логично, — отметила Лена. — Выбирается вариант с наименьшими затратами.

— Да. Оптимизатор учитывает еще множество разных деталей. Например, разницу между последовательным и случайным чтением, корреляцию индекса и т.д.

— А что это такое "корреляция индекса"? — заинтересовалась Лена.

— Это такая статистика, которая показывает, насколько физический порядок строк в таблице соответствует реальному порядку в данных. Это важно, когда есть индекс и выборка идет по диапазону (BETWEEN). Может иметь значения от -1 до 1:
🔸 1.0 — идеальная кластеризация (строки в таблице лежат в логическом порядке).
🔸 0.0 — нет корреляции.
🔸 -1.0 — обратная кластеризация.

-- Посмотреть correlation для столбца:
SELECT tablename, attname, correlation FROM pg_stats 
WHERE tablename = 'orders' AND attname = 'created_at';

Эта информация используется для оценки стоимости Index Scan:
🔸 Если correlation ближе к 1.0, оптимизатор понимает, что Index Scan будет читать данные последовательно;
🔸 Если correlation ближе к 0.0, то Index Scan потребует много случайных чтений, что повышает cost.

Конечно, на современных SSD разница между последовательным и случайным чтением не такая большая, как на обычных дисках, но она все же есть.

— Кстати, есть возможность повлиять на этот параметр. — добавил Макс, пока Лена переваривала информацию. — Для этого есть команда CLUSTER. Но надо понимать, что выровняв данные по одному столбцу, корреляция по другим может сильно ухудшиться. Это имеет смысл только для редких случаев. К тому же эта команда блокирует таблицу, так что на проде такие эксперименты под запретом.

— Поняла! — Лена сделала заметку в блокноте. — Значит, главное не слепо гнаться за индексами, а понимать логику оптимизатора.

— Хорошо сказано! — Макс одобрительно кивнул. — Для закрепления предлагаю тебе поизучать статистику для известных тебе таблиц. В pg_stats еще много информации. Готовь вопросы - обсудим.

#explain #статистика

🎓 100 уроков EXPLAIN. Часть 7
Index Only Scan, и что показывают “heap fetches”

Утро. Лена зашла в кабинет Макса с блокнотом:
— Макс, привет! А что такое «сканирование только индекса»? Я видела в плане узел Index Only Scan. Расскажи, как он работает?

Макс повернулся к Лене:
— Доброе утро! Твое упорство и интерес меня восхищают.

— Ну мне правда интересно. — смутилась Лена. — Ну а все же, что это?

— Представь индекс, который содержит в себе все поля, которые нужны для запроса. Такой индекс называется покрывающим. Когда он есть, Postgres может вернуть данные прямо из индекса, а не по идентификаторам версий строк из таблицы.

Он набросал простой пример:

EXPLAIN ANALYZE
SELECT login FROM users 
WHERE login LIKE 'z%';

План запроса:

Index Only Scan using users_login_uindex on users  (cost=0.28..156.60 rows=1 width=10) (actual time=2.690..2.709 rows=17 loops=1)
  Filter: ((login)::text ~~ 'z%'::text)
  Rows Removed by Filter: 5961
  Heap Fetches: 469
Planning Time: 0.144 ms
Execution Time: 2.745 ms

— Кажется классно, — кивнула Лена. — Получается таблица в таком случае вообще не нужна, достаточно только индекса?

— А вот это, Лена, самый коварный миф про Index Only Scan. Индекс хранит не всё. Обрати внимание на строку Heap Fetches.

— Вижу там цифры, а что они значат?

— Дело в том, что сам индекс не хранит информацию о видимости строк. Есть специальная карта видимости, куда VACUUM помечает «чистые» страницы — те, где все версии строк таблицы видны всем транзакциям. Если страница помечена, Postgres пропускает проверку в heap и сразу отдаёт данные из индекса. Если же пометка не стоит — он идёт в таблицу, проверяет видимость и увеличивает счётчик Heap Fetches.

— Ничего не понятно. — Честно призналась Лена.

— Смотри, таблица - это книга, индекс - глоссарий. Но в этой книге постоянно ведутся правки - кто-то стирает строки, кто-то дописывает новые и оставляет об этом отметки. Сам глоссарий содержит только слово для поиска и номера страниц, где оно встречалось. Но он сам не знает, зачистили ли эти страницы от пометок «старое» или «удалено». — Макс выжидающе смотрел на Лену.

— Продолжай.

— Чтобы не перелистывать каждую страницу (то есть не заглядывать в основную таблицу), Postgres ведёт отдельный список — «карта видимости». В этой карте отмечены страницы, которые точно чистые и в порядке.
🔸 Если страница в карте помечена как «чистая», то сразу берём данные из индекса и не ходим в таблицу.
🔸 Если страницы там нет, значит нужно проверить: заглянуть в таблицу и убедиться, что строка «видна» (это и считается одной «вынужденной проверкой» — Heap Fetch).

Чем чаще делается VACUUM, тем больше страниц помечено чистыми, и тем реже Postgres будет лишний раз заглядывать в таблицу. — подвел итог Макс.

— То есть, чтобы Heap Fetches ушли в ноль, нужен покрывающий индекс и актуальная карта видимости, правильно?

— Именно. В Postgres есть статистика, по которой это можно отслеживать - это поля relpages (всего страниц) и relallvisible (сколько видимых) в pg_class. Можешь потренироваться (строго на тесте) - засечь эти значения для какой-нибудь таблицы, затем выполнить VACUUM и посмотреть как они изменятся.

#explain #индексы

🚀 Неполный refresh Materialized View

— Вась, ты чего такой хмурый? — спросил Макс, заметив коллегу у кофе-поинта.

— Да вот, дали задачу, — вздохнул Вася. — Есть матвью, которая рефрешится почти полтора часа. А бизнес требует обновлять её каждые 10 минут, потому что, — он противным голосом передразнил Евгения Вадимовича, — «нам нужны максимально оперативные данные». А там данные по проводкам за 5 лет, оптимизировать уже некуда.

— Классика, — кивнул Макс. — Сделай им отдельную матвью и отдельный отчет на оперативном периоде.

— Если бы так просто. Требуют, чтобы данные были все в одном месте.

— Ну так и сделай им «одно место», но из двух частей, — не сдавался Макс. — Раздели одну большую матвью на две: историческую, где данные уже не меняются, и оперативную, за последнюю неделю. Историю обновляй раз в сутки, а оперативку — хоть каждую минуту.

— Это мысль! — оживился Вася. — А основную матвью заменить на VIEW, которая их через UNION ALL объединяет! Но как избежать дублей и дыр в данных на стыке?

— Да, нужен буфер, — согласился Макс, подходя к доске. — Сделай матвью с небольшим пересечением по датам, чтобы застраховаться.

-- 1. Исторические данные
-- Обновляется редко (раз в сутки)
CREATE MATERIALIZED VIEW mv_historical AS
SELECT * FROM source_transactions
WHERE operation_date < (CURRENT_DATE - INTERVAL '7 days');

-- 2. Оперативные данные (с буфером в 1 день)
-- Обновляется часто
CREATE MATERIALIZED VIEW mv_current AS
SELECT * FROM source_transactions
WHERE operation_date >= (CURRENT_DATE - INTERVAL '8 days');

— Отлично, с матвью разобрались. А как теперь их правильно "склеить" во VIEW, чтобы не было дублей из-за буферного дня? — спросил Вася. — UNION без ALL не подойдет, т.к. объем данных огромный, производительность просядет сразу.

— А вот тут у нас есть два пути, — Макс нарисовал на доске две стрелки. — Выбирай любой.

— Первый способ — самый очевидный, — начал Макс. — Мы просто жёстко задаём границу во VIEW с помощью CURRENT_DATE.

CREATE OR REPLACE VIEW v_full AS
-- Берем все из оперативной части
SELECT * FROM mv_current
WHERE operation_date >= CURRENT_DATE - INTERVAL '8 days'; -- исключаем буферные данные
UNION ALL
-- И добавляем историю, отсекая по той же границе
SELECT * FROM mv_historical
WHERE operation_date < (CURRENT_DATE - INTERVAL '8 days');

— Выглядит просто, — кивнул Вася. — И должно быть быстро, планировщик легко поймет такое условие. А почему исключаем буферные данные из обеих матвью? тут нет ошибки?

— Тут есть нюанс — улыбнулся Макс. — Если наступит новый день, а ни одна из матвью не успеет обновиться, тогда будет дырка в данных. Поэтому такое хитрое условие.

— Есть еще и второй путь, — продолжил Макс. — Границу отсечения брать из самих данных.

CREATE OR REPLACE VIEW v_full_safe AS
-- Берем все из оперативной части
SELECT * FROM mv_current
UNION ALL
-- А историю отсекаем по МИНИМАЛЬНОЙ дате из оперативных данных
SELECT * FROM mv_historical h
WHERE h.operation_date < (SELECT min(t.operation_date) FROM mv_current t);

— Понял! — воскликнул Вася. — Граница определяется данными из оперативной матвью. Но... этот подзапрос в WHERE... Он же может свести с ума планировщик на сложных отчётах, верно?

— В точку, — одобрительно кивнул Макс. — Этот подзапрос может помешать оптимизатору правильно оценить объём данных и построить эффективный план.

Вася задумчиво посмотрел на доску.
— Так что же выбрать? Какой способ лучше?

— А это тебе и предстоит решить, — улыбнулся Макс. — Создай обе версии VIEW и протестируй на реальных отчётах с помощью EXPLAIN ANALYZE. Сравни планы и время выполнения. У тебя на руках будут цифры, а с ними — и осознанное решение. Нет идеальных рецептов, есть только подходящие для конкретной задачи.

#кейс #матвью

🎓 100 уроков EXPLAIN. Часть 8
Битва JOIN'ов — Hash vs Nested Loop vs Merge

Лена вошла в кабинет Макса с видом детектива, который почти раскрыл дело, но наткнулся на последнюю загадку.

— Макс, я снова с EXPLAIN'ом. Смотри, какая странность! — она развернула свой ноутбук. — Вот запрос с JOIN, он летает. Я написала почти такой же, но поменяла одно условие в ON — и он стал в 10 раз медленнее! План совершенно другой.

Макс отхлебнул утренний кофе и взглянул на экран. На одном плане красовался знакомый Hash Join, на другом — какой-то Nested Loop Join.

— Поздравляю, — усмехнулся он. — Ты только что столкнулась с большой тройкой стратегий соединения таблиц. Postgres не просто выполняет JOIN, он сначала выбирает, как именно его выполнить. А твое условие и статистика — ключ к его выбору.

Макс взял маркер, подошел к доске и начал рисовать.

1. Nested Loop Join
— Это самый простой и «тупой» способ. Postgres берёт первую запись из внешней таблицы и начинает просматривать всю внутреннюю таблицу в поисках совпадений. Потом берёт вторую запись и так далее.
— Когда хорош: Когда внутренняя таблица очень маленькая (например, 5 строк). Тогда «пробежаться» по ней недолго. Или когда для поиска во внутренней таблице есть очень селективный индекс.
— Когда плох: На больших таблицах это катастрофа. Две таблицы по 100 тысяч записей дадут 10 миллиардов итераций.

2. Hash Join
— Это твой старый знакомый. Postgres сначала берёт меньшую из двух таблиц и строит по ней в памяти «справочник» — хеш-таблицу. А потом просто прогоняет по этому справочнику вторую таблицу, мгновенно находя совпадения. Как в том мультфильме - день потерять, потом за 5 минут долететь. Для 5 записей строить хеш-таблицу невыгодно, а для тысяч - очень даже.
— Когда хорош: Когда таблицы большие и соединяются по условию равенства (=). Это самый частый и эффективный способ.
— Ограничение: Работает только для равенства. Для условий вроде > или LIKE он бесполезен.

3. Merge Join
— А это самый хитрый способ. Если обе таблицы отсортированы по ключу соединения, Postgres может соединить их за один проход, просто идя по обоим спискам одновременно, как застёжка-молния.
— Когда хорош: Когда соединяются большие, уже отсортированные таблицы (например, по выходу из Index Scan).
— Ограничение: Требует сортировки. Если данные не отсортированы, то узел Sort может съесть всю выгоду.

Лена хлопнула себя по лбу:
— Точно! В быстром запросе у меня было t1.user_id = t2.user_id, и он выбрал Hash Join. А в медленном — t1.value > t2.limit, и для этого неравенства Hash Join не подошел. Ему пришлось использовать медленный Nested Loop!

Макс кивнул:

— Это закономерно: для неравенства Hash Join не работает. Но вот где главное — Nested Loop не всегда беда. Проблема часто не в самом JOIN, а в том, что планировщик не угадал, сколько реально вернёт строк и выбрал неподходящий алгоритм.

— Да? — удивилась Лена.

— Вот смотри: если в плане у Nested Loop написано rows=2, а на деле actual rows=1500 — это тревожный признак. Планировщик думает, что с каждой внешней строки получит всего пару совпадений, а реально их гораздо больше. Потому и делает медленный вложенный цикл. Чаще всего причина такого - устаревшая или неточная статистика.

— А как понять, что дело именно в статистике?

— По разнице между ожидаемым (rows) и реальным (actual rows). Если actual rows сильно больше ожидаемого, значит пора запускать ANALYZE — статистика не отражает реальную картину. Планировщик ошибается в выборе стратегии, думая, что оптимальный путь — Nested Loop.

— Получается, не всегда условие виновато? Иногда плохой план — это просто сбившаяся статистика?

— Да. статистика очень важна для выбора правильного алгоритма соединения. Это как информация о длине улиц и пробках для навигатора.

— Поняла. Теперь буду внимательнее к статистике. На всякий случай пробегусь по таблицам с ANALYZE!

Макс улыбнулся:
— Обычно база сама заботится о статистике через autovacuum. Руками ANALYZE нужен только при массовых изменениях или явных проблемах с производительностью.

#explain

⚖️ Искусство «разбора полётов»
Как найти причины и не найти виноватых

Утро понедельника в офисе было тихим и напряжённым. Пятничный пожар потушили, сервис авторизации, лежавший почти час, подняли, но осадок остался.

— Ладно, проехали, — сказала Таня, тимлид, на утреннем стендапе. — Главное, что всё работает. Не будем тратить время, у нас куча задач на спринт.

Сергей, разработчик, чей коммит был последним перед падением, нервно кивнул, стараясь не встречаться ни с кем взглядом.

— Минутку, — вмешался Макс, спокойно отпивая кофе. — Я думаю, время как раз потратить стоит. Предлагаю в 11 собраться в переговорке на полчаса. Проведём post-mortem.

В воздухе повисло неловкое молчание. Слово «post-mortem» прозвучало как «вызов на допрос».

— Макс, да что там разбирать? — не выдержала Таня. — Сергей ошибся, мы поправили. Все устали, не надо никого тыкать носом.

— А я и не собираюсь, — ответил Макс. — Моя цель — не найти виноватого, а понять, почему наша система позволила этой ошибке случиться.

В 11:00 в переговорке было тихо, как в библиотеке. Сергей сидел, скрестив руки, готовый к обороне.

— Итак, — начал Макс и подошёл к доске. — Прежде чем мы начнём, одно правило.

Он указал на доску, где крупными буквами было написано:
❗️Мы все верим, что каждый из нас действовал из лучших побуждений, исходя из той информации, которая у него была. Сломалась система, а не человек

— Цель этой встречи, — продолжил он, — сделать нашу систему сильнее. Давайте разберём не «кто», а «почему». Итак, что случилось?

— Сервис упал из-за NullPointerException, — буркнул Сергей. — В поле userProfile пришёл NULL, а код этого не ожидал.

— Окей, это триггер. А теперь давайте раскрутим цепочку, — Макс нарисовал первую стрелку. — Почему в коде не было проверки на NULL?

— Я был уверен, что профиль есть всегда, — ответил Сергей. — В требованиях не было указано, что он может отсутствовать. Меня торопили с задачей, я не стал добавлять избыточных проверок.

— Понятно, — сказал Макс. — Это не ошибка, это фактор №1: неполные требования и спешка.

Он нарисовал вторую стрелку. — Почему тесты пропустили такой сценарий?

— У нас в тестовых данных у всех пользователей есть профили, — вступила Оля, тестировщик. — Скрипт генерации тестовых данных старый, он не покрывает этот случай.

— Фактор №2: устаревшая тестовая среда. Записал. Идём дальше. Почему мы узнали о проблеме от пользователей, а не от мониторинга?

— Алерт сработал, но уже когда сервис перестал отвечать на health-чеки, — ответил Макс сам себе. — У нас нет алерта на конкретную ошибку в логах. Фактор №3: неэффективный мониторинг.

Напряжение в комнате начало спадать. Стало очевидно, что дело не только в строчке кода Сергея.

— И последний вопрос, — сказал Макс. — Почему мы выкатывали критичное изменение в сервис авторизации в пятницу в 15:00?

Таня вздохнула.
— Потому что маркетинг обещал клиентам запуск акции на выходных. Было давление сверху.

— Фактор №4: нарушение внутренних процессов под давлением бизнеса.

Макс обвёл все четыре пункта на доске.
— Смотрите. Падение — это не вина Сергея. Это результат совпадения четырёх факторов. Любой из них мог бы его предотвратить. Наша задача — исправить систему, чтобы эта цепочка больше не собралась.

Дальше встреча пошла конструктивно. За 15 минут команда накидала конкретные задачи:

💻 Код: Добавить проверку на NULL в сервис. отв.: Сергей.

🧪 Тесты: Обновить скрипт генерации тестовых данных. отв.: Оля.

📈 Мониторинг: Настроить алерт на NullPointerException в логах сервиса. отв.: Макс.

📋 Процессы: Внести в регламент обновлений раздел "Критичные сервисы" и правило, запрещающее релизы критичных сервисов в нерегламентное время. отв.: Таня.

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

Сергей подошёл к Максу.
— Спасибо. Я думал, меня сейчас будут распинать.

— Запомни, — ответил Макс, — хороший инженер не тот, кто не ошибается, а тот, кто делает выводы из ошибок, чтобы система стала надёжнее. Сегодня мы все стали немного лучшими инженерами.

#процессы

NOT EXISTS vs NOT IN vs анти-LEFT JOIN

— Запрос тормозит, — выдохнул Алекс, — Использую NOT IN, чтобы отсеять ненужные записи.

— Попробуй NOT EXISTS. Все знают, он быстрее. — посоветовала Ольга, сидевшая напротив.

Макс, проходивший мимо, остановился с улыбкой:
— А что, если я скажу вам, что это — главный миф PostgreSQL, а настоящая ловушка скрыта в вашем NOT IN?

Он опёрся о стол.
— Давайте поиграем в детективов. Подозреваемый №1: миф, что NOT EXISTS быстрее, чем LEFT JOIN + IS NULL. Когда-то так и было, но современный PostgreSQL умнее. Ему важна не форма, а суть: найти строки без пары в другой таблице. Для него оба варианта — это команда: "выполни анти-соединение".

Алекс набросал два варианта своего запроса и запустил EXPLAIN для обоих. На его лице проступило удивление. «Планы… идентичны. Hash Anti Join в обоих случаях».

— Именно, — подтвердил Макс. — Миф разрушен. Поэтому NOT EXISTS или LEFT JOIN + IS NULL — это вопрос стиля, а не скорости.

— А теперь — к настоящему виновнику, — голос Макса стал серьезнее. — Ваш NOT IN. Он коварен. Что будет, если в таблице, по которой вы исключаете записи появится хотя бы один NULL?

—Запрос его проигнорирует? — предположил Алекс.

— Хуже, — ответил Макс. — Логика SQL превращает условие id NOT IN (1, 2, NULL) в id <> 1 AND id <> 2 AND id <> NULL. А любое сравнение с NULL дает результат UNKNOWN. И строки, для которых условие UNKNOWN, в выборку не попадают. Ваш запрос не сломается. Он молча вернет ноль строк. Это мина замедленного действия.

Алекс откинулся на спинку стула и медленно выдохнул.
— Вот это да. Я лет десять писал NOT IN, уверенный, что это нормально. И всегда ругал LEFT JOIN за громоздкость.

— Идея не в том, чтобы запомнить, что «NOT EXISTS и LEFT JOIN — молодцы, а NOT IN — плохой». — Улыбнулся Макс. — Главное — понять, почему. И всегда спрашивать: «А что, если здесь будут NULL?». И, конечно, проверять с помощью EXPLAIN.

«Выбирайте, что вам ближе: NOT EXISTS или LEFT JOIN», — подытожил Макс. — «Они надежны. А NOT IN оставьте для случаев, когда вы готовы поклясться на своей любимой кружке, что в данных никогда не будет NULL. Но мы-то с вами знаем: в данных нельзя быть уверенным ни в чём».

#explain #МифыSQL

🎓 100 уроков EXPLAIN. Часть 10
Загадочный Bitmap Scan

Лена вошла в кабинет Макса, но на этот раз без паники в глазах. На её лице было скорее любопытство исследователя, наткнувшегося на неопознанный артефакт.

— Макс, я тут смотрела один из наших старых запросов. Он работает быстро, но план меня смутил. Там написано Bitmap Heap Scan, а под ним — Bitmap Index Scan. Это как вообще? Он что, не смог выбрать между индексом и полной проверкой таблицы и решил сделать и то, и другое?

Макс отложил клавиатуру и улыбнулся.
— Ты наткнулась на самый элегантный компромисс, который придумали Postgres. Это не нерешительность, это стратегия. Помнишь наши аналогии про трактор (Seq Scan) и спорткар (Index Scan)?

Лена кивнула.

— Так вот, представь, что тебе нужно собрать урожай не со всего поля, а, скажем, с 30% грядок, которые разбросаны по всей территории.

Трактор (Seq Scan) будет обрабатывать всё поле целиком. Долго.

Спорткар (Index Scan) будет мотаться к каждой грядке отдельно. Тысячи поездок туда-сюда убьют время и бензин.

— И что делать? — Лена прищурилась.

— А теперь представь, что мы сначала запускаем дрон. Он пролетает над полем, находит все нужные грядки и составляет их точную карту. А уже потом по этой карте мы отправляем трактор, который едет по оптимальному маршруту от точки к точке, собирая урожай.

Bitmap Scan работает именно так:

Bitmap Index Scan (Дрон): Postgres бежит по индексу и не дёргает таблицу. Вместо этого он создаёт в памяти «карту» — битовую маску страниц, на которых есть нужные нам строки.

Bitmap Heap Scan (Трактор с картой): Затем он берёт эту карту и последовательно, по порядку, читает только нужные страницы из таблицы. Это гораздо быстрее случайных «прыжков» спорткара.

— То есть, если строк не слишком мало для спорткара, но и не так много, чтобы гонять трактор по всему полю, Postgres вызывает дрон? — в глазах Лены загорелся огонёк понимания.

— Бинго! — подтвердил Макс. — Это золотая середина. И знаешь, что самое крутое? Дрон может облететь поле по нескольким картам от разных агрономов. Bitmap Scan умеет комбинировать результаты нескольких индексов для одного сканирования. Но это уже совсем другая история.

📌 Что запомнить:
🔸 Bitmap Scan — это двухэтапный процесс: сначала создаём карту по индексу, потом читаем по ней таблицу.
🔸 Это гибрид: эффективнее, чем Seq Scan (читаем не всё) и быстрее, чем Index Scan (избегаем хаотичных чтений с диска).
🔸 Идеален для запросов, которые возвращают «среднее» количество строк — уже слишком много для точечного поиска, но ещё слишком мало для полного сканирования.

#explain #индексы

🎓 100 уроков EXPLAIN. Часть 11
Загадочный Recheck Cond — доверять или проверять?

— Макс, я опять с планом, и он меня смущает, — Лена сдвинула на экране окно с результатами EXPLAIN. — Вот смотри: Bitmap Heap Scan, а под ним Recheck Cond. Выглядит так, будто Postgres сначала нашел что-то по индексу, а потом усомнился в себе: «А вдруг я ошибся? Дай-ка перепроверю». И тут в плане еще Heap Blocks: exact=502 lossy=1621. Как на это реагировать? Просто махнуть рукой и довериться оптимизатору?

Макс усмехнулся.
— Ты задала один из самых тонких вопросов. Это не недоверие, а наоборот — признак умной и хорошо продуманной подстраховки.

— Подстраховки от чего?

— От неидеальности мира, — Макс сделал глоток кофе. — Помнишь наш дрон, который составлял карту урожая (Bitmap Index Scan)? Представь, что поле огромное, а память под карту (work_mem) у нас ограничена. Дрон не может отметить на карте каждый отдельный куст.

Он сделал паузу, давая Лене время представить картину.

— Когда ему не хватает work_mem, он обводит на карте целые сектора (блоки), где точно есть нужные нам кусты. Но в этих секторах могут расти и другие растения. Такая карта называется неточной, или lossy. И вот когда наш трактор (Bitmap Heap Scan) приезжает в отмеченный сектор, он вынужден перепроверять (Recheck Cond) каждый куст, чтобы собрать только нужные.

— Ааа, то есть это плата за то, что у нас была неточная карта? — осенило Лену.

— Именно! Это не провал оптимизатора, а его штатный план «Б». Он сэкономил память на карте, но заплатил за это дополнительной проверкой на месте. В плане он это выделяет:
▪️ Heap Blocks: exact — это страницы, для которых карта содержит точную информацию о размещении каждой подходящей строки.
▪️ Heap Blocks: lossy — это “грубые” сектора карты. В памяти не хватило места, чтобы обозначить каждую строку, и Postgres вынужден помечать целую страницу целиком: “Может, здесь есть что-то подходящее — перепроверь вручную”.

— И что мне с этим делать?

— Вот тут самое главное. Не паниковать, а смотреть в EXPLAIN ANALYZE на строку Rows Removed by Index Recheck. Если там маленькое число, значит, всё отлично. Карта была почти точной. А вот если там тысячи отброшенных строк, это сигнал: наш трактор тратит слишком много времени на добычу и проверку «мусора».

— Поняла. Но у меня там как раз очень большое число! Почему?

— Ну это может означать, что индекс не очень хорошо подходит для этого запроса, и карта получается слишком «грязной». А может это сигнал, что в таблице накопились “мертвые” строки, удаленные или обновлённые, но ещё не убранные автовакуумом. Если страница полна мусора, точность битмапы падает: Postgres “не видит” какие строки актуальны, и приходится читать лишнее.

📌 Что запомнить:
🔸 Recheck Cond — это не ошибка, а плановая перепроверка для неточных (lossy) битовых карт.
🔸 Много lossy-блоков — нехватка памяти для построения точной карты (work_mem).
🔸 Чрезмерный Rows Removed by Index Recheck — повод проверить селективность индекса, а также проверить работает ли autovacuum, не растет ли таблица быстрее, чем Postgres успевает её подчищать?

#PostgreSQLДляНачинающих #EXPLAIN

PS: В комментариях есть пример, как получить план с Bitmap Index Scan. Добавил пример - как воспроизвести его с Recheck Cond и как повлиять на него.

#explain #индексы

🎯 Индекс для избранных

Лена, ведущий аналитик, уже час смотрела в монитор, как в бездну.

— Макс, это парадокс, — сказала она, когда Макс подошёл с неизменной кружкой кофе. — Я построила индекс, чтобы ускорить поиск. А он замедлил всё. Вставку. Обновление. И даже сам поиск.

Макс молча взглянул на её экран. Таблица operations, 500 миллионов строк. Индекс по полю status, занимающий 25 гигабайт.

— Какой статус ты ищешь? — спросил он, хотя уже знал ответ.
— active, — выдохнула Лена. — Но их там доли процента. Остальные 99.9% — это completed.

Макс кивнул. Он сделал глоток кофе и посмотрел на Лену. Не как на коллегу, а как на соучастника фундаментального заблуждения.

— Ты ищешь иголку в стоге сена. Тогда зачем ты индексируешь сено?

Лена моргнула. Вопрос был настолько простым, что казался абсурдным.
— Но… индекс работает по всей колонке. Так устроены базы данных.

— Так устроен мир по умолчанию, — поправил Макс. — Мир, который пытается быть справедливым ко всем и в итоге неэффективен ни для кого. Твой индекс — это огромная, подробная карта города, где отмечен каждый дом. Но ты каждый день ходишь только в одно здание. Зачем тебе карта всего города? Тебе нужен прямой, короткий путь к единственной нужной двери.

Он придвинул её клавиатуру. Пальцы легко пробежали по клавишам.
— Мы не будем составлять карту для всех. Мы создадим её только для избранных.

CREATE INDEX idx_operations_only_active
ON operations (status)
WHERE status = 'active';

— Этот индекс, — Макс показал на экран, — проигнорирует 499 миллионов твоих completed-записей. Он будет существовать только для тех немногих, кто действительно важен прямо сейчас. Он будет крошечным. Мгновенным. И не будет мешать остальным.

Он нажал Enter. После того как команда выполнилась Макс бросил взгляд на статистику:
- Старый индекс:25 Гб 🔴
- Новый индекс: 100 Мб 🟢

— Обнови свой дашборд, — тихо сказал Макс.

Лена нажала F5. Графики, которые раньше вырисовывались несколько секунд, появились раньше, чем она успела убрать палец с клавиши.
Она молчала.

Макс допил кофе и улыбнулся.
— Иногда лучшее решение — не общее, а специализированное. Перестань строить дороги для всех. Проложи одну идеальную тропу туда, куда действительно часто ходишь.

#best_practice #индексы

🎓 100 уроков EXPLAIN. Часть 12
Index Cond vs. Filter: битва за эффективность

— Макс, я сломала мозг, — подошла Лена. — Смотри: у меня есть составной индекс по (status, created_at). Я делаю запрос с условиями по обоим этим полям. Но в плане запроса поле status попадает в Index Cond, а created_at — в Filter. Почему он не использует индекс целиком? Это же неэффективно!

Макс отвлекся от своего монитора.
— Поздравляю. Ты только что нашла «серую зону» оптимизации, где запрос вроде бы использует индекс, но не на все сто. Это как купить швейцарский нож и использовать его только для открывания бутылок.

— То есть Filter — это плохо?

— Ну не то чтобы это плохо, но это важный сигнал. Давай на аналогии. Представь, что индекс — это картотека в библиотеке.
🔸 Index Cond (условие индекса): Ты подходишь к ящику с буквой «Т», потому что ищешь Толстого. Это мгновенный поиск. Ты сразу отсекаешь огромную часть книг.
🔸 Filter (фильтр): А теперь ты достаешь всю огромную пачку карточек на «Толстой» и начинаешь вручную перебирать их в поисках той, где год издания равен заданному. Это уже дополнительная работа. Это все еще быстрее, чем обыскивать всю библиотеку, но медленнее, чем если бы у тебя был отдельный ящик для «Толстой, 1900».

Лена кивнула:
— Да это я понимаю. Index Cond — это навигация, Filter — это перебор. Но почему created_at ушло в Filter, если оно входит в мой индекс?

Макс развернул её ноутбук.
— Покажи запрос.

SELECT *
FROM orders
WHERE status = 'processed'
  AND date_trunc('month', created_at) = '2025-08-01';

— Вот! — воскликнул он. — Ты ищешь не по created_at, а по функции от него. Индекс хранит точные значения created_at, но ничего не знает о результате date_trunc. Поэтому Postgres делает так:
1. Index Cond: status = 'processed': Быстро находит по индексу все строки с нужным статусом.
2. Filter: date_trunc('month', created_at) = ...: Для каждой найденной строки он вычисляет функцию и проверяет, подходит ли она под условие.

📌 Когда ещё условие попадает в Filter?
🔸 Неявное приведение типов: Если у тебя поле phone текстовое, а ты ищешь WHERE phone = 79991234567 (без кавычек), Postgres будет вынужден приводить тип для каждой строки, и индекс не сработает для прямого поиска.
🔸 Поле в INCLUDE индекса: Если ты создала индекс ... ON t(a) INCLUDE (b), то поле b не является частью ключа поиска. Условие WHERE b = 5 уйдет в Filter.
🔸 Не «левая» часть индекса: Если индекс создан по (city, street), а ты ищешь WHERE street = 'Ленина', планировщик не сможет «перепрыгнуть» через city. Он будет сканировать весь индекс и применять Filter.
🔸 Частая ловушка — когда условия включаются через OR или NOT. Индекс использует только то, что соответствует структуре (обычно первое поле в ключе), а всё остальное фильтруется вручную.

— Так что же, я зря создавала составной индекс? — расстроилась Лена.

— Нет! Ты все еще можешь его использовать эффективно. Например, перепиши условие по created_at без использования функции:

SELECT *
FROM orders
WHERE status = 'processed'
  AND created_at BETWEEN '2025-07-01'::date AND ('2025-07-01'::date + interval '1 month') ;

— Если же нужно использовать именно условие по функции, то можно создать подходящий индекс:

CREATE INDEX ON orders (status, date_trunc('month', created_at));

С таким индексом оба твоих условия попадут в Index Cond, и Filter исчезнет.

📌 Что запомнить
🔸 Index Cond: Прямое использование индекса для навигации. Это высшая лига.
🔸 Filter: Дополнительная проверка строк, уже полученных после сканирования по Index Cond.
🔸 Появление Filter — это не катастрофа, а подсказка: возможно, твой индекс или запрос можно улучшить.

#explain #индексы

🔪 Хирург, а не мясник: Искусство последнего взгляда

Стажер Коля сидел перед монитором, как сапёр перед бомбой. Задача была простой: удалить тестовые записи, созданные до 1 сентября. В его редакторе горели два слова, от которых у любого инженера холодеет в груди: DELETE FROM.

Он перепроверил условие WHERE трижды. created_at < '2025-09-01' и status = 'test'. Логично. Безопасно. Но палец замер над клавишей Enter. А что, если?.. Что, если в полночь системное время сбоило? Или какой-то старый импорт пометил реальные заказы как тестовые?

В этот момент рядом бесшумно возник Макс с кружкой кофе. Он не заглядывал в монитор. Он посмотрел на Колю.

— Ты сейчас похож на человека, который собирается произнести необратимую фразу, — тихо сказал Макс. — Фразу, после которой мир уже не будет прежним.

Коля нервно усмехнулся:
— Да просто старые данные чищу. Но чувство именно такое.

Макс кивнул, и в его взгляде не было ни капли осуждения. Только полное, тотальное понимание.
— Все мы держим в руках скальпель. Каждый день. И грань между хирургом и мясником — не в умении резать. Она — в искусстве последнего взгляда.

Он мягко подвинул к себе клавиатуру.

— Мы не пишем команды. Мы ведём диалог с системой, в которой живут судьбы, деньги и чужие надежды. И прежде чем отдать приказ, мудрый правитель всегда спрашивает: «Покажите мне тех, кого коснётся моё решение».

Макс не стал ничего исправлять. Он просто скопировал всю строчку DELETE FROM table WHERE... и вставил её ниже. А затем одним движением заменил DELETE FROM на SELECT * FROM.

— Посмотри им в глаза, — сказал он. — Не как строкам. А как историям, которые ты собираешься стереть.

На экране появились сотни записей. Коля пробежался по ним взглядом... и замер. Одна из записей. Статус — 'test'. Дата — августовская. Но сумма — огромная. И комментарий: «Демо для ключевого клиента N, не трогать до согласования». Эту запись завели вручную для презентации, и она попала под все формальные критерии удаления.

Он молча добавил в WHERE ещё одно условие: AND source != 'manual_demo'. Снова запустил SELECT. На этот раз опасной записи в списке не было.

Только теперь, испытав смесь ужаса и облегчения, он заменил SELECT на DELETE и нажал выполнить.

Макс сделал глоток кофе.
— Видишь? — он улыбнулся. — Ты не просто удалил мусор. Ты спас ценность. Это и есть работа хирурга. Один лишний взгляд — одна спасённая вселенная.

Он похлопал его по плечу и пошёл дальше, оставив его наедине с простой, но жизненно важной истиной: самый мощный инструмент инженера — это не право менять, а мудрость не навредить.

#best_practice

🎓 100 уроков EXPLAIN. Часть 13
Порядок столбцов в индексе: грамматика запроса

Лена вошла в кабинет Макса и без предисловий развернула ноутбук. На этот раз в её глазах не было замешательства. Был азарт.

— Макс, смотри. Индекс по (user_id, status). Запрос: WHERE user_id = ? AND status = ?. План идеальный, Index Cond по обоим полям. Я меняю их местами в индексе — (status, user_id) — план почти не меняется. Логично. Но стоит мне поменять равенство на диапазон WHERE user_id = ? AND status > ? — и магия ломается. Индекс (user_id, status) справляется, а (status, user_id) — нет, улетает в Filter. Я, кажется, понимаю, почему. Но я не чувствую, почему.

Макс откинулся в кресле. Он смотрел не на экран, а на Лену. Это уже был вопрос не только о правилах, но о природе самых правил.

— Ты пытаешься думать об индексе как о наборе инструментов, — медленно начал он. — А нужно думать о нём как о языке. У каждого индекса есть своя грамматика. Свой синтаксис. Порядок столбцов — это его алфавит.

Он взял карандаш и лист бумаги.

— Представь, что индекс — это словарь. Индекс (фамилия, имя) — это идеально отсортированный справочник. Сначала по фамилии, потом, внутри каждой фамилии, по имени.
🔸 WHERE фамилия = 'Иванов' AND имя = 'Пётр'. Ты открываешь секцию «И», находишь «Иванов», внутри неё — «Пётр». Это прямой путь. Index Cond по обоим полям.
🔸 WHERE фамилия = 'Иванов'. Ещё проще. Находишь всех Ивановых. Index Cond по первому полю.

— Это очевидно, — кивнула Лена.

— А теперь, — Макс поднял палец, — WHERE имя = 'Пётр'. Что ты будешь делать с этим словарём?

Лена нахмурилась.
— Придётся... пролистать весь словарь от А до Я и на каждой странице искать Пётр.

— Вот. Ты только что описала Seq Scan. А теперь — самое интересное. WHERE фамилия > 'Иванов'. Ты находишь Иванов и просто читаешь дальше. Данные в индексе уже лежат в нужном порядке. Запрос не просто находит, он получает отсортированный результат. И если ты попросишь WHERE фамилия = 'Иванов' AND имя > 'Пётр', он сделает то же самое: найдёт точку старта и пойдёт вперёд по уже отсортированному маршруту.

Он вернулся к её примеру.

— Твой индекс (user_id, status) — это словарь, отсортированный по пользователям. Внутри каждого пользователя — по статусу. Он «понимает» фразу «дай все статусы для этого юзера, начиная с processed». А индекс (status, user_id) отсортирован по статусам. Он идеально ответит на запрос «дай всех юзеров со статусом processed. Но на фразу «дай для всех статусов, начиная с processed, юзера с id=5» он ответить не может. Он может найти все нужные статусы, но user_id ему придётся проверять вручную. Filter.

Лена молчала, глядя в одну точку. Потом медленно произнесла:
— Порядок столбцов — это не просто приоритет. Это структура повествования. Мы заранее говорим базе, по какому сценарию будем запрашивать данные. (A, B) — это история про А, в которой есть детали про Б. А (B, A) — совсем другая история.

— Именно, — кивнул Макс. — Ты перестала искать «правильный» порядок. Ты начала думать о том, какую историю хочешь рассказать. И это уровень, на котором оптимизация становится искусством.

📌 Что запомнить
🔸 Порядок столбцов в индексе — это не приоритет, а лексикографический порядок, как в словаре.
🔸 Индекс можно эффективно использовать только по его префиксу. Условие по второму столбцу без условия по первому — часто приводит к Filter или Seq Scan.
🔸 Правильный порядок позволяет не только искать, но и избегать сортировки (ORDER BY), если порядок в запросе совпадает с порядком в индексе.

#explain #индексы

🎓 100 уроков EXPLAIN. Часть 14
Почему Hash Join — не всегда спасение

— Макс, странный случай, — Лена подошла к его столу без ноутбука, с одной лишь распечаткой плана. — Запрос выполняется две секунды. Это быстро. Но страница в интерфейсе «подвисает» на все две секунды, прежде чем показать первые 20 строк. В плане — обычный Hash Join. Я думала, он — наш друг.

Макс отставил кружку.

— Ты привыкла, что Hash Join — это мощная фабрика, которая перемалывает миллионы строк. Это так. Но ты забыла, что прежде чем фабрика выпустит первый товар, она должна быть полностью построена, — он взял листок. — Hash Join — это блокирующий узел. Он не может отдать ни одной строки наверх, пока полностью не обработает одну из таблиц и не построит из неё в памяти хеш-таблицу.

— То есть… все эти две секунды он не ищет, а готовится к поиску? — медленно проговорила Лена.

— Именно. Он строит свой «справочник». Для отчёта, который должен обработать всё, это идеальная стратегия: потратить время на подготовку, чтобы потом соединить гигантские объёмы данных молниеносно. Но для твоего интерфейса, которому нужна первая страница записей прямо сейчас, это катастрофа. Ты ждёшь первого результата почти столько же, сколько всего результата. Это time-to-first-row против time-to-total. Планировщик обычно учитывает обе метрики, но в сложных случаях или при неточностях в статистике может выбрать план, оптимизированный на общее время.

Лена нахмурилась.
— А какая альтернатива? Nested Loop? Но он же «тупой» и перебирает всё для каждой строки!

— Не совсем, — Макс начертил схему. — Nested Loop с хорошим индексом по ключу соединения — это не перебор. Это снайперская стрельба. Он берёт одну строку из внешней таблицы и мгновенно, по индексу, находит соответствующую во внутренней. И сразу же отдаёт результат наверх. Он может вернуть первую строку за миллисекунды. Да, на обработку миллиона строк он потратит гораздо больше времени, чем Hash Join. Но для пагинации он бесценен.

— Получается, Hash Join — это спринтер, который долго разминается, а Nested Loop — марафонец, который стартует сразу?

— Хорошая аналогия. Но есть и вторая ловушка Hash Join. Он требует, чтобы хеш-таблица поместилась в оперативную память (work_mem). Если планировщик ошибся в оценке числа строк, и хеш-таблица не влезает в RAM, начинаются «проливы на диск».

— А где это видно?

— В плане у узла Hash (составляющая Hash Join) ты увидишь batches: N (originally 1). Это сигнал, что Postgres пришлось разбить хеш-таблицу на несколько частей и записывать их во временные файлы на диске. Скорость падает в десятки, а то и в сотни раз.

Лена посмотрела на свой план. Там не было проливов, но она поняла главное.
— Получается, Hash Join — не только блокирующий, но и чувствительный к памяти инструмент. А Nested Loop, который мы привыкли считать «плохим», в мире интерактивных интерфейсов может быть настоящим спасением.

— Да. Важно видеть не просто узлы плана, а компромиссы, которые за ними стоят, — кивнул Макс.

📌 Что запомнить
🔸 Высокая цена запуска: Hash Join — блокирующая операция. Он не вернёт первую строку, пока не построит хеш-таблицу. Это делает его неэффективным для запросов с LIMIT или для быстрой отдачи первой страницы данных.
🔸 Зависимость от work_mem: Если хеш-таблица не помещается в оперативную память, Hash Join начинает использовать диск. Для проверки смотрите EXPLAIN (ANALYZE, BUFFERS): у Hash будет Batches: … (originally 1), рост nbatches и временные файлы
🔸 Nested Loop с индексом — отличная «стриминговая» альтернатива для быстрого старта, но без индекса по ключу соединения он сильно деградирует.
🔸 Merge Join — ещё один потоковый способ соединения. Он тоже может быстро отдавать первые строки, если оба входа уже отсортированы (например, после Index Scan).
🔸 Блокирующий Sort — главный враг быстрого старта. Если в плане есть Sort перед LIMIT, польза от «стриминговых» соединений теряется. Решение — индекс, поддерживающий нужный ORDER BY, или keyset-пагинация.

Hash Join — это не серебряная пуля. Это тяжёлая артиллерия, которую нужно применять с умом.

#explain

🛠 Сбой обязательно произойдёт

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

— Макс, у нас проблема, — начала она. — Вчера ночью был сбой в одном из сервисов, и он не смог создать исходящие платёжные поручения. При этом входящие операции из шины он исправно читал и складывал в базу. В итоге у нас несколько сотен «подвисших» переводов.

Макс спокойно отхлебнул кофе:
— И что вы уже сделали?

— Уже настроили алерт в мониторинге, который сработает, если в таблице операций появятся записи, у которых не заполнена связка с транзакцией, — поделилась Анна. — Во-вторых, мы сейчас готовим скрипт, чтобы вручную найти все такие операции и для каждой породить соответствующие транзакции. И тут нам нужно расширить права для выполнения скриптов. На время.

— То есть, вы решили проблему для вчерашнего дня, — кивнул Макс. — А что будете делать, когда это случится снова? Опять будете в спешке писать скрипты?

Анна нахмурилась:
— Ну... мы надеемся, что это не повторится.

— Надёжность не строится на надежде, — Усмехнулся Макс. — Хотя слова и похожи.

Он развернул к ней монитор.
— Ваш подход правильный, но неполный. Мониторинг — жизненно необходим. Он как ЭКГ, показывает, что что-то пошло не так. Скрипты для исправления — это как экстренная операция. А где профилактика?

— И что ты предлагаешь? — спросила Анна.

— Сделайте вашу систему «самовосстанавливающейся», — сказал Макс. — Доработайте ваше приложение. Пусть сервис при старте и периодически фоновым воркером проверяет: «Нет ли в базе операций, для которых не появилось транзакций?». Если находит — берёт их в обработку и достраивает недостающее. Главное - у вас в приложении уже есть нужный функционал, не нужно руками писать скрипты и множить вероятность ошибки из-за поспешных действий.

Анна на секунду задумалась.
— Не задублируем ли мы что-то при повторной обработке?

— Нет, если обработка идемпотентна: используйте уникальные ключи и дедупликацию, повторы — с контролем (ограниченные попытки, паузы), а на нерешаемые случаи — письмо с алертом.

— То есть, сервис сам будет исправлять последствия сбоев?

— Именно, — подтвердил Макс. — Это проще, надёжнее и работает автоматически. Вам не придётся каждый раз после сбоя поднимать команду по тревоге. Система сама догонит хвост.

— Это фундаментальный принцип отказоустойчивых систем, — продолжил он. — всегда исходить из того, что сбой обязательно произойдёт. Цель не в том, чтобы система работала без сбоев, а в быстром и предсказуемом восстановлении.

#процессы

🎓 100 уроков EXPLAIN. Часть 15
Партицированные таблицы

Лена подошла к столу Макса с новым планом. На этот раз её вид был озадаченным.

— Макс, я смотрю на запрос к нашей таблице events. И план выглядит… как-то странно. Тут появился узел Append, под которым куча Index Scan по разным таблицам. Это нормально?

Макс взглянул на план:

Append  (...)
  ->  Index Scan using idx_events_2025_08_dt on events_2025_08 ...
  ->  Index Scan using idx_events_2025_09_dt on events_2025_09 ...

— Поздравляю! — улыбнулся он. — Ты столкнулась с тем, как EXPLAIN показывает работу с партицированными таблицами. Это очень хитро устроенная таблица, которая на самом деле состоит из множества разных таблиц. Узел Append в плане это склейка результатов из релевантных партиций. Каких именно — видно по дочерним узлам.

— Я читала об этом, это похоже на шкаф с одинаковыми документами, где каждый ящик — отдельный месяц?

— Отличная аналогия! — кивнул Макс. — И самая главная магия оптимизатора здесь — это отсечение партиций. При правильном запросе Postgres заранее знает, в какие ящики ему нужно заглянуть, а какие можно проигнорировать. Это и есть ключ к производительности.

Макс открыл два примера, чтобы показать разницу.

1. "Правильный" запрос с фильтром по ключу партицирования

EXPLAIN SELECT * FROM events WHERE event_date >= '2025-08-15';

В плане будет узел Append, но под ним сканируются только те партиции (ящики), которые содержат нужные даты. Например, events_2025_08, events_2025_09 и так далее. Postgres "отсёк" все партиции за июль и более ранние, даже не заглядывая в них. Это называется partition pruning.

2. "Неправильный" запрос, который всё портит

— А теперь смотри, — Макс поменял запрос, — если не использовать ключ партицирования в запросе, ну или использовать его не напрямую.

EXPLAIN SELECT * FROM events WHERE date_trunc('month', event_date) = '2025-08-01';

Лена нахмурилась:
— План изменился! Теперь под Append сканируются все партиции с 2022 года!

— Именно! — подтвердил Макс. — И это уже плохо для производительности.

— Получается, главный секрет — помочь Postgres понять, какие партиции ему нужны? — спросила Лена.

— Точно!

— А что если мне нужно выбрать все события пользователя? — спросила Лена. — Например: WHERE user_id = 5.

Макс показал ей план:

Append (...)
  -> Index Scan using idx_events_2022_01_user_id on events_2022_01 ...
  -> Index Scan using idx_events_2022_02_user_id on events_2022_02 ...
  -> ...
  -> Index Scan using idx_events_2025_10_user_id on events_2025_10 ...

— Видишь? — пояснил Макс. — Ключ партиционирования — event_date, а фильтр по нему отсутствует. Значит, отсечение партиций не сработает, и Postgres вынужден проходить по каждой партиции отдельно: для каждой — свой Index Scan, а затем результаты «склеиваются» узлом Append. Иногда это будет Parallel Append (если включён параллелизм) или Merge Append при ORDER BY, но суть та же — много мелких сканов вместо одного большого.

— То есть партиции могут даже замедлять? — уточнила Лена.

— Могут, если часто нужен поиск по всем партициям без фильтра по ключу. Лучше сузить диапазон по event_date (например, последние 90 дней), чтобы отсеять лишние партиции. Но выбор схемы партиционирования и вообще «партиционировать или нет» — это отдельная тема.

📌 Что запомнить
🔸 Фильтр по ключу партицирования сильно повышает эффективность. Без него Postgres будет сканировать все партиции подряд.
🔸 Избегай функций над ключом. Переписывай запросы так, чтобы ключ партицирования был в "чистом" виде. Вместо date_trunc или extract используй чистую проверку интервалов.
🔸 Проверяй план через EXPLAIN. Всегда смотри, какие партиции сканируются под узлом Append. Если видишь там все партиции таблицы — это верный признак того, что отсечение не работает.

Лена задумчиво кивнула:
— Значит, партицирование — это инструмент, который требует правильного обращения. И EXPLAIN — лучший способ проверить, правильно ли ты его используешь.

— В точку! — подытожил Макс. — Теперь ты знаешь, что Append в плане — это твой друг, который показывает, насколько эффективен твой запрос.

#explain #партицирование