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

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

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

Как отвечать

Основной инструмент это план запроса:

• PostgreSQL: EXPLAIN показывает оценочный план, EXPLAIN (ANALYZE, BUFFERS) выполняет запрос и выводит реальные строки, время и чтения страниц. Индекс работает, если в плане есть Index Scan, Index Only Scan или Bitmap Index Scan с его именем, а Seq Scan означает полный проход по таблице;
• MySQL: в выводе EXPLAIN смотрят key (выбранный индекс), type (ALL это полный скан, ref, range, const это работа по индексу) и rows. С версии 8.0.18 есть EXPLAIN ANALYZE;
• статистика: pg_stat_user_indexes.idx_scan показывает, сколько раз индекс вообще читался. Ноль после недели работы означает мёртвый индекс;
• реальный SQL из приложения: логгер org.hibernate.SQL, а для значений параметров datasource-proxy или p6spy. Для медленных запросов в PostgreSQL удобен модуль auto_explain, он сам пишет план в лог.

Затем назовите причины, по которым индекс игнорируется: низкая селективность, маленькая таблица, функция над колонкой (lower(email)), LIKE '%текст', неподходящий порядок колонок в составном индексе, неявное приведение типа параметра, устаревшая статистика (её обновляет ANALYZE).

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

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

Запускают EXPLAIN ANALYZE на UPDATE или DELETE, забыв, что он реально выполняет запрос. Такие проверки делают в транзакции с ROLLBACK.

Смотрят план на локальной базе со ста строками. Там полный скан дешевле индекса, и вывод «индекс не работает» ошибочен: план надо смотреть на объёме, близком к боевому.

Проверяют только наличие индекса через pg_indexes или SHOW INDEX. Это ничего не говорит о том, выбирает ли его планировщик.