Generated SQL, EXPLAIN и индексы

1. Generated SQL: источник правды

SQL-лог уже дал нам форму проблемы: root query, secondary selects, wide SELECT, внезапные записи в read-case. Statistics рядом с ним ответили на другой вопрос — насколько это всё масштабно и повторяемо. Но бывает и такая картина: запросов уже немного, secondary selects не стреляют, flush не мешает, а сценарий всё равно тяжёлый. Значит, bottleneck сидит не в chatty ORM-поведении как таковом, а в конкретном SQL, который реально исполняет база.

Вот здесь и нужен generated SQL. Мы больше не спорим на уровне «мой JPQL вроде простой». Нам нужен тот SQL, который уехал в PostgreSQL, а дальше — EXPLAIN, чтобы понять, как база собирается его выполнять.

Когда мы пишем JPQL/HQL, мозг начинает жить в приятной реальности: «я же выбрал Product, отфильтровал по status, отсортировал по name — значит, в базе будет быстрый запрос». Проблема в том, что Hibernate — это не телепат, а компилятор: он превращает вашу абстракцию в конкретный SQL, учитывая маппинг, типы колонок, диалект PostgreSQL, настройки fetching и даже такие мелочи, как distinct и порядок join. Поэтому для диагностики важен не «замысел», а «исполнение».

В Hibernate-heavy проекте, как наш Commerce Persistence Lab, спорить о производительности на уровне JPQL — это примерно как спорить о скорости доставки по словам «ну курьер же на велосипеде». Велосипед — это JPQL, а реальный маршрут с пробками, светофорами и перекрытыми улицами — это generated SQL.

Чтобы заземлиться, возьмём знакомый пример «список активных товаров» (каталог):

import jakarta.persistence.EntityManager;
import java.util.List;

public class CatalogQuery {
    private final EntityManager entityManager;

    public CatalogQuery(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<?> findActive() {
        // JPQL — это "идея запроса", а не гарантия того, какой SQL реально поедет в базу
        return entityManager.createQuery("""
                select p
                from Product p
                where p.status = :status
                order by p.name
                """)
            // Параметры важны: от значения и типа может зависеть итоговый план
            .setParameter("status", ProductStatus.ACTIVE)
            .getResultList();
    }
}

На уровне JPQL всё выглядит «невинно». Но уже на уровне SQL возможны варианты, которые глазом по JPQL вы не угадаете: какие именно колонки выбраны (особенно если это entity-loading), какие алиасы и join’ы появились, как выражены условия, какие типы сравниваются, не добавился ли где-то left join из-за fetch-плана и так далее.

Поэтому правило сегодняшнего дня звучит скучно, но спасает нервы: анализируем не JPQL, а SQL, который ушёл в базу. А дальше — EXPLAIN показывает, как именно PostgreSQL планирует этот SQL выполнять.

2. Как поймать нужный SQL

EXPLAIN — штука мощная, но у неё есть одна неприятная черта: она не умеет «объяснять ваш сервисный метод». Ей нужен конкретный SQL. Значит, наша задача — аккуратно вытащить из SQL-лога ровно тот запрос, который соответствует одному use case. И здесь легко сделать две классические ошибки: либо взять не тот запрос (потому что их в логе сотни), либо взять SQL с ? вместо параметров (а потом пытаться объяснить базе, что такое status = ? без значения).

Самый простой способ сделать SQL-лог дружелюбным — помечать ключевые запросы комментариями. Hibernate умеет добавлять comment к запросу, и в логе вы потом находите блок буквально по тексту. В нашем проекте это особенно полезно на больших датасетах и в длинных сценариях, где запросов много.

Вот минимальный пример на Hibernate API (через Session), который вы можете держать прямо в query-сервисе:

import jakarta.persistence.EntityManager;
import org.hibernate.Session;
import java.util.List;

public class CatalogQueryService {
    private final EntityManager entityManager;

