LIMIT 10 ограничивает результат, но для поиска десяти различных значений могут потребоваться тысячи входных строк. В этом руководстве показано, как диагностировать эту работу и сравнить два подхода на синтетических темах статей из Terminal на Mac.

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

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

PostgreSQL определяет DISTINCT как удаление повторяющихся строк результата, а LIMIT — как ограничение возвращаемого результата. Эта семантика не устанавливает лимит в десять строк для базового сканирования. Планировщик учитывает ограничение при выборе плана, поэтому фактический объём работы зависит от выбранной стратегии выполнения. SELECT

Рассмотрим упорядоченный поток из 1 000 вхождений topic-001, за которыми следуют 1 000 вхождений topic-002 и так далее. При потоковом удалении дубликатов десятая уникальная тема обнаружится только после обработки всех девяти предшествующих групп и первой строки десятой группы:

Необходимое количество входных строк = (9 × 1 000) + 1 = 9 001
Возвращено уникальных результатов = 10

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

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

Изучите план, чтобы определить, где обрабатываются строки

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

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

Упорядоченный потоковый путь:
Limit
  Unique
    Index Only Scan или Index Scan

Путь с агрегацией:
Limit
  Sort
    HashAggregate
      Seq Scan

Для упорядоченного пути сравните выход сканирования с выходом Unique и Limit. Большая разница указывает на множество дубликатов во входных данных. При агрегации узел обрабатывает входные данные, чтобы определить группы, прежде чем завершатся итоговая сортировка и ограничение. В документации PostgreSQL по планам объясняется, как сканирование передаёт данные узлам сортировки и агрегации. Using EXPLAIN

Читайте фактическое количество строк вместе с loops: если узел выполняется многократно, число строк указано в среднем на одно выполнение. Чтобы оценить общий объём его выходных данных, умножьте это число на количество циклов. Также изучите данные на входе сортировки: небольшое число строк на выходе сортировки не означает, что и на входе их было мало. Using EXPLAIN

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

Воспроизведите сравнение на Mac

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

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

mkdir distinct-row-demo
cd distinct-row-demo
npm init -y
npm install pg

Сохраните следующую полную программу в файле distinct-demo.mjs. Она создаёт синтетические строки статей во временной таблице, измеряет обычный запрос до и после добавления индекса и сравнивает его с явным запросом поиска следующего значения. Для всех запросов используется один Client, поэтому они выполняются в рамках одного сеанса базы данных.

Параметры подключения берутся из стандартных переменных среды PostgreSQL. Перед запуском программы настройте их для своей существующей локальной базы данных. В документации node-postgres описаны как подключения через переменные среды, так и передача значений запроса отдельно от текста SQL. Connecting, Queries

import pg from 'pg';
import assert from 'node:assert/strict';

const { Client } = pg;
const client = new Client({ connectionTimeoutMillis: 5000 });
const limit = 10;

const distinctSql = `
  SELECT DISTINCT topic FROM demo_articles
  ORDER BY topic LIMIT $1::integer
`;

const seekSql = `
  WITH RECURSIVE topics(topic, step) AS (
    (SELECT topic, 1 FROM demo_articles ORDER BY topic LIMIT 1)
    UNION ALL
    SELECT n.topic, t.step + 1
    FROM topics AS t
    CROSS JOIN LATERAL (
      SELECT a.topic FROM demo_articles AS a
      WHERE a.topic > t.topic ORDER BY a.topic LIMIT 1
    ) AS n
    WHERE t.step < $1::integer
  )
  SELECT topic FROM topics ORDER BY topic
`;

async function showPlan(label, sql) {
  const plan = await client.query(
    `EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) ${sql}`, [limit]
  );
  console.log(`\n${label}`);
  console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));
}

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

  await client.query(`
    CREATE TEMP TABLE demo_articles (
      article_id integer NOT NULL,
      topic text COLLATE "C" NOT NULL,
      title text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO demo_articles (article_id, topic, title)
    SELECT n,
           'topic-' || lpad((((n - 1) / 1000) + 1)::text, 3, '0'),
           'Sample article ' || n::text
    FROM generate_series(1, 100000) AS s(n)
  `);

  await client.query('ANALYZE demo_articles');
  await showPlan('Before index', distinctSql);

  await client.query(
    'CREATE INDEX demo_articles_topic_idx ON demo_articles (topic)'
  );
  await client.query('ANALYZE demo_articles');
  await showPlan('After index', distinctSql);
  await showPlan('Successor seeks', seekSql);

  const ordinary = await client.query(distinctSql, [limit]);
  const seeking = await client.query(seekSql, [limit]);
  assert.deepEqual(seeking.rows, ordinary.rows);
  assert.equal(ordinary.rows.length, limit);
  console.log(ordinary.rows.map(row => row.topic));
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

Генератор данных создаёт целые числа от 1 до 100 000 с помощью документированной функции PostgreSQL generate_series. Арифметика распределяет одинаковую тему для каждой последовательной группы из 1 000 статей. ANALYZE предоставляет статистику таблицы, которую планировщик использует при выборе плана. Set Returning Functions, ANALYZE

Запустите программу, указав имя существующей локальной базы данных. Например, если она называется postgres:

PGHOST=localhost PGPORT=5432 PGDATABASE=postgres node distinct-demo.mjs

Ожидаемые названия тем — от topic-001 до topic-010; проверки подтверждают количество результатов и совпадение результатов обоих запросов. Для этой статьи проверены синтаксис JavaScript и потоковый расчёт с 9 001 входной строкой. В исследовательской среде запуск базы данных был недоступен, поэтому время выполнения и зафиксированные планы PostgreSQL намеренно не приводятся.

Почему Index Only Scan всё ещё может выполнять значительный объём работы

Index Only Scan означает, что запрос может получить значения запрошенного столбца из индекса. Это не означает, что на каждый уникальный результат приходится одна запись индекса. Проверяйте фактическое количество строк сканирования, даже если к самой таблице обращаются редко.

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

Добавление дополнительных столбцов с данными через INCLUDE может сделать дополнительные значения доступными для индексного сканирования, но не меняет количество запрошенных уникальных тем. Выбирайте покрытие индекса для устранения подтверждённой работы по доступу к куче; обработку дубликатов измеряйте отдельно. Index-Only Scans and Covering Indexes

Решит ли эту задачу автоматически skip scan в PostgreSQL 18?

В PostgreSQL 18 появилась поддержка skip scan для B-tree. Согласно документации, её цель — улучшить некоторые поиски по многоколоночным индексам, когда ограничения на последующие столбцы позволяют выполнять повторные поиски по значениям предыдущих столбцов. Эта функция появилась в версии 18; не предполагайте, что она есть в PostgreSQL 17 и более ранних версиях. Release 18, Multicolumn Indexes

Автоматический переход от одного уникального значения ведущего ключа к следующему обычно называют loose index scan. Вики проекта PostgreSQL отличает эту операцию от ориентированного на условия отбора skip scan для индексов и описывает её эмуляцию с помощью рекурсивного SQL. Поэтому объявление о появлении skip scan в PostgreSQL 18 не доказывает, что для этого запроса с одним столбцом DISTINCT автоматически выполняется loose scan. Loose indexscan

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

В этом примере предполагается, что темы не содержат NULL, используется сортировка по возрастанию с указанной C сортировкой collation и задано положительное ограничение. При нулевом ограничении всё равно будет возвращена начальная строка рекурсивного запроса, поэтому в обобщённой реализации нулевое значение нужно обрабатывать отдельно. Значения NULL требуют явной обработки, потому что сравнение «больше» не перечислит их. Если вы добавляете фильтры по статьям, применяйте одинаковые условия и к начальному поиску, и к каждому поиску следующего значения; иначе переписанный запрос может вернуть другой набор результатов.

Выберите следующий шаг на основании данных

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

Наблюдаемый результат Что проверить дальше
Сканирование передаёт данные агрегации и итоговой сортировке Сравните текущий план с планом, использующим индекс B-tree, соответствующий порядку тем.
Упорядоченное сканирование выдаёт тысячи строк для десяти тем Измерьте размеры групп дубликатов и сравните результат с явным поиском следующих значений.
В Index Only Scan указано много извлечений из кучи Отдельно изучите видимость и доступ к куче, не смешивая их с подсчётом дубликатов.
Оценки существенно отличаются от фактического числа строк Обновите статистику с помощью ANALYZE, затем повторите измерение.
Обычный индексированный запрос уже выполняется недорого Оставьте более простой запрос, если только другое измеренное требование не оправдывает его переписывание.

Не переносите LIMIT 10 во вложенный запрос, который выбирает десять исходных статей до применения DISTINCT. В примере все эти десять статей относятся к topic-001, поэтому переписанный запрос вернёт одну тему вместо десяти. Это уже другая задача.

Также точно определите ключ уникальности. Добавление article_id к выбранным столбцам превращает каждую статью в отдельную строку результата и сводит на нет устранение дубликатов тем. Если задача меняется на выбор одной представительной статьи для каждой темы, PostgreSQL поддерживает такую операцию с помощью DISTINCT ON, выражения которого должны совпадать с крайними левыми выражениями сортировки. Сам по себе этот оператор не гарантирует, что будет просмотрено меньше строк. SELECT

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

Источники