Оптимизация запросов PostgreSQL

От понимания планов к эффективным индексам

25 мая 2024

У PostgreSQL зрелый оптимизатор запросов, но он работает по предсказуемым правилам: стоимостная модель, статистика по таблицам, набор алгоритмов соединений. Понимание этих правил - главное отличие запроса, который работает за миллисекунды, от запроса, падающего по таймауту. Эта статья - не каталог EXPLAIN-флагов, а попытка собрать карту: откуда взялась стоимостная оптимизация, как именно планировщик принимает решения, где он ошибается, и как читать его выводы так, чтобы вместо угадывания приходить к конкретным правкам в индексах и запросах.

Откуда берётся стоимостная оптимизация Этапы выполнения запроса Как считается стоимость Селективность и точность статистики Индексы: когда сканировать, когда читать последовательно Читаем EXPLAIN ANALYZE Оптимизация коротких запросов Оптимизация длинных запросов Антипаттерны и где их искать Чек-лист оптимизации Источники и смежные статьи

Откуда берётся стоимостная оптимизация

Идея «база сама выбирает, как выполнить запрос» сегодня кажется естественной, но в начале 1970-х она была спорной. Реляционная модель Эдгара Кодда (1970) описала, что данные можно запрашивать декларативно, но не сказала, как именно эти запросы выполнять. Первые реляционные системы делали это императивно: программист указывал порядок соединений и индексы. Прорыв случился в проекте System R в IBM Research, где в 1979 году Patricia Selinger с соавторами опубликовали статью «Access Path Selection in a Relational Database Management System» (SIGMOD 1979). Это - канонический документ cost-based query optimization: впервые формально описано, как оптимизатор перечисляет планы (dynamic programming по порядку соединений), оценивает их стоимость через модель ввода-вывода и CPU и выбирает минимальный. Селинджеровская архитектура - стоимостная функция плюс статистика плюс динамическое программирование - до сих пор лежит в основе планировщиков всех серьёзных СУБД, включая PostgreSQL.

Сама PostgreSQL родилась как академический проект Майкла Стоунбрейкера в Berkeley в 1986 году (продолжение POSTGRES после Ingres). Стоимостный планировщик был с самого начала, но в его современном виде - с парсером, переписывателем и cost-based оптимизатором, разделёнными на три фазы - устоялся к версии 6.5 (1999). Tom Lane, один из старейших активных контрибьюторов проекта и автор большой части кода планировщика, на pgsql-hackers неоднократно повторял базовое правило, по которому он много лет принимает архитектурные решения: оптимизатор должен быть «good enough on average, не оптимальным на каждом запросе». Это означает, что у PostgreSQL принципиально нет хинтов в духе Oracle (/*+ INDEX(...) */) и не будет: вместо них предлагается чинить статистику, переписывать запрос или менять планировочные параметры. Решение, которое часть индустрии до сих пор считает спорным, но которое определило характер оптимизатора.

За двадцать лет существования cost-based-планировщика в Postgres сложилась длинная линейка улучшений, и любая статья об оптимизации запросов опирается на этот фундамент: bitmap-сканы (8.1, 2005), оконные функции и CTE (8.4, 2009), там же - расширение pg_stat_statements для агрегированной статистики по запросам, материализованные представления (9.3, 2013), параллельное выполнение запросов (9.6, 2016), декларативное партиционирование (10, 2017) с серьёзным улучшением partition pruning в 11-13, JIT-компиляция выражений (11, 2018), значительные улучшения параллельного хеш-джойна (с 11), incremental sort и план-стабильность (14, 2021). Многие из этих изменений приходили через работу нескольких людей, имена которых стоит знать, потому что их доклады и блоги - лучший источник, когда планировщик ведёт себя странно: Tom Lane (общая архитектура планировщика), Robert Haas (Citus, Microsoft - параллельный запрос и declarative partitioning), Andres Freund (anarazel.de - WAL, parallel query, real-world performance), Bruce Momjian (momjian.us - публичные презентации по внутренностям). На прикладной стороне канонический автор - Markus Winand с книгой «SQL Performance Explained» (2012) и сайтом use-the-index-luke.com: он формулирует правила работы с индексами вне зависимости от конкретной СУБД и регулярно цитируется PostgreSQL-сообществом как первый источник для разработчиков, которые впервые сталкиваются с EXPLAIN.