    public CatalogQueryService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<?> findActiveProducts() {
        // Достаём Hibernate Session, чтобы навесить SQL-комментарий на конкретный запрос
        Session session = entityManager.unwrap(Session.class);

        return session.createQuery("""
                select p from Product p
                where p.status = :status
                order by p.name
                """)
            // Комментарий попадёт в SQL-лог и позволит быстро найти нужный запрос среди сотен строк
            .setComment("catalog-active-products")
            // Параметр в SQL будет как '?', а значение — отдельной строкой в bind-логах
            .setParameter("status", ProductStatus.ACTIVE)
            .list();
    }
}

Если в профиле sql-trace у вас включены SQL-комментарии, в логе это начинает выглядеть примерно так (упрощённо):

/* catalog-active-products */
-- Это generated SQL из лога; параметры здесь будут плейсхолдерами '?'
select p1_0.id, p1_0.sku, p1_0.name, p1_0.status, p1_0.price_amount, p1_0.price_currency
from product p1_0
where p1_0.status=?
order by p1_0.name

А рядом (при включённом bind-логировании) вы увидите значения параметров, например:

binding parameter [1] as [VARCHAR] - [ACTIVE]

И вот тут появляется практический момент: для EXPLAIN нам нужна версия SQL с подставленным значением (и обычно с правильными кавычками/типами). То есть мы должны построить «финальный» SQL примерно так:

-- EXPLAIN имеет смысл делать на SQL, максимально близком к реальному запросу (включая значения параметров)
explain
select p1_0.id, p1_0.sku, p1_0.name, p1_0.status, p1_0.price_amount, p1_0.price_currency
from product p1_0
where p1_0.status='ACTIVE'
order by p1_0.name;

Пара слов про «границы сценария». Даже если вы нашли один запрос, не спешите делать EXPLAIN и радоваться: убедитесь, что это действительно доминирующий запрос сценария, а не, скажем, «разовый запрос к таблице категорий». Если у вас N+1, то проблема не в одном SQL, а в серии повторяющихся шаблонов. Но если по статистике queryExecutionCount=1, а время всё равно плохое — это как раз идеальная ситуация для EXPLAIN: один запрос, который «тяжело едет».

3. EXPLAIN в PostgreSQL: чтение плана

EXPLAIN — это способ спросить у PostgreSQL: «Как ты собираешься выполнить вот этот SQL?» Ответом будет план выполнения (execution plan): дерево шагов вроде «прочитать таблицу», «применить фильтр», «соединить с другой таблицей», «отсортировать», «вернуть результат». Важно понимать философию: база не обязана выполнять запрос «как вам кажется логичным»; она выбирает путь, который считает наиболее дешёвым по своей модели стоимости.

Обычно страх начинается с того, что EXPLAIN выглядит как заклинание на древнем языке:

Sort  (cost=123.45..124.67 rows=100 width=64)
  Sort Key: p.name
  ->  Seq Scan on product p  (cost=0.00..98.00 rows=1000 width=64)
        Filter: (status = 'ACTIVE'::text)

Если убрать мистику, это читается вполне по-человечески: «я собираюсь пройти таблицу product целиком (Seq Scan), отобрать строки по status, а потом отсортировать результат по name».

Чтобы не утонуть в деталях, на уровне курса нам достаточно понимать несколько самых частых узлов плана. Вот небольшая шпаргалка — не «полная таблица всех планов мира», а то, что вы будете реально видеть в наших лабораториях:

Bitmap Heap Scan

У каждой строки в плане есть cost, rows, width. Не нужно пытаться трактовать cost как миллисекунды (это не «120 ms», это некие внутренние условные единицы). Но полезно держать в голове: если верхушка плана имеет гигантский cost, это почти всегда причина, почему запрос «дорогой» по мнению оптимизатора.

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

4. EXPLAIN: консоль и Java

Когда вы впервые пробуете EXPLAIN, возникает вопрос: «Окей, а где именно его запускать?» Можно запускать в любой SQL-консоли: psql, DBeaver, DataGrip, даже в веб-консоли — лишь бы это была та же база, где вы воспроизводите сценарий. Но иногда удобно запускать EXPLAIN прямо из Java, особенно в лабораторных утилитах проекта, чтобы быстро сравнить несколько вариантов или сохранить план рядом с логами.

Самый простой, «без философии» вариант — вручную скопировать SQL из лога и выполнить в SQL-консоли:

-- Ручной запуск в консоли: просто берём SQL (с параметрами) и просим план
explain
select p.id, p.sku, p.name
from product p
where p.status = 'ACTIVE'
order by p.name;

Но мы хотим ещё и «Java-вариант», чтобы вы видели, что это не какое-то отдельно стоящее искусство. В JPA/Hibernate вы можете выполнить EXPLAIN через native query. PostgreSQL вернёт план как набор строк, и вы можете просто их распечатать.

Минимальный пример:

import jakarta.persistence.EntityManager;
import java.util.List;

public class ExplainRunner {
    private final EntityManager entityManager;

