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

Это руководство предназначено для разработчика, работающего на Mac и которому нужно объяснить медленный запрос к статьям: выбрать статьи-кандидаты один раз, а затем найти наибольший ID статьи-кандидата в пределах темы каждой статьи. Везде используются синтетические строки. Цель — определить, где работа повторяется, и выбрать форму запроса, сохраняющую результат.

Подготовьте воспроизводимый запрос к набору кандидатов

Используйте уже установленный локально PostgreSQL, где доступен psql. Откройте Terminal, запустите psql с привычными параметрами подключения и оставьте этот сеанс открытым на время всех примеров SQL. В документации PostgreSQL psql описан как интерактивный терминал для отправки запросов и просмотра результатов. psql

Сначала проверьте версию сервера командой SHOW server_version;. В актуальной документации, использованной для этой статьи, текущей указана PostgreSQL 18. В примерах используется синтаксис PostgreSQL 12 или новее; это нижняя граница совместимости функции, а не рекомендация использовать старую версию. В PostgreSQL 12 появились встраивание CTE и управляющие конструкции MATERIALIZED и NOT MATERIALIZED. Запросы WITH, Версия 12

Выполните эту настройку один раз в новом сеансе. Она создаёт 20 000 синтетических статей и индекс для поиска по теме и ID. Предикат кандидатов, используемый ниже, выбирает первые 2 000 статей.

CREATE TEMP TABLE article_demo (
    id integer PRIMARY KEY,
    topic_id integer NOT NULL,
    title text NOT NULL
);

INSERT INTO article_demo (id, topic_id, title)
SELECT g, 1 + (g % 50), 'Article ' || g
FROM generate_series(1, 20000) AS s(g);

CREATE INDEX article_demo_topic_id_idx
    ON article_demo (topic_id, id);

ANALYZE article_demo;

По умолчанию эти временные таблицы существуют только в течение сеанса. После загрузки явно выполните ANALYZE: процесс autovacuum PostgreSQL не анализирует временные таблицы. CREATE TABLE

Теперь зафиксируйте исходный вариант. Подзапрос LATERAL вычисляет наибольший ID кандидата для темы текущей статьи. Здесь «наибольший ID» — это просто заданное условие задачи; оно не подразумевает дату публикации.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate AS x
    WHERE x.topic_id = c.topic_id
) AS m;

Явный MATERIALIZED чётко обозначает границу повторного использования в эксперименте. Материализованный CTE можно построить один раз, а его потребители могут многократно читать сохранённые строки. В документации PostgreSQL показано, как материализованный CTE с несколькими ссылками лишает поиск в промежуточном результате преимуществ индексов исходной таблицы. Материализация общих табличных выражений

Примеры SQL проверены, но для этой статьи не выполнялись на сервере PostgreSQL. Арифметика числа синтетических строк проверена отдельно; времена выполнения и планы запросов не приводятся как результаты измерений.

Найдите повторяющуюся работу в плане

Начните с ветви, формирующей кандидатов, и проследите за каждым потребителем. При вложенном цикле определите внешний вход и внутренний поиск. PostgreSQL показывает loops как число выполнений узла; фактическое количество строк и время узла — это средние значения на одно выполнение. В варианте с коррелированным скалярным подзапросом повторяющаяся работа может отображаться в SubPlan, поэтому при диагностике нужно проследить за выполняемыми узлами, а не требовать наличия метки определённого типа. Использование EXPLAIN

Зафиксируйте четыре наблюдения из фактического плана:

  1. Сколько строк поступает на внешний вход поиска?
  2. Сколько раз выполняется базовое внутреннее сканирование?
  3. Сканирование читает кандидатов, а затем отбрасывает большинство из них с помощью фильтра?
  4. Есть ли узел Memoize между внешними строками и затратной внутренней операцией?

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

В этом конкретном примере 2 000 кандидатов относятся к 50 темам, по 40 статей на тему. Расчёт ниже описывает возможные схемы работы, а не гарантированный вывод плана или время выполнения:

Схема обработки Иллюстративное число просмотров строк-кандидатов
Полное сканирование кандидатов для каждой внешней статьи 2 000 × 2 000 = 4 000 000
Полное сканирование один раз на тему, а все последующие поиски обслуживаются из кэша 50 × 2 000 = 100 000
Однократная группировка кандидатов по теме 2 000 строк на входе шага группировки плюс последующая работа соединения

Это различие важно для PostgreSQL 14 и новее, где появилась мемоизация результатов внутренних вложенных циклов. В актуальной документации Memoize описан как узел, кэширующий результаты параметризованного сканирования и позволяющий пропускать базовое сканирование при попадании в кэш; записи также могут вытесняться. Прежде чем считать, что тысячи внешних строк вызвали тысячи полных сканирований, проверьте ключ кэша, а также число попаданий, промахов, вытеснений и переполнений, если эти показатели приведены. Версия 14, Планирование запросов