Когда вы запускаете EXPLAIN и смотрите на дерево плана с оценками стоимости и числа строк, вы видите прямую родословную статьи Селинджер 1979 года. Названия узлов изменились (вместо «merge-scan» - «Merge Join»), но идея - перечислить альтернативы, оценить, выбрать минимум - та же. Любой PostgreSQL-инженер, который читал статью System R, гораздо легче понимает, почему планировщик ведёт себя именно так, а не иначе.

Этапы выполнения запроса

Каждый SQL-запрос в PostgreSQL проходит три отделённые друг от друга фазы: разбор, планирование, выполнение. Это разделение - не косметика, а архитектурное решение из ранних версий: каждая фаза имеет свой набор структур данных, свой кэш и свои варианты оптимизации. Понимание границ между фазами помогает диагностировать проблемы: тормозит ли парсинг (длинные запросы, plpgsql с динамическим SQL), планирование (огромные IN-листы, плохая статистика), или собственно выполнение (неподходящий план, медленный диск).

1. Разбор (Parsing)

2. Планирование (Query Planning)

Оптимизатор строит множество возможных планов и оценивает их стоимость:

Выбирается план с минимальной оценочной стоимостью. PostgreSQL не имеет хинтов для оптимизатора - он сам принимает решения на основе статистики. Эта позиция, как уже было выше, восходит к Tom Lane и принципиальна: разработчики Postgres считают хинты костылём, который скрывает настоящую проблему (неточную статистику, плохой запрос, отсутствующий индекс), а не лечит её. На практике это означает, что инструмент диагностики у вас один - EXPLAIN, и научиться его читать важнее, чем заучить какой-либо синтаксис «принудительного использования индекса».

3. Выполнение (Execution)

План - дерево операций. Узлы выполняются снизу вверх, данные передаются от детей к родителям. Каждый узел реализует один из конечного набора алгоритмов: сканы (Seq, Index, Index Only, Bitmap Heap), соединения (Nested Loop, Hash Join, Merge Join), сортировки, агрегаты, оконные функции. Разнообразие планов - это разные комбинации этих узлов в дереве, и большая часть «магии» планировщика - это перебор вариантов и оценка стоимости каждого.

Пишите декларативно. Вложенные CTE с конструкцией WITH - императивный стиль. JOIN позволяет базе самой выбирать способ извлечения данных. Это та же идея, которую Селинджер сформулировала в 1979: декларативный SQL даёт оптимизатору пространство для выбора плана, а императивный код этот выбор отбирает.

Как считается стоимость

Стоимость (cost) - абстрактная единица, отражающая ресурсы (диск, память, CPU) для выполнения операции. Она не выражается в миллисекундах напрямую: это композитный показатель, рассчитанный по фиксированной модели и нескольким параметрам, которые можно тюнить под своё железо. 

Total Cost = Startup Cost + Run Cost

Ключевые параметры конфигурации

Параметр По умолчанию Описание
seq_page_cost 1.0 Стоимость последовательного чтения страницы
random_page_cost 4.0 Стоимость случайного доступа (обычно дороже из-за seek)
cpu_tuple_cost 0.01 Стоимость обработки одной строки
cpu_index_tuple_cost 0.005 Стоимость обработки строки индекса
work_mem 4MB Память для сортировок и хеш-таблиц

Дефолтное соотношение random_page_cost = 4.0 при seq_page_cost = 1.0 заложено в эпоху вращающихся дисков, где seek реально стоил в 4-10 раз дороже последовательного чтения. На современном NVMe-диске разница исчезает: random чтение обходится почти как последовательное, и оставление дефолтного значения приводит к тому, что планировщик систематически переоценивает стоимость Index Scan и предпочитает Seq Scan в случаях, когда индексное чтение было бы быстрее. Andres Freund в нескольких разборах на anarazel.de и в докладах на PGCon показывал реальные сценарии, где простое снижение random_page_cost до 1.1 на NVMe-стенде ускоряло workload в разы. Это - один из первых параметров, который имеет смысл тюнить, и один из немногих, для которых есть обоснованное «правильное» значение по железу.

Типы сканирования

Выбор между этими алгоритмами - предмет соревнования cost-моделей. Markus Winand в «SQL Performance Explained» (2012) показывает один из самых полезных интуитивных принципов: Index Scan выгоден тогда, когда индекс позволяет нам прочитать строки в порядке, который нам нужен (для пагинации, сортировки), либо когда выбранных строк очень мало; Bitmap Heap Scan оптимален в среднем диапазоне (2-20% таблицы), когда строк уже достаточно, чтобы random-доступ дороже последовательного, но недостаточно, чтобы вообще игнорировать индекс; Seq Scan побеждает на больших долях таблицы, потому что параллельное последовательное чтение страниц с предсказуемой работой prefetcher всегда быстрее, чем хаотичные random-чтения.