    public ExplainRunner(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public void explain() {
        // EXPLAIN — это просто SQL, поэтому выполняем его как native query
        List<String> plan = entityManager.createNativeQuery("""
                explain
                select p.id, p.sku, p.name
                from product p
                where p.status = 'ACTIVE'
                order by p.name
                """).getResultList();

        // PostgreSQL возвращает план как строки; печатаем их для диагностики
        plan.forEach(System.out::println); // Sort ... / Seq Scan ...
    }
}

Если вы запускаете это в нашем проекте, логически полезно держать в голове две тонкости. Во-первых, EXPLAIN — это всё равно SQL к базе. Если вы запускаете его «внутри приложения», вы всё ещё зависите от того, какие данные в базе и какие индексы уже применены Flyway-миграциями. Во-вторых, native query и строки плана — это не «API для бизнеса», а инструмент диагностики. Не надо «строить на EXPLAIN бизнес-фичу», это как не надо делать endpoint /please-optimize-me.

Иногда вы захотите сделать вариант с EXPLAIN ANALYZE. Он может выглядеть так:

-- Важно: ANALYZE выполняет запрос по-настоящему, поэтому используйте его осознанно
explain analyze
select p.id, p.sku, p.name
from product p
where p.status = 'ACTIVE'
order by p.name;

В курсе мы можем упоминать его как «следующий уровень точности», но использовать нужно аккуратно: он действительно выполняет запрос и тратит реальное время и ресурсы.

5. Индексы по плану выполнения

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

Самая частая ошибка начинающих — думать так: «Запрос медленный → значит нужен индекс». На практике сначала нужно понять, почему он медленный. И EXPLAIN как раз отвечает на этот вопрос. Если план говорит «Seq Scan на большой таблице + filter по колонке», это неплохой кандидат на индекс. Если план говорит «много времени уходит на Sort», то индекс может помочь, если он соответствует ORDER BY. А если проблема в том, что вы загрузили гигантский граф сущностей, где половина полей вообще не нужна, индекс тут не спасёт: вы просто быстро найдёте строки… и всё равно вытащите тонну данных.

Чтобы связать план и индексы более инженерно, удобно держать в голове такой «переводчик» симптомов:

Filter

Seq Scan

Index Scan

Особенно осторожно стоит относиться к колонкам с маленьким числом возможных значений (низкая кардинальность), например status со значениями ACTIVE/INACTIVE/DELETED. Индекс по такой колонке может быть полезен, но может и не использоваться, если, скажем, ACTIVE составляет 95% строк. PostgreSQL не «обязан» использовать индекс — он выбирает то, что дешевле.

И ещё одна важная ремарка именно в контексте ORM: индекс не лечит N+1. Он может сделать каждый из N запросов чуть быстрее, но вы всё равно останетесь с N запросами, только более бодрыми. Это как лечить дырявую лодку более быстрым насосом.

6. Пример: каталог активных товаров

Чтобы связать всё в одну цепочку, возьмём реальный для нашего проекта read-case: «админский список активных товаров, отсортированный по имени». На уровне Java он обычно выглядит как простая выборка с order by. На уровне SQL это превращается в запрос к таблице product. А на уровне базы возникает вопрос: как быстро найти товары по статусу и отсортировать?

Пусть наш query-метод выглядит так: мы добавим и комментарий, и read-only hint, чтобы в read-сценарии не тратить лишнее на dirty checking.

import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;
import java.util.List;

public class CatalogQueryService {
    private final EntityManager entityManager;

