Как работать со сложными запросами в PostgreSQL

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

Умеете ли вы находить причину медленного запроса по плану, а не переписывать SQL наугад. Для Node.js-разработчика сюда добавляется вопрос, как запрос собирается в коде: следом часто просят показать, как вы читаете EXPLAIN, или спрашивают про N+1 в ORM.

Как отвечать

Начните с диагностики, а не с приёмов:

• pg_stat_statements: показывает запросы, которые съедают больше всего суммарного времени. Оптимизировать стоит их, а не тот, что показался страшным;
• EXPLAIN (ANALYZE, BUFFERS): фактический план с временем и чтением страниц. Смотрите на расхождение оценки rows с фактом, на Seq Scan по большой таблице, на Sort Method: external merge, то есть сортировку на диске;
• индекс под конкретный запрос: составной в порядке «равенство, потом диапазон или сортировка», частичный под постоянное условие WHERE, индекс по выражению для lower(email);
• CTE: начиная с PostgreSQL 12 CTE, на который ссылаются один раз, встраивается в основной запрос. Управлять этим можно явно через MATERIALIZED и NOT MATERIALIZED;
• оконные функции вместо коррелированных подзапросов, NOT EXISTS вместо NOT IN;
• статистика: ANALYZE после массовой загрузки, CREATE STATISTICS для зависимых колонок, из-за которых планировщик ошибается в оценках;
• пагинация по ключу (WHERE id > $1 ORDER BY id LIMIT 50) вместо большого OFFSET, который всё равно читает и выбрасывает строки.

Со стороны Node.js: параметры только через плейсхолдеры $1 в pg, без склейки строк; pg.Pool вместо нового подключения на каждый запрос; statement_timeout, чтобы тяжёлый запрос не держал соединение бесконечно. В Prisma, TypeORM или Knex включите логирование SQL и проверяйте, что реально уходит в базу.

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

Делают выводы по EXPLAIN без ANALYZE, то есть по оценкам планировщика. И забывают, что EXPLAIN ANALYZE на UPDATE или DELETE запрос реально выполняет, поэтому его заворачивают в транзакцию с ROLLBACK.

Повторяют, что CTE всегда барьер оптимизации. Это правда только для версий до 12.

Не замечают N+1: цикл по списку с await запроса внутри даёт сотню походов в базу вместо одного JOIN или WHERE id = ANY($1).