Селективность и точность статистики

Селективность - доля строк, которую выбирает условие: 

Селективность = Количество строк, удовлетворяющих условию / Общее количество строк

Правило: индекс обычно эффективен для выборок не более 2-5% таблицы.

Это правило - не абсолютная истина, а упрощение из практики. Конкретный порог зависит от ширины строки, от того, насколько данные физически упорядочены по индексу (correlation), и от того, помещается ли индекс целиком в shared_buffers. На горячей таблице с хорошим correlation Index Scan может быть выгоден и на 20% выборке; на холодной таблице с низким correlation и узкой строкой - проигрывать уже на 2%. Selinger в 1979 году записывала эту зависимость через cost formula с явными коэффициентами; современный PostgreSQL делает то же самое, только с большим количеством переменных. Поэтому правильное мышление при работе с EXPLAIN - не «попадаю ли я в порог», а «какой план планировщик считает дешевле и почему».

Откуда берётся статистика

PostgreSQL собирает статистику командой ANALYZE и хранит её в pg_statistic:

Именно точность этой статистики определяет, насколько хорошие решения принимает планировщик. И именно здесь живёт самая частая причина «странных планов»: статистика устарела, autovacuum не успевает её обновить на горячей таблице, или default_statistics_target (по умолчанию 100) недостаточен для редких, но критичных значений в MCV. Heap Analytics в нескольких публичных постах подробно разбирали свою историю отладки планировщика на multi-tenant таблицах, где проблема была именно в распределении: 99% строк - один большой tenant, оставшиеся 1% - тысячи маленьких, и на маленьких EXPLAIN показывал блестящие планы, а на большом - Seq Scan, потому что MCV-статистика не отлавливала перекос. Решение - повышение default_statistics_target для конкретных колонок и переключение на CREATE STATISTICS для составных корреляций между колонками. Это - стандартный путь, который команда из Stripe (Brandur Leach в публикациях на brandur.org) описывает примерно теми же словами для своего workload.

Важно: при обновлении PostgreSQL статистика сбрасывается. После апгрейда обязательно запустите ANALYZE на всех таблицах. Это одна из самых частых причин «после апгрейда всё стало медленнее»: причина не в новой версии, а в отсутствующей статистике, которую планировщик заменил pessimistic-оценками.

Индексы: когда сканировать, когда читать последовательно

B-tree индексы

Основной тип индекса в PostgreSQL. Только листья хранят ссылки на строки. Узлы хранят наименьший ключ следующего уровня. Сложность поиска - O(log n). B-tree - дефолт по историческим причинам: он подходит почти для всех запросов на равенство и диапазон, дёшев в обновлении и хорошо поддаётся параллельному сканированию. Кроме B-tree в PostgreSQL есть GIN (для массивов, JSONB и полнотекстового поиска), GiST и SP-GiST (для геоданных и кастомных типов), BRIN (для огромных, но физически упорядоченных таблиц - например, append-only логов), и Hash (редко используется, имеет смысл только для запросов на чистое равенство и не реплицируется в физической репликации до версии 10).

Виды индексов по применению

Составные индексы

CREATE INDEX idx_name ON account (last_name, first_name, middle_name);

Будет использоваться для запросов по last_name, last_name + first_name, last_name + first_name + middle_name. Но не будет использоваться для запросов только по first_name. Markus Winand называет это «leftmost prefix rule» и подробно разбирает в use-the-index-luke.com - правило применимо ко всем СУБД с B-tree индексами, не только к PostgreSQL.

Индексы по выражениям

-- Обычный индекс не поможет для lower()
SELECT * FROM account WHERE lower(last_name) = 'smith';

-- Нужен индекс по выражению
CREATE INDEX idx_lower_name ON account (lower(last_name));

Это - один из самых частых источников «индекс есть, но не используется»: разработчик добавляет регистронезависимый поиск, но не понимает, что обычный индекс по last_name здесь бесполезен. Альтернатива - тип citext, который хранит данные регистронезависимо на уровне типа, но он имеет свои compromises (сложнее с сортировками, не поддерживает все операторы B-tree).

Покрывающие индексы

CREATE INDEX idx_name ON account (last_name) INCLUDE (phone, email);