    public CatalogQueryService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<Product> findActiveProducts() {
        // TypedQuery помогает держать контракт "мы читаем Product", а не сырые Object[]
        TypedQuery<Product> q = entityManager.createQuery("""
                select p from Product p
                where p.status = :status
                order by p.name
                """, Product.class);

        return q.setParameter("status", ProductStatus.ACTIVE)
                // Read-only режим: меньше накладных расходов на dirty checking в чистом read-case
                .setHint("org.hibernate.readOnly", true)
                .getResultList();
    }
}

Теперь включаем профиль sql-trace, запускаем сценарий и достаём из лога generated SQL. Он может выглядеть не один в один как ваш JPQL, и это нормально. Он будет примерно такой формы:

/* catalog-active-products */
select
    p1_0.id,
    p1_0.sku,
    p1_0.name,
    p1_0.status,
    p1_0.price_amount,
    p1_0.price_currency
from product p1_0
where p1_0.status=?
order by p1_0.name

Дальше мы восстанавливаем параметр (например, ACTIVE) и строим EXPLAIN:

explain
select
    p.id, p.sku, p.name, p.status, p.price_amount, p.price_currency
from product p
where p.status = 'ACTIVE'
order by p.name;

Типичный план без подходящего индекса (или на достаточно большом наборе данных) может выглядеть так:

Sort
  Sort Key: p.name
  -> Seq Scan on product p
       Filter: (status = 'ACTIVE'::text)

Что нам говорит эта картина? База читает всю таблицу product, отбрасывает «не ACTIVE», и потом сортирует оставшееся. Если таблица маленькая — всё ок, это даже может быть быстрее индекса. Но если таблица стала большой (наш big-dataset профиль как раз для того, чтобы «стало большой»), то Seq Scan + Sort превращается в вполне реальную проблему.

Теперь допустим, у нас есть индекс, который поддерживает и фильтрацию, и сортировку. Классический кандидат тут — составной индекс (status, name). Тогда план может стать похожим на:

Index Scan using idx_product_status_name on product p
  Index Cond: (status = 'ACTIVE'::text)

Смысл в том, что база может пройти по индексу уже в порядке name (внутри группы status=ACTIVE) и не делать отдельную сортировку. Это и есть тот момент, когда разговор про индекс становится осмысленным: мы видим конкретный узел плана (Sort + Seq Scan) и понимаем, какую часть работы можно потенциально убрать.

Но обратите внимание: если ваш SELECT очень широкий или тащит тяжёлые поля, например огромный текст описания или технических характеристик (в реальных проектах это иногда десятки килобайт), то даже идеальный индекс не спасёт — вы всё равно физически читаете и гоняете много данных. В таком случае первый кандидат на улучшение — не индекс, а read-model (проекции) и контроль fetching, которые мы уже проходили в предыдущих модулях.

7. Пример: список заказов

Второй живой сценарий из Commerce Persistence Lab — «список новых заказов, последние сверху». Он очень похож на каталожный кейс: фильтр по статусу и сортировка по дате создания. Только таблица другая (purchase_order), и сортировка обычно по created_at desc.

На уровне JPQL это выглядит почти так же:

import jakarta.persistence.EntityManager;
import java.util.List;

public class OrderQueryService {
    private final EntityManager entityManager;

