Сравнивайте планы PostgreSQL, сопоставляя повторные замеры времени выполнения с количеством буферов в корневом узле. В этом руководстве для Mac Terminal используются синтетические строки статей, чтобы проверить, снижает ли индекс нагрузку на базу данных, учитывая влияние кэша, из-за которого один запуск может ввести в заблуждение.

Что следует измерять вместе?

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

Используйте EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON). Параметр ANALYZE выполняет запрос; TIMING OFF отключает измерение времени для отдельных узлов, сохраняя общее время выполнения оператора. Машиночитаемый вывод упрощает сбор повторных замеров. PostgreSQL: EXPLAIN

Рассматривайте эти измерения вместе:

Измерение Что оно показывает
Execution Time Продолжительность выполнения на сервере без учета планирования
Planning Time Время, затраченное на построение и оптимизацию плана
Shared Hit Blocks Обращения к буферам, обслуженные из общего кэша PostgreSQL
Shared Read Blocks Блоки, загруженные в общие буферы
Сумма попаданий и чтений общих блоков Полезная величина для сравнения объема обращений к общим буферам
Temp Read Blocks и Temp Written Blocks Активность временных рабочих файлов, например сброс сортировки на диск при нехватке памяти

Общие блоки относятся к обычным таблицам и индексам. Локальные блоки относятся к временным таблицам и индексам; блоки временных рабочих файлов — отдельная категория. Родительский узел включает активность буферов дочерних узлов, поэтому для итоговых показателей выполнения используйте корневой узел, а дочерние узлы проверяйте, чтобы определить, где выполнялась работа. Никогда не суммируйте значения всех узлов. PostgreSQL: EXPLAIN

Количество буферов отражает неуникальные обращения: повторное обращение к блоку может учитываться снова. Кроме того, оценки стоимости планировщика выражены в условных единицах, а не в миллисекундах. Сравнивайте фактическое время выполнения с фактическими показателями буферов; оценки стоимости используйте для понимания выбора планировщика. PostgreSQL: Using EXPLAIN

Чтение общего блока не доказывает, что устройство хранения вашего Mac выполнило физическое чтение. Статистика ввода-вывода PostgreSQL не позволяет отличить данные, полученные с устройства хранения, от данных, уже находившихся в кэше страниц операционной системы. Описывайте это измерение как чтение общих буферов, если у вас нет отдельных данных о работе устройства хранения. PostgreSQL: The Cumulative Statistics System

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

Для начала нужна работающая локальная база данных PostgreSQL и Node.js с доступным в Terminal npm. Это руководство рассчитано на PostgreSQL 18 и Node.js 22 или 24. В документации node-postgres указано, что эти версии Node.js совместимы, а установка выполняется командой npm install pg. node-postgres

Команда сравнения также есть в документации PostgreSQL 16 и 17. В PostgreSQL 18 вывод буферов автоматически включается при использовании EXPLAIN ANALYZE; явное указание BUFFERS делает намерение понятным для всех этих версий. Не предполагайте, что в старых серверах доступны новые необязательные параметры. PostgreSQL 16: EXPLAIN, PostgreSQL 17: EXPLAIN, PostgreSQL 18 release notes

Создайте новую рабочую директорию и установите драйвер:

mkdir pg-plan-comparison
cd pg-plan-comparison
npm init -y
npm install pg

Используйте существующую локальную тестовую базу данных, к которой можно подключиться обычным для вас способом. В скрипте используется new Client(), считывающий те же переменные окружения подключения, что и psql. Например, если база данных называется plan_lab, запустите скрипт с помощью PGDATABASE=plan_lab. При необходимости добавьте существующие параметры PGHOST или PGPORT. node-postgres: Connecting

Программа создает 100 000 синтетических статей внутри транзакции, сравнивает два состояния схемы и откатывает транзакцию. Она использует обычную таблицу, чтобы сканирование учитывалось в общих буферах. В документации PostgreSQL указано, что ROLLBACK отбрасывает изменения транзакции. Если таблица с демонстрационным именем уже существует, создание завершится ошибкой, а не заменит ее. PostgreSQL: ROLLBACK

Выполните полное сравнение до и после

Сохраните следующую полную программу в файл compare-plans.mjs. Она генерирует данные самостоятельно, выполняет один неучитываемый прогрев для каждой фазы, сохраняет семь измеренных запусков и выводит первый измеренный план каждой фазы. Для генерации строк используется документированная функция PostgreSQL generate_series. PostgreSQL: Set Returning Functions

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const query = `
  SELECT article_id, title, published_at
  FROM plan_compare_articles_demo
  WHERE category_id = $1
  ORDER BY published_at DESC, article_id DESC
  LIMIT $2
`;
const values = [7, 50];
const explain = `
  EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)
  ${query}
`;
const median = values =>
  [...values].sort((a, b) => a - b)[Math.floor(values.length / 2)];

async function measure(label) {
  await client.query(explain, values);
  const runs = [];
  let firstPlan;
  for (let run = 1; run <= 7; run++) {
    const result = await client.query(explain, values);
    const document = result.rows[0]['QUERY PLAN'][0];
    const root = document.Plan;
    firstPlan ??= document;
    const hits = root['Shared Hit Blocks'] ?? 0;
    const reads = root['Shared Read Blocks'] ?? 0;
    runs.push({
      phase: label,
      run,
      execution_ms: document['Execution Time'],
      planning_ms: document['Planning Time'],
      shared_hit: hits,
      shared_read: reads,
      shared_accesses: hits + reads,
      temp_read: root['Temp Read Blocks'] ?? 0,
      temp_written: root['Temp Written Blocks'] ?? 0
    });
  }
  console.table(runs);
  console.log(label, 'median execution ms:',
    median(runs.map(run => run.execution_ms)));
  console.dir(firstPlan, { depth: null });
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows[0]);
  await client.query('BEGIN');
  await client.query(`
    CREATE TABLE plan_compare_articles_demo (
      article_id integer PRIMARY KEY,
      category_id integer NOT NULL,
      title text NOT NULL,
      published_at timestamp NOT NULL,
      body text NOT NULL
    )
  `);
  await client.query(`
    INSERT INTO plan_compare_articles_demo
      (article_id, category_id, title, published_at, body)
    SELECT g, g % 20, 'Article ' || g,
           timestamp '2025-01-01' + g * interval '1 minute',
           repeat('Synthetic article text. ', 12)
    FROM generate_series(1, 100000) AS series(g)
  `);
  await client.query('ANALYZE plan_compare_articles_demo');
  const before = await client.query(query, values);
  await measure('before index');

  await client.query(`
    CREATE INDEX plan_compare_articles_demo_list_idx
    ON plan_compare_articles_demo
      (category_id, published_at DESC, article_id DESC)
  `);
  const after = await client.query(query, values);
  if (JSON.stringify(before.rows) !== JSON.stringify(after.rows)) {
    throw new Error('Result mismatch between schema states');
  }
  await measure('after index');
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  try {
    await client.query('ROLLBACK');
  } finally {
    await client.end();
  }
}

Запустите программу из той же директории, подставив фактическое имя тестовой базы данных:

PGDATABASE=plan_lab node compare-plans.mjs

Программа передает SQL и значения параметров отдельно через client.query(text, values). Она считывает план из возвращенного столбца с именем QUERY PLAN; node-postgres сопоставляет столбцы результата со свойствами объекта и разбирает JSON PostgreSQL в объекты JavaScript. node-postgres: Queries, node-postgres: pg.Result, node-postgres: Data Types

Отдельная команда ANALYZE собирает статистику таблицы для планировщика. Она отличается от параметра ANALYZE внутри EXPLAIN, который измеряет выполнение. В данном случае сбор статистики после загрузки синтетических строк дает обеим фазам одинаковую исходную информацию о таблице. PostgreSQL: ANALYZE

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

Что должно измениться в двух планах?

В предлагаемом индексе сначала идет категория, по которой выполняется фильтрация на равенство, а за ней — столбцы требуемой сортировки. В документации PostgreSQL указано, что условия равенства для первых столбцов B-tree ограничивают сканируемый диапазон индекса. Поэтому этот индекс — обоснованный кандидат для данного запроса. PostgreSQL: Multicolumn Indexes

До индекса ожидайте увидеть сканирование и сортировку, а после — упорядоченное сканирование индекса под ограничением количества строк. Это предположения, которые нужно проверить по своему выводу, а не гарантированная форма плана. Подходящий индекс B-tree может сразу возвращать строки в нужном порядке, а сочетание сортировки с ограничением количества строк позволяет не обрабатывать остальные совпадения. PostgreSQL: Indexes and ORDER BY

Проверьте в выведенных планах три вещи:

  1. Убедитесь, что корневой узел возвращает по 50 строк в обеих фазах и программа не сообщает о расхождении результатов.
  2. Найдите узлы, отвечающие за сканирование и сортировку. Сравните оценку количества строк с фактическим значением и проверьте, где возникает активность буферов.
  3. Сравните обращения к общим буферам корневого узла и повторные замеры времени выполнения. Одно лишь изменение имен узлов не свидетельствует о полезном улучшении.

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

Для более тщательной проверки повторите весь эксперимент и протестируйте другие категории и ограничения количества строк. Если проверяете более высокое ограничение, обозначьте его как отдельную нагрузку. Исходное сравнение отвечает на вопрос, помогает ли этот индекс обработать конкретный запрос на 50 статей в наблюдавшихся локальных условиях.

Как отличить снижение нагрузки от прогрева буферов?

Следующие числа приведены для примера и не являются результатами реального запуска. Они показывают, почему чтения, попадания и время нужно рассматривать вместе:

Иллюстративный запуск Выполнение Попадания в общие буферы Чтения общих буферов Попадания + чтения
A, начальное наблюдение 12 мс 900 100 1 000
A, повторное наблюдение 10 мс 1 000 0 1 000
B, план-кандидат 3 мс 80 0 80

Между двумя наблюдениями A чтения исчезают, а общее число обращений к общим буферам остается равным 1 000. Это согласуется с изменением состояния кэша, но не демонстрирует снижение объема обращений к общим буферам. В случае B меньше и продолжительность, и число обращений. Если эта закономерность повторяется при эквивалентных результатах, можно сделать более узкий вывод: для этой задачи B выполняет меньше работы с общими буферами.

Если ваши результаты отличаются, воспользуйтесь этой таблицей:

Наблюдаемая картина Интерпретация и дальнейшие действия
Меньше времени и обращений к общим буферам Признак улучшения для тестируемой нагрузки; повторите замеры, чтобы проверить стабильность
Меньше времени, меньше чтений, но сходное общее число обращений Изменение может объясняться состоянием кэша; сравните повторные запуски с прогретым кэшем
Меньше обращений, но столько же или больше времени Прежде чем делать выбор, проверьте сортировку, выражения и разброс времени
Ноль чтений, но много попаданий При выполнении из кэша обращения к буферам все равно происходят; проверьте узлы с высокой активностью
Появились временные чтения или записи Прежде чем менять настройки, проверьте, какая операция с рабочими данными их вызвала

Не следует принимать процент попаданий в кэш за решающий показатель. В примере для прогретого A и B доля попаданий в общие буферы составляет 100%, но число обращений заметно различается. Сохраняйте исходные счетчики и размер результата вместе с замерами времени.

Как эти измерения связаны со скоростью приложения?

Execution Time на сервере не включает планирование, а обычный EXPLAIN ANALYZE, кроме того, не учитывает передачу результата и по умолчанию — преобразование вывода. Инструментирование само по себе создает дополнительные накладные расходы. Поэтому скрипт сравнивает измеренное выполнение в базе данных, а не полное время отображения списка статей. PostgreSQL: Using EXPLAIN

Чтобы отдельно исследовать преобразование вывода в PostgreSQL 17 или 18, добавьте SERIALIZE TEXT к тем же параметрам объяснения. В PostgreSQL 16 эта опция не указана. Сериализация все равно не передает строки результата запроса, поэтому измерить доставку по сети с ее помощью нельзя. Во всех сравниваемых запусках используйте одинаковые параметры сериализации. PostgreSQL 17: EXPLAIN, PostgreSQL 16: EXPLAIN

Для измерения на стороне приложения замеряйте время обычного вызова драйвера для статьи SELECT до завершения ожидаемого асинхронного вызова; к этому моменту соединение уже должно быть установлено. Записывайте это как отдельный клиентский замер прошедшего времени. Сохраняйте фактическую конфигурацию запросов приложения: добавление параметра name в запрос node-postgres включает поведение подготовленных выражений для соединения и меняет условия сравнения. node-postgres: Queries

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

Источники