Дополнительные колонки в INCLUDE позволяют выполнить Index Only Scan без обращения к таблице. INCLUDE-clause появилась в PostgreSQL 11 (2018) и часто незаслуженно игнорируется: это один из самых дешёвых способов превратить тёплый запрос в «чтение только из индекса», особенно для list/dashboard-запросов с известным небольшим набором возвращаемых полей.

Частичные индексы

CREATE INDEX idx_active ON orders (created_at) WHERE status = 'pending';

Индексирует только часть таблицы - меньше размер, выше селективность. Классический сценарий - активные сущности на огромной append-only таблице: индекс по WHERE status IN ('active', 'pending') может оказаться в десятки раз меньше полного индекса и при этом покрывать 95% продакшен-запросов.

Когда индексы невыгодны

Цена записи на каждый дополнительный индекс - реальная: каждое UPDATE по индексированной колонке требует обновления соответствующего B-tree, а HOT-обновление (Heap-Only Tuple, оптимизация с PostgreSQL 8.3) работает только для UPDATE-ов колонок, не входящих в индексы. Из этого вытекает практическое правило: если на таблице больше 6-8 индексов, и она write-heavy, скорее всего часть индексов лишняя - проверьте через pg_stat_all_indexes, какие из них реально используются.

Читаем EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE order_date >= '2024-01-01';

EXPLAIN - инструмент диагностики номер один. Без него любой разговор об оптимизации - угадывание. Markus Winand в «SQL Performance Explained» формулирует это так: разработчик, который не умеет читать EXPLAIN, не может оптимизировать запросы; он может только пробовать. Опция ANALYZE заставляет действительно выполнить запрос (а не только спланировать) и собрать реальные времена и количества строк; BUFFERS добавляет статистику по cache hits и disk reads. Эта пара флагов - то, что нужно по умолчанию для любой серьёзной диагностики; EXPLAIN без ANALYZE показывает только то, что планировщик думает, что произойдёт, и эти оценки могут быть драматически неверными.

Ключевые метрики

Метрика Описание
cost=0.00..1000.00 Startup cost..Total cost (оценка)
rows=50000 Ожидаемое количество строк (оценка)
actual time=0.012..15.034 Реальное время выполнения в мс
actual rows=48000 Реальное количество строк
loops=1 Сколько раз выполнялся узел
Buffers: shared hit=100 read=50 Страницы из кеша / с диска

На что обращать внимание

Главный диагностический жест - сравнение оценочных rows и фактических actual rows. Если планировщик думал, что вернётся 100 строк, а вернулось 100 000, он, скорее всего, выбрал Nested Loop (оптимальный для маленьких outer-сторон) на массиве, для которого нужно было Hash Join, и запрос будет тормозить в сотни раз. Pganalyze (pganalyze.com/blog) построила свой бизнес именно вокруг этой одной проблемы: автоматизированный анализ pg_stat_statements и EXPLAIN-планов, который ловит расхождения между планом и реальностью раньше, чем они становятся инцидентом. Их публичный блог - один из лучших источников реальных кейсов того, как cost mismatch выглядит в production.

Полезный приём для длинных EXPLAIN-планов - визуализаторы. explain.depesz.com Hubert Lubaczewski показывает план с подсветкой узлов по доле времени; pgmustard.com Michael Christofides и Paweł Zabczyk даёт ещё и автоматические рекомендации. Оба - стандартный инструментарий PostgreSQL-сообщества. На длинном плане с десятком JOIN-ов глаз быстро теряется в текстовом выводе; визуализатор сразу показывает, в каком узле сидит большая часть времени.

Оптимизация коротких запросов

Короткие запросы (OLTP) - используют небольшое количество данных. Цель: минимизировать количество чтений через индексы. Большая часть production-нагрузки в типичном веб-приложении - именно такие запросы: «найти пользователя по email», «вернуть последние 20 заказов клиента», «обновить статус документа». Они критичны для latency, и неправильно индексированный короткий запрос отзывается p99 на всех endpoint-ах сразу.

Стратегия

  1. Найти самый ограничивающий критерий в запросе
  2. Проверить, есть ли индекс для этого критерия
  3. Рассмотреть составной индекс для нескольких условий
  4. Добавить покрывающие колонки для Index Only Scan

Мониторинг использования индексов

SELECT relname, idx_scan, seq_scan, idx_tup_read
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

