Как проверить использование индексов и построение плана выполнения запроса в базе данных?

Что проверяют

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

Как отвечать

Шаг первый: получить настоящий SQL. spring.jpa.show-sql печатает запрос без значений параметров, поэтому лучше логировать org.hibernate.SQL на уровне DEBUG, а привязку параметров в Hibernate 6 через org.hibernate.orm.jdbc.bind на TRACE, или подключить datasource-proxy либо p6spy.

Шаг второй: план. В PostgreSQL:

• EXPLAIN: только оценка планировщика, запрос не выполняется;
• EXPLAIN (ANALYZE, BUFFERS): запрос выполняется, видно реальное время, число строк и чтение страниц. UPDATE и DELETE так проверяют внутри транзакции с ROLLBACK;
• узлы: Seq Scan читает всю таблицу, Index Scan и Index Only Scan работают по индексу, Bitmap Heap Scan это промежуточный вариант для большого числа строк;
• оценка против факта: если ожидаемые rows расходятся с реальными в десятки раз, статистика устарела, помогает ANALYZE.

В MySQL смотрят колонку type: const, eq_ref, ref и range означают работу по индексу, ALL полное сканирование таблицы, а index полный проход по индексу, что тоже дорого. В Extra тревожные сигналы Using filesort и Using temporary.

Шаг третий: взгляд на всю систему. pg_stat_statements покажет самые тяжёлые запросы, pg_stat_user_indexes с idx_scan = 0 покажет неиспользуемые индексы, auto_explain запишет планы медленных запросов в лог.

Где спотыкаются

Смотрят план на тестовой базе со ста строками. Планировщик честно выбирает Seq Scan, потому что так дешевле, и кандидат делает неверный вывод.

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

Не замечают, что индекс отключил сам запрос: функция над колонкой, LIKE '%текст', несовпадение типа параметра и колонки.