🎓 100 уроков EXPLAIN. Урок 2
Как читать дерево планов и не сойти с ума

Лена ворвалась в кабинет Макса с горящими глазами:
— Я сделала 20 EXPLAIN'ов! И теперь у меня ещё больше вопросов! Почему тут Seq Scan, а тут Index Scan? Что за цифры в скобках? И почему это похоже на дерево?

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

🌳 Дерево плана: корни вверх, листья вниз
Макс открыл пример:

EXPLAIN SELECT * FROM orders JOIN users ON users.id = orders.user_id;

Hash Join  (cost=100.50..150.20 rows=500 width=200)
  Hash Cond: (orders.user_id = users.id)
  ->  Seq Scan on orders  (cost=0.00..50.00 rows=1000 width=100)
  ->  Hash  (cost=70.50..70.50 rows=2000 width=100)
        ->  Seq Scan on users  (cost=0.00..70.50 rows=2000 width=100)

— Видишь эту лесенку? Читаем снизу вверх:

Листья (низ): Сначала сканируем таблицы (Seq Scan)
Ветки (середина): Хешируем данные (Hash)
Ствол (верх): Соединяем результаты (Hash Join)

Лена постучала пальцем по экрану:
— То есть Postgres сначала выполняет то, что глубже в дереве?

— Бинго! — Макс достал маркер. — Это как сборка бургера:

Сначала достаём булочки и котлету (читаем таблицы)
Потом собираем (JOIN, сортировка)
В конце подаём (возвращаем результат)

🔢 Цифры в скобках: что значит cost=100.50..150.20?
— Первое число — стартовая стоимость, второе — полная. Важно: стоимость накапливается снизу вверх!

Лена кивнула:
— То есть 150.20 — это сумма всего, что ниже плюс сам Hash Join?

— Именно! — Макс ухмыльнулся. — Как чек в ресторане: итоговая сумма = закуски + основное + десерт.

Лена нахмурилась:
— А почему иногда cost=0.00..50.00?

— А, это особенность расчётов! — махнул рукой Макс. — Postgres не учитывает подготовку к Seq Scan как затраты.
Но на самом деле:
- 0.00 — не значит «бесплатно», просто нет дополнительных расходов как у индексов
- 50.00 — полная цена чтения всей таблицы

📊 Четыре важных типа узлов (для начала)
Макс нарисовал на доске:

Seq Scan (Трактор) 🚜
— Читает таблицу от начала до конца, как трактор пашет поле.
— Когда встречается: Нет подходящего индекса / читается вся таблица.

Index Scan (Спорткар) 🏎
— Быстро находит данные по индексу, но если строк много — делает много «поездок» туда-сюда.
— Когда встречается: WHERE по индексированному полю.

Sort (Конвейерная лента) 🔀
— Сортирует данные перед выдачей (ORDER BY) или для соединения.
— Когда встречается: ORDER BY без индекса / GROUP BY / DISTINCT.

Hash Join (Фабрика) 🏭
— Сначала строит хеш-таблицу из одной таблицы, потом ищет совпадения.
— Когда встречается: JOIN больших таблиц по равенству (=).

💡 Практика: разберём реальный план
Макс открыл заготовленный пример:

Limit  (cost=150.21..150.21 rows=1)
  ->  Sort  (cost=150.20..155.20 rows=2000)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (cost=0.00..50.00 rows=2000)

— Видишь? — ткнул он в Sort. — Без индекса по created_at Postgres:

Сначала трактором (Seq Scan) читает все заказы,
Потом конвейером (Sort) сортирует,
И только потом обрезает (Limit).

— Если добавить индекс — Sort исчезнет, — Макс хитро улыбнулся. — Но это уже следующий урок!

📌 Что запомнить
🌳 План — перевёрнутое дерево (читаем снизу вверх)
🔢 cost=старт..конец — как быстро получим первую и все строки
🚜 Seq Scan vs 🏎 Index Scan — трактор или спорткар?

Лена потянулась за ноутбуком:
— Окей, я попробую еще «почитать» планы. Если у меня будут еще вопросы?

— Присылай скриншоты, — Макс вручил ей шоколадку. — Разберём вместе. А завтра я покажу, как заставить Postgres использовать индекс!

#explain

P.S. Задание:
Сделайте EXPLAIN для любого запроса с JOIN
Попробуйте «прочитать» дерево плана снизу вверх
Найдите самый «дорогой» узел (максимальное увеличение cost)

⚖️ Скорость или качество?

Типичное утро в опенспейсе. Кофемашина хрипела, выдавая очередную порцию. Макс, с красными от недосыпа глазами работал над каким-то скриптом. На втором мониторе Zabbix радостно подсвечивал десяток алертов красным.

— Ну и день... — пробормотал он, отхлебывая кофе. — Опять эта долбанная релизная гонка...

Таня (тимлид, за его спиной):
— Макс, ну что там с нашим "горячим" фиксом? Клиенты уже в чате истерику закатывают.

— Не торопи меня, Тань! — Макс развернулся к ней. — Петя из маркетинга вчера кричал, что фичу нужно "еще вчера". Коля из разработки сегодня вдруг понял, что "упустил один момент". И с кого теперь спрос? Правильно — с меня!

В этот момент подошел Сергей, старший разработчик.

Сергей:
— А я говорил Ане, что нельзя было обещать клиентам эту фичу без полноценного тестирования. Но нет же, "time to market превыше всего"!

Аня (продукт-менеджер, входя в комнату):
— Эй, я тут ни при чем! Сергей, ты же сам сказал, что "в принципе должно работать"! Да и протестировали же все!

Оля (тестировщица, из-за перегородки):
— А ничего, что меня в час ночи сегодня подняли тестировать ваш "срочный" релиз, который "нужно выкатить до утра любой ценой"?!

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

Сергей (скрестив руки):
— Я поддерживаю. Может хватит уже гнаться за сроками? Давайте делать все нормально.

Таня:
— Легко сказать, когда конкуренты уже две недели как выпустили аналогичный функционал...

Оля (неожиданно мягко):
— Знаете что, ребята... Мы все постоянно разрываемся между сроками и качеством. Это нормально. Но главное — не винить друг друга, а системно улучшать процессы. Я вот на прошлом проекте...

Макс (перебивает с ухмылкой):
— Да ладно, Оль, ты же знаешь, что мы тут все перфекционисты-невротики. Но спасибо, что напомнила — мы действительно не одни такие.

Сергей (кивает):
— Точно. Главное — не повторять одни и те же ошибки. И фиксировать техдолги.

Макс откинулся на спинку кресла:
— Тогда предлагаю простые правила:
1️⃣ Деньги и безопасность — тестируем до посинения
2️⃣ Не прячем ошибки. За EXCEPTION WHEN OTHERS NULL; — расстрел
3️⃣ Если делаем быстро "любой ценой" — сразу записываем техдолг с дедлайном

Аня:
— Но как тогда успевать за рынком?

— А вот смотрите, — Макс показал на список открытых заявок в helpdesk. — Если тратить 30% времени на профилактику проблем, мы экономим 70% на их исправлении.

Все задумались. Кофемашина одобрительно булькала .

Таня (подводя итог):
— Значит, так: не виним себя, фиксируем проблемы, и... Макс, сделай уже тот график зависимости багов от релизов — будем тыкать в него маркетологам.

Все смеются. Напряжение спадает.

#процессы

P.S. Макс вечером записал в дневник: "День 149. Оля неожиданно оказалась голосом разума. Надо чаще её слушать. И купить ей шоколадку."

🎓 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