Следите за неиспользуемыми индексами - они замедляют запись без пользы. На горячей OLTP-таблице каждый лишний индекс - это дополнительные write-amplification и дополнительная работа autovacuum. Brandur Leach в постах на brandur.org о Stripe-инфраструктуре описывает культуру, которую команда Stripe выстроила вокруг pg_stat_statements: дашборд с топ-N запросов по cumulative time, регулярный обзор и явный owner для каждого медленного запроса. Это - применимый паттерн любой компании, которая хочет не доводить ситуацию до production-инцидента.

Оптимизация длинных запросов

Длинные запросы (OLAP) - высокая селективность, читают значительную часть таблиц. Аналитика, отчёты, ETL-выгрузки. Их характеристики противоположны OLTP: latency важнее throughput, индексы менее полезны (если процент выборки большой, Seq Scan побеждает), параллельное выполнение и hash-операции дают самый большой выигрыш.

Стратегии

CTE: материализация vs инлайн

С PostgreSQL 12 CTE по умолчанию могут инлайниться. Для контроля используйте: 

-- Принудительная материализация
WITH cte AS MATERIALIZED (
  SELECT * FROM large_table WHERE condition
)
SELECT * FROM cte WHERE other_condition;

-- Принудительный инлайн
WITH cte AS NOT MATERIALIZED (
  SELECT * FROM large_table WHERE condition
)
SELECT * FROM cte WHERE other_condition;

До PostgreSQL 12 CTE были «optimization fence»: планировщик не мог пробрасывать предикаты внутрь CTE, и материализация была единственным поведением. Это привело к огромному количеству легаси-кода, где CTE использовали как императивные шаги, и поведение менялось при апгрейде на 12+. Старое поведение по умолчанию доступно через MATERIALIZED; новое (более похожее на VIEW) - через NOT MATERIALIZED. Robert Haas в обсуждениях этого изменения подчёркивал, что цель была вернуть планировщику свободу выбора - и это, опять же, та самая идея Селинджер: декларативный SQL даёт оптимизатору пространство, императивный кодом отбирает.

Материализованные представления

Для тяжёлых аналитических запросов рассмотрите Materialized Views: 

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT product_id, SUM(amount) AS total
FROM sales
WHERE sale_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY product_id;

-- Обновление (можно по расписанию)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

Materialized views - это в некотором смысле кэш с явным контролем инвалидации. REFRESH MATERIALIZED VIEW CONCURRENTLY (PostgreSQL 9.4+) умеет обновлять представление без блокировки чтения, что делает их пригодными для аналитических дашбордов с приемлемым лагом. Альтернатива - инкрементальное обновление через триггеры на исходных таблицах, но это уже значительно сложнее в эксплуатации, и большинство команд предпочитают full refresh раз в N минут.

Антипаттерны и где их искать

Большинство «странных» планов в проде укладывается в небольшой набор повторяющихся паттернов. Каждый из них обычно проявляется одинаково в EXPLAIN ANALYZE, имеет понятную причину и понятное лечение. Перечислим явно, потому что распознавание этих паттернов - больше половины оптимизационной работы.

Антипаттерн 1: Seq Scan на большой таблице, у которой есть индекс. Планировщик выбрал Seq Scan, хотя индекс по фильтру существует. Две частые причины: статистика устарела и планировщик думает, что выборка больше, чем на самом деле; либо random_page_cost завышен для вашего железа, и Index Scan кажется дороже. Лечение: ANALYZE на таблице, потом - проверка random_page_cost (на NVMe значение 1.1 обосновано).

Антипаттерн 2: Nested Loop на широких таблицах. Планировщик выбрал Nested Loop, потому что оценка outer-стороны была мала (10 строк), а реально вернулось 100 000 - и для каждой из них пошёл повторный Index Scan. actual rows × loops в EXPLAIN сразу показывает масштаб бедствия. Лечение - сначала исправить оценку (статистика, расширенная статистика по составным колонкам через CREATE STATISTICS), потом, если нужно, переписать запрос так, чтобы планировщик увидел Hash Join.

Антипаттерн 3: ORM-генерируемые N+1 запросы. Не проблема планировщика, а проблема приложения, но именно она съедает большую часть p99 в типичном Rails/Django/Hibernate-проекте. Один SELECT * FROM users WHERE id = ?, выполненный 500 раз в цикле, в EXPLAIN выглядит идеально (Index Scan на PK, миллисекунды), а на проде убивает latency. Диагностика - pg_stat_statements показывает аномально высокий calls на таком запросе; Bullet (Rails), django-debug-toolbar (Django), Hibernate BatchSize annotations (Hibernate) - стандартные инструменты обнаружения. Лечение - eager loading на уровне ORM, не оптимизация SQL.