Также сравните оценочное число строк с фактическим и найдите самое раннее существенное расхождение. PostgreSQL рекомендует проверять эти оценки и предупреждает, что планы для крошечных таблиц не обязательно позволяют судить о поведении на больших наборах данных. Не складывайте время родительских и дочерних узлов: оно описывает пересекающуюся работу. Использование EXPLAIN

Выберите переписывание под повторяющуюся задачу

Полезно выяснить, какая часть набора кандидатов нужна каждому потребителю. Используйте эту таблицу, чтобы выбрать первый эксперимент:

Наблюдаемое требование Первый эксперимент Условие сохранения результата
Для каждого кандидата нужен одинаковый максимум по теме Сгруппировать один раз, затем выполнить соединение Одна сводная строка на тему
Каждому потребителю нужна небольшая часть источника, доступная через индекс Попробовать NOT MATERIALIZED Сохранить полный предикат кандидатов
Нескольким операторам требуется одна и та же явно индексированная таблица кандидатов Создать временную таблицу с индексом Использовать тот же сеанс и учитывать затраты на настройку
Повторяющиеся параметры уже обеспечивают много попаданий в кэш Сравнить общее время выполнения до изменения формы запроса Убедиться, что базовые сканирования действительно затратны

Вычислите результат по каждой теме один раз

Для этой задачи сводка с группировкой напрямую выражает повторное использование. Выполните следующий запрос после настройки в том же сеансе:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
), topic_max AS (
    SELECT topic_id, max(id) AS highest_id
    FROM candidate
    GROUP BY topic_id
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
JOIN topic_max AS m USING (topic_id);

Это переписывание вычисляет максимум для каждой темы и присоединяет его ко всем статьям-кандидатам. PostgreSQL определяет max как максимальное ненулевое входное значение. Поскольку в примере столбцы темы и ID не содержат NULL, эквивалентность очевидна: каждый кандидат принадлежит группе, в которую входит он сам. Агрегатные функции

При запуске запроса без EXPLAIN ожидайте 2 000 строк результата. Например, статья 1 относится к теме 2, и для неё будет указано highest_id = 1951; для темы 1 будет указано highest_id = 2000. Эти значения однозначно следуют из генератора примера.

Перед обобщением проверьте смысл результата. Группировка по дополнительным столбцам может создать несколько сводных строк на тему и увеличить число строк результата соединения. Если разрешить NULL в идентификаторах тем, нужно заново проверить семантику сравнения. Если требуется название статьи с наибольшим ID, нужно сделать дополнительный шаг — соединить максимум с исходными строками-кандидатами; отдельное вычисление максимума для названия ответило бы на другой вопрос.

Переписывание допускает другой план, но не требует использования hash join и не гарантирует ускорения. Сравните измеренные затраты на группировку и соединение с фактическим числом выполнений внутреннего узла в исходном варианте.

Проверьте, могут ли индексы источника заменить сканирование промежуточного результата

Если CTE подходит для этого, замените AS MATERIALIZED в полном исходном запросе на AS NOT MATERIALIZED, а затем выполните его повторно. Так можно оптимизировать CTE совместно с исходным отношением, возможно заменив сканирование сохранённых кандидатов поиском в источнике. Это также может привести к повторным вычислениям набора кандидатов. Материализация общих табличных выражений

Индекс (topic_id, id) в примере соответствует условию равенства по ведущему столбцу темы и ограничению по столбцу ID. Поэтому его стоит проверить: убедитесь, использует ли его фактический план и сколько данных он сканирует. В документации PostgreSQL указано, что равенство по ведущему столбцу составного индекса B-tree и неравенство по следующему столбцу ограничивают область сканирования. Составные индексы

Сохраните id <= 2000 в определении кандидатов. Поиск максимума среди всех 20 000 строк источника изменил бы ответ. Также соблюдайте документированное ограничение: для рекурсивных CTE и CTE, не являющихся свободными от побочных эффектов, NOT MATERIALIZED игнорируется. Для CTE, на которые оно не распространяется, используйте поддерживаемое реляционное переписывание или явно сохраните результат во временной таблице. SELECT

Создайте индексированную таблицу кандидатов, если повторное использование охватывает несколько операторов

CTE действует только в пределах одного оператора. Если нескольким операторам нужны одни и те же строки-кандидаты и явные индексы, временная таблица позволяет задать другой срок жизни и добавить индексы. Выполните эту альтернативу после исходной настройки:

CREATE TEMP TABLE candidate_demo AS
SELECT id, topic_id, title
FROM article_demo
WHERE id <= 2000;

CREATE INDEX candidate_demo_topic_id_idx
    ON candidate_demo (topic_id, id);

ANALYZE candidate_demo;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT c.id, c.title, m.highest_id
FROM candidate_demo AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate_demo AS x
    WHERE x.topic_id = c.topic_id
) AS m;