    public OrderQueryService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<PurchaseOrder> findNewOrders() {
        // Read-case: нам важны фильтрация по статусу и порядок "сначала новые"
        return entityManager.createQuery("""
                select o from PurchaseOrder o
                where o.status = :status
                order by o.createdAt desc
                """, PurchaseOrder.class)
            // В реальном SQL это станет условием по колонке статуса (и тут могут помочь индексы)
            .setParameter("status", OrderStatus.NEW)
            // Read-only режим уменьшает накладные расходы ORM
            .setHint("org.hibernate.readOnly", true)
            .getResultList();
    }
}

Достаём SQL из лога и запускаем EXPLAIN. Если индекса нет, база опять легко может прийти к Seq Scan + Sort. И это логично: «прочитал всё, выбрал нужное, потом отсортировал».

Если же индекс есть, например (status, created_at desc) или хотя бы (status, created_at), то база получает шанс пройти по индексу уже в нужном порядке. Тогда план может перейти в Index Scan и сортировка исчезнет (или станет существенно дешевле).

Этот пример полезен именно как «шаблон мышления»: сортировка — это работа. Когда вы видите Sort в плане на большом наборе данных, у вас появляется конкретный вопрос: «а могу ли я дать базе порядок через индекс?» И да, иногда ответ «нет», потому что сортировка сложная, или потому что данные всё равно почти все подходят под фильтр, или потому что join’ы раздувают результат. Но это уже инженерный разговор, а не гадание.

8. Типичные ошибки при работе с SQL, EXPLAIN и индексами

Ошибка №1: делать EXPLAIN на «примерный SQL», а не на фактический generated SQL.
Иногда разработчик смотрит на JPQL, мысленно переводит его в SQL «как бы я написал руками», запускает EXPLAIN и делает выводы. Проблема в том, что Hibernate мог добавить join, мог выбрать другие колонки, мог использовать другие алиасы и даже другую форму условия. В итоге вы оптимизируете запрос, которого в реальности не существует, а настоящий generated SQL как был дорогим, так и остаётся.

Ошибка №2: забыть про параметры и типы, а потом удивляться странному плану.
status = 'ACTIVE' и status = ACTIVE — это два разных мира, и PostgreSQL довольно строго относится к типам. Если вы подставили параметр «на глаз», можно получить либо ошибку, либо план, который не соответствует реальному сценарию. Правильный подход — брать значения параметров из bind-логов (sql-trace) и подставлять их аккуратно, с кавычками и корректным типом.

Ошибка №3: лечить индексами проблему формы чтения (например, N+1 или overfetching).
Если в сценарии N+1, вы можете добавить индекс и действительно ускорить каждый отдельный запрос. Но вы всё равно будете выполнять десятки/сотни запросов там, где нужен один нормальный read-case. Это лечение симптомов без причины. Сначала исправляем форму чтения (fetch-план или projection), а потом уже решаем, есть ли смысл в индексах под финальную форму SQL.

Ошибка №4: воспринимать cost как миллисекунды и спорить о «130.42 ms» по EXPLAIN.
cost — это внутренняя модель стоимости PostgreSQL, а не время. Она полезна для сравнения планов и выбора стратегии, но не обязана совпадать с реальностью «в миллисекундах». Если вам нужно реальное время — это EXPLAIN ANALYZE, и то с пониманием, что это измерение конкретного запуска на конкретном железе.

Ошибка №5: запускать EXPLAIN ANALYZE бездумно на тяжёлом запросе и потом грустить.
EXPLAIN ANALYZE реально выполняет запрос. На большом датасете это может занять время, нагрузить базу, забить кеши и ухудшить жизнь всему локальному стенду. Для учебной диагностики чаще достаточно начать с EXPLAIN, а ANALYZE включать тогда, когда вы точно понимаете, что и зачем измеряете.

1

Задача

Hibernate deep-dive, 26 уровень, 2 лекция

Недоступна

Комментарий запроса и вывод `EXPLAIN` из приложения

Комментарий запроса и вывод `EXPLAIN` из приложения

Открыть

1

Задача

Hibernate deep-dive, 26 уровень, 2 лекция

Недоступна

Индекс через Flyway и чтение его из `pg_indexes`

Индекс через Flyway и чтение его из `pg_indexes`

Открыть