Антипаттерн 4: устаревшая или отсутствующая статистика. Autovacuum выключен, или не успевает на горячей таблице, или default_statistics_target = 100 не отлавливает редкие, но важные значения. Симптом - драматическое расхождение rows и actual rows. Лечение - ANALYZE, повышение default_statistics_target для конкретных колонок (через ALTER TABLE ... ALTER COLUMN ... SET STATISTICS), CREATE STATISTICS для составных корреляций.

Антипаттерн 5: SELECT * на широких таблицах. Возвращает все колонки, в том числе TOAST-хранилища (большие BLOB и JSONB), которых вы не запрашивали. Это убивает Index Only Scan (потому что не все колонки в индексе) и забивает сеть. Лечение - явно перечислить нужные колонки. Markus Winand на use-the-index-luke.com подробно разбирает, почему SELECT * - это не «удобство», а «отказ от половины оптимизаций индексов».

Антипаттерн 6: LIKE '%pattern%' вместо LIKE 'pattern%'. Префиксный поиск ('pattern%') использует обычный B-tree индекс. Поиск с wildcard в начале - не использует. Если нужен реальный полнотекстовый поиск, GIN-индекс по tsvector или pg_trgm (trigram) - правильное решение. Trigram-индексы особенно хороши для подстрочного поиска и fuzzy match: CREATE INDEX ON table USING gin (col gin_trgm_ops) - и LIKE '%pattern%' начинает использовать индекс.

Антипаттерн 7: функция в WHERE без функционального индекса. WHERE lower(email) = ? или WHERE date_trunc('day', created_at) = ? не использует обычные индексы. Лечение - индекс по выражению (см. выше) или хранение нормализованного значения в отдельной колонке.

Антипаттерн 8: ROW_NUMBER() OVER (...) для top-1. Часто видится в коде, портированном из других СУБД: «получить последнюю запись по ключу через оконную функцию». В PostgreSQL почти всегда быстрее ORDER BY ... LIMIT 1 с правильным индексом или DISTINCT ON. Оконная функция требует сортировки всего набора; LIMIT 1 на правильно индексированной колонке - O(log n).

Антипаттерн 9: effective_cache_size и work_mem по умолчанию на большом железе. effective_cache_size по умолчанию - 4 GB, и это сообщает планировщику, какой объём индексов может находиться в OS page cache. На сервере с 128 GB RAM реальное значение - 90-100 GB. Заниженный effective_cache_size заставляет планировщик предпочитать Seq Scan, потому что он «думает», что индекс не помещается в память. Аналогично work_mem = 4 MB вынуждает sort и hash операции уходить на диск, что в десятки раз медленнее. Утилита pgtune (pgtune.leopard.in.ua) даёт разумную стартовую точку под железо; дальше - тюнинг под конкретный workload.

Антипаттерн 10: OFFSET N LIMIT M для глубокой пагинации. OFFSET 100000 заставляет PostgreSQL сначала прочитать 100 000 строк, потом отбросить их. Это линейная сложность по offset. Для глубокой пагинации правильное решение - keyset pagination: WHERE created_at < ? ORDER BY created_at DESC LIMIT 20, с курсором по последнему виденному значению. Markus Winand на use-the-index-luke.com посвятил этому одну из самых популярных страниц сайта - и это паттерн, который должен знать каждый, кто пишет API с пагинацией.

Простой тест диагностики: возьмите свой топ-10 запросов из pg_stat_statements по cumulative time, прогоните каждый через EXPLAIN (ANALYZE, BUFFERS), и посмотрите на каждый из десяти антипаттернов выше. На production-системе средней зрелости почти всегда найдётся 2-3 совпадения, и каждое - это шанс на кратное ускорение без изменения архитектуры.

Чек-лист оптимизации

Перед оптимизацией

Анализ плана

Типичные решения

Проблема Решение
Seq Scan вместо Index Scan Создать индекс, проверить селективность, проверить random_page_cost
Ошибки в оценке rows ANALYZE, увеличить default_statistics_target, CREATE STATISTICS
Медленная сортировка Увеличить work_mem, создать индекс по сортируемой колонке
Nested Loop на больших данных Добавить индекс на ключ соединения, исправить оценку cardinality
Индекс не используется для функции Создать индекс по выражению
N+1 от ORM Eager loading на уровне приложения, не на уровне SQL
Глубокая пагинация по OFFSET Keyset pagination с курсором
Связанные материалы:

Источники и смежные статьи