Какие механизмы ускорения запросов есть в PostgreSQL

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

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

Как отвечать

Начните с диагностики: EXPLAIN (ANALYZE, BUFFERS) показывает реальный план, время узлов и сколько страниц прочитано из памяти и с диска. Медленные запросы в проде ищут через pg_stat_statements.

Дальше механизмы:

• индексы: B-tree по умолчанию, GIN для jsonb, массивов и полнотекстового поиска, GiST для геоданных и диапазонов, BRIN для огромных таблиц, упорядоченных по времени. Плюс частичные, по выражению (lower(email)) и покрывающие с INCLUDE, дающие index-only scan;
• статистика планировщика: ANALYZE и autovacuum. Устаревшая статистика ведёт к неверной оценке строк и плохому плану, для зависимых колонок есть CREATE STATISTICS;
• память: shared_buffers это кэш страниц, work_mem выделяется на каждую сортировку или хэш в запросе. Если памяти не хватило, в плане видно external merge Disk;
• параллельные запросы и секционирование с отсечением партиций по WHERE;
• материализованные представления для тяжёлой аналитики с REFRESH ... CONCURRENTLY.

Со стороны Java: убрать N+1 в Hibernate через JOIN FETCH или @EntityGraph, включить пакетную вставку (hibernate.jdbc.batch_size и reWriteBatchedInserts=true у pgJDBC), держать пул HikariCP в десятки, а не сотни соединений. Большие выборки читать с setFetchSize при выключенном autocommit, иначе драйвер загрузит весь результат в память.

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

Советуют пагинацию через OFFSET. База всё равно прочитает и выбросит пропущенные строки, на дальних страницах это секунды. Нужна keyset-пагинация по индексу.

Переписывают IN на JOIN «для скорости». Планировщик сам превращает IN (подзапрос) в semi join, выигрыша обычно нет.

Смотрят EXPLAIN без ANALYZE и не видят расхождения между оценкой и реальным числом строк, которое и объясняет плохой план.