Индексы временных таблиц тоже временные, а сама таблица существует только в рамках сеанса. Оценивая, помогает ли такой способ, учитывайте создание таблицы, построение индекса и анализ. Не сравнивайте только заключительный поиск с полной операцией без промежуточной таблицы. CREATE TABLE

Зафиксируйте тот же эксперимент на Mac с node-postgres

Чтобы проверить путь выполнения запроса в приложении, используйте поддерживаемую версию Node.js и установите пакет согласно документации командой npm install pg. Сохраните приведённую ниже программу целиком в файле diagnose.mjs в проекте и выполните node diagnose.mjs. Программа сама создаёт входные синтетические данные, поэтому не зависит от предыдущего сеанса psql. node-postgres

Программа использует имеющиеся переменные окружения для подключения к PostgreSQL. В документации node-postgres указаны те же переменные, что и для psql, в том числе PGHOST, PGPORT и PGDATABASE. Подключение

import pg from 'pg';
const { Client } = pg;
const client = new Client();

try {
  await client.connect();
  const version = await client.query('SHOW server_version');
  console.log(version.rows[0]);

  await client.query(`
    CREATE TEMP TABLE article_demo (
      id integer PRIMARY KEY,
      topic_id integer NOT NULL,
      title text NOT NULL
    );
    INSERT INTO article_demo (id, topic_id, title)
    SELECT g, 1 + (g % 50), 'Article ' || g
    FROM generate_series(1, 20000) AS s(g);
    CREATE INDEX article_demo_topic_id_idx
      ON article_demo (topic_id, id);
    ANALYZE article_demo;
  `);

  const sql = `
    WITH candidate AS MATERIALIZED (
      SELECT id, topic_id, title
      FROM article_demo
      WHERE id <= $1::integer
    )
    SELECT c.id, c.title, m.highest_id
    FROM candidate AS c
    CROSS JOIN LATERAL (
      SELECT max(x.id) AS highest_id
      FROM candidate AS x
      WHERE x.topic_id = c.topic_id
    ) AS m
  `;

  const result = await client.query({
    text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) ${sql}`,
    values: [2000]
  });

  for (const row of result.rows) {
    console.log(row['QUERY PLAN']);
  }
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await client.end();
}

Конфигурация запроса использует документированные поля text и values. Добавление name создаёт подготовленный оператор на каждом соединении; это не кэширует строки-кандидаты и не устраняет повторные выполнения внутреннего узла. Запросы

Если существующее приложение использует пул, создавайте временную таблицу и обращайтесь к ней через один и тот же полученный из пула клиент, а затем освобождайте его в finally. Разные вызовы pool.query могут использовать разные клиенты. Кроме того, node-postgres требует, чтобы все операторы транзакции выполнялись через один экземпляр клиента. Пулы соединений, Транзакции

Если поведение именованных операторов зависит от значений параметров, отдельно изучите планирование. PostgreSQL может выбирать общий план или план, составленный с учётом конкретных параметров; документированный способ его проверки для подготовленного оператора — EXPLAIN EXECUTE. Поэтому новый запрос без имени, проверенный через EXPLAIN, может выполняться иначе, чем уже используемый подготовленный оператор. PREPARE

Определите, уменьшилась ли повторяющаяся работа на самом деле

Для каждого сравнения используйте одинаковые предикат кандидатов, выбранные столбцы и входные значения. Сначала выполните запрос, затем повторите сопоставимые запуски, чтобы увидеть влияние кэша. Запишите версию сервера, число кандидатов, число выполнений внутреннего сканирования, поведение кэша и общее время выполнения. В PostgreSQL 17 улучшилось планирование CTE с использованием статистики столбцов и порядка сортировки, поэтому при сравнении результатов с разных машин фиксируйте версию. Версия 17

Для второго диагностического прохода EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) сохраняет число строк и время выполнения оператора, не замеряя время каждого узла по отдельности. Итоговые показатели буферов для родительских узлов включают работу дочерних узлов, поэтому не складывайте значения для всего дерева. EXPLAIN ANALYZE выполняет запрос, но не измеряет время передачи его строк результата в Node.js. EXPLAIN

Используйте настройки планировщика только как эксперимент. Отключение enable_nestloop делает вложенные циклы менее предпочтительными, но не может полностью их исключить; enable_material также не может запретить всю материализацию. Ни одна настройка не подтверждает, что обработка кандидатов стала эффективной. Планирование запросов

В итоге выберите форму запроса, которая сохраняет требуемые строки и уменьшает измеренную работу для всей задачи. Для поиска максимума по каждой теме группировка с повторным использованием — самый очевидный первый эксперимент. Оставьте индексированные поиски, если они оказываются недорогими, а явно сохраняйте данные во временной таблице, если дополнительная настройка окупается при повторном использовании.

Источники