PostgreSQL может использовать индекс для id < 11 и последовательное сканирование для id > 11, потому что первое условие соответствует очень небольшому числу строк, а второе — почти всей таблице. Оба оператора поддерживают B-деревья; планировщик сравнивает расчётную стоимость вариантов.

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

Почему один и тот же индекс может приводить к разным решениям о сканировании?

Индекс B-дерева поддерживает сравнения как «меньше», так и «больше». Изменение направления сравнения не делает индекс непригодным. Меняется диапазон значений, а значит, и ожидаемое планировщиком количество строк. PostgreSQL: Index Types

Рассмотрим иллюстративную таблицу ровно со 100 000 строк статей и последовательными целочисленными идентификаторами от 1 до 100 000:

Предикат Совпадающих строк Доля таблицы
id < 11 10 0.01%
id > 11 99,989 99.989%
id = 11 1 0.001%

Эти значения рассчитаны по синтетическим данным, а не получены при измерении существующей базы данных. Ни один из диапазонов не включает идентификатор 11. Индекс позволяет эффективно найти десять подходящих идентификаторов в первом диапазоне. Во втором диапазоне в любом случае пришлось бы найти почти все строки.

При обычном сканировании индекса PostgreSQL извлекает записи из индекса, а соответствующие строки — из основного хранилища таблицы, называемого кучей. Эти строки кучи могут быть распределены по множеству страниц. Когда нужны почти все страницы, чтение таблицы напрямую может обойтись дешевле, чем обход индекса и получение строк, на которые он ссылается. PostgreSQL: Index-Only Scans and Covering Indexes

Поэтому число 11 не имеет особого значения для планировщика. При других данных условие «больше» могло бы задавать узкий диапазон. PostgreSQL оценивает долю совпадающих строк, называемую селективностью, используя статистику таблицы. Одно лишь числовое значение границы не определяет эту долю. PostgreSQL: Statistics Used by the Planner

Как воспроизвести сравнение на Mac?

Вам понадобятся действующее подключение к PostgreSQL и доступная в Terminal на Mac команда psql. Установка не входит в это упражнение. Откройте обычное подключение к базе данных с помощью psql: эта команда принимает параметры подключения и предоставляет интерактивную командную строку SQL. Выполните следующие блоки в одном новом сеансе. PostgreSQL: psql

Настройка создаёт временную таблицу, содержащую только синтетический текст статей. Временные таблицы исчезают после завершения сеанса. Обычный первичный ключ с целочисленным типом автоматически создаёт уникальный индекс B-дерева, поэтому второй индекс для id не нужен. PostgreSQL: CREATE TABLE

SHOW server_version;

CREATE TEMP TABLE article_scan_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL
);

INSERT INTO article_scan_demo (id, title, body)
SELECT
    g,
    'Article ' || g,
    repeat('Sample article text. ', 20)
FROM generate_series(1, 100000) AS s(g);

ANALYZE article_scan_demo;

SELECT
    count(*) AS total_rows,
    count(*) FILTER (WHERE id < 11) AS below_11,
    count(*) FILTER (WHERE id > 11) AS above_11
FROM article_scan_demo;

Целочисленная функция PostgreSQL generate_series по умолчанию включает обе конечные точки и использует шаг 1. Поэтому запрос подсчёта должен вернуть в общей сложности 100 000 строк, 10 строк с идентификаторами меньше 11 и 99 989 строк с идентификаторами больше 11. PostgreSQL: Set Returning Functions

После загрузки строк выполните ANALYZE, чтобы у планировщика появилась статистика заполненной таблицы. Эта команда собирает статистику и служит другой цели, чем параметр измерения выполнения в EXPLAIN ANALYZE. PostgreSQL: ANALYZE

Затем сравните два запроса, выбирающих все столбцы:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id < 11;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

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

При обычных настройках для узкого диапазона ожидается план с использованием индекса, а для широкого — последовательное сканирование. Считайте это ожидаемым результатом, а не гарантией: исходя из оценок PostgreSQL может выбрать план с битовой картой или другой подходящий план. SQL-код этого примера был проверен, но при подготовке не было доступно сервера PostgreSQL для его выполнения; арифметика количества строк была проверена отдельно.

Какие части плана объясняют это решение?

Начните с узла сканирования, его расчётного значения rows и фактического значения rows. Для первого запроса ищите Index Scan с Index Cond или Bitmap Index Scan под узлом Bitmap Heap Scan. Для второго ищите Seq Scan с Filter. План с битовой картой тоже использует индекс: он сначала упорядочивает местоположения подходящих строк, а затем получает страницы кучи. PostgreSQL: Using EXPLAIN

Затем проверьте следующее:

Наблюдение в этом примере Что проверить
Оценки примерно совпадают с фактическими 10 и 99 989 строками Разница в количестве совпадений объясняет разные варианты.
Расчётное количество строк сильно отличается от фактического Обновите статистику, прежде чем менять настройки планировщика.
На значительно меньшей выборке для обоих предикатов выполняется последовательное сканирование Возможно, прямое сканирование дешевле из-за небольшого размера таблицы.
Появляется план с битовой картой Сравните оценки количества строк и объём работы с кучей как для другого варианта использования индекса.

Стоимость планировщика выражается в условных единицах, а не в миллисекундах. Рассматривайте её в рамках модели PostgreSQL: стоимость 100 не означает длительность выполнения. Поскольку в примере используются временные таблицы, операции с буферами учитываются среди локальных блоков; буферы обычных таблиц и индексов — среди общих блоков. PostgreSQL: EXPLAIN

Если оценки выглядят неожиданно, выполните в том же сеансе этот диагностический запрос:

SELECT
    schemaname,
    n_distinct,
    histogram_bounds,
    correlation
FROM pg_stats
WHERE schemaname = (
    SELECT nspname
    FROM pg_namespace
    WHERE oid = pg_my_temp_schema()
)
  AND tablename = 'article_scan_demo'
  AND attname = 'id';

Гистограмма описывает распределение значений, используемое для оценки диапазонов. Корреляция показывает, насколько логический порядок столбца соответствует физическому порядку строк; значения у крайних границ диапазона могут снижать расчётную стоимость доступа к куче через индекс. Ни одно из этих полей не задаёт универсальную долю, при достижении которой PostgreSQL обязан переключить тип сканирования. PostgreSQL: pg_stats

Как сравнить запросы через node-postgres?

node-postgres передаёт PostgreSQL текст запроса и значения параметров; планирование выполняется на стороне PostgreSQL. API запросов принимает сначала SQL-код, а затем массив значений, поэтому для обоих сравнений можно использовать одну и ту же граничную величину 11. node-postgres: Queries

Для независимого воспроизведения на Node.js нужна рабочая установка Node.js/npm и существующие параметры подключения к PostgreSQL. В новой папке проекта выполните npm init -y, затем npm install pg. Сохраните приведённую ниже программу целиком в файл compare-scans.mjs и запустите node compare-scans.mjs. Клиент использует переменные окружения PostgreSQL, например PGHOST, PGPORT и PGDATABASE; задайте параметры для своей существующей локальной базы данных. node-postgres: Connecting

import pg from 'pg';

const client = new pg.Client();

try {
  await client.connect();

  await client.query(`
    CREATE TEMP TABLE article_scan_demo (
      id integer PRIMARY KEY,
      title text NOT NULL,
      body text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO article_scan_demo (id, title, body)
    SELECT
      g,
      'Article ' || g,
      repeat('Sample article text. ', 20)
    FROM generate_series(1, 100000) AS s(g)
  `);

  await client.query('ANALYZE article_scan_demo');

  const queries = [
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id < $1',
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id > $1'
  ];

  for (const text of queries) {
    const result = await client.query(text, [11]);
    console.log(text);
    console.log(
      result.rows.map(row => row['QUERY PLAN']).join('\n')
    );
  }
} finally {
  await client.end();
}

Программа создаёт собственную тестовую таблицу и выполняет все операции через одно подключение. Она выводит оба плана, а затем закрывает подключение. Синтаксис JavaScript был проверен с помощью Node.js; подключение к базе и выполнение программы при подготовке не проверялись. Если подключение не удаётся настроить, сначала решите эту проблему и только потом анализируйте поведение сканирования.

Могут ли именованные подготовленные операторы изменить сравнение?

Добавление name в конфигурацию запроса node-postgres создаёт именованный подготовленный оператор для каждого подключения. Это добавляет ещё одно различие: PostgreSQL может использовать пользовательский план, построенный с учётом значений параметров, или общий план, используемый при разных выполнениях. node-postgres: Queries, PostgreSQL: PREPARE

Согласно автоматической политике по умолчанию в PostgreSQL 18, при первых пяти выполнениях используются пользовательские планы. Затем PostgreSQL сравнивает расчётную стоимость общего плана со средней стоимостью пользовательских планов; автоматического переключения на общий план при шестом выполнении не происходит. PostgreSQL: PREPARE

Если именованный оператор приложения работает иначе, чем тест с буквальными значениями в SQL, проверьте этот оператор на том же подключении с помощью EXPLAIN EXECUTE. Общий план отображает символы параметров, а пользовательский подставляет переданные значения. Храните два оператора сравнения отдельно, поскольку тексты их SQL-запросов различаются. PostgreSQL: PREPARE

Когда следует менять запрос или настройки планировщика?

В первую очередь сохраните фактическую задачу запроса. Замена SELECT * на SELECT id меняет набор данных, который должен получить PostgreSQL. Если подходящий индекс содержит все нужные столбцы, становится возможным сканирование только индекса. Однако отсутствие обращений к куче также зависит от карты видимости, поэтому план со сканированием только индекса не гарантирует нулевого доступа к куче. PostgreSQL: Index-Only Scans and Covering Indexes

Если задаче нужны все подходящие статьи, сохраните это требование при сравнении планов. Если требуется лишь небольшая упорядоченная выборка, проверьте фактический запрос с ORDER BY и LIMIT: упорядочивание и возможность остановить поиск раньше могут изменить предпочтительный план. PostgreSQL: Using EXPLAIN

Проверив статистику, можно сравнить альтернативный план в текущем SQL-сеансе:

BEGIN;
SET LOCAL enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

ROLLBACK;

Эта настройка не поощряет последовательное сканирование, но полностью запретить его не может. Используйте результат для диагностического сравнения. PostgreSQL также моделирует последовательный и непоследовательный доступ к страницам с помощью seq_page_cost и random_page_cost. Изменение их относительных значений может повлиять на выбор плана, однако документация рекомендует оценивать настройки стоимости на всей нагрузке запросов, а не по нескольким экспериментам. PostgreSQL: Query Planning

Для этой синтетической таблицы решающее различие — 10 подходящих строк против 99 989. Проверьте точный запрос, обновите статистику, сравните расчётное и фактическое количество строк, а при необходимости изучите поведение подготовленных планов. Это поможет определить, соответствует ли последовательное сканирование объёму работы, и даст основания для дальнейших изменений.

Источники