LIMIT 10 caps the result, but finding ten distinct values can require thousands of input rows. This guide shows how to diagnose that work and compare two approaches using synthetic article topics from your Mac’s Terminal.

Why ten results can require thousands of rows

Suppose an article table contains 100,000 rows across 100 topics, with 1,000 articles per topic. Your task is to retrieve the first ten different topic names alphabetically. The query needs to eliminate repeated names, preserve topic order, and return ten results.

PostgreSQL defines DISTINCT as removing duplicate output rows and LIMIT as restricting the returned result. These semantics do not impose a ten-row budget on the underlying scan. The planner considers the limit when choosing a plan, so the physical work depends on the chosen execution strategy. SELECT

Consider an ordered stream containing 1,000 occurrences of topic-001, followed by 1,000 of topic-002, and so on. A streaming duplicate-removal operation sees the tenth distinct topic only after consuming all nine preceding groups and the first row of the tenth group:

Input rows needed = (9 × 1,000) + 1 = 9,001
Distinct results returned = 10

This is an illustrative calculation for that particular streaming strategy, not a measured PostgreSQL plan. Uneven groups change the calculation: a very large first topic group can dominate the work even when later groups are small.

A matching B-tree index can provide sorted input without an explicit sort. PostgreSQL documents early retrieval for indexed ORDER BY with LIMIT, but duplicate elimination adds another requirement: enough different values must reach the limit node. An index can remove sorting work while leaving substantial duplicate-processing work. Indexes and ORDER BY

Read the plan to identify what consumes the rows

Use EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) on the actual query and parameter values. ANALYZE executes the statement and reports actual row counts; BUFFERS reports block activity. The complete example below runs this diagnostic on synthetic data. EXPLAIN

Two useful plan patterns are shown below. They are schematic shapes, not captured output or promises about the plan your installation will choose.

Ordered streaming path:
Limit
  Unique
    Index Only Scan or Index Scan

Aggregation path:
Limit
  Sort
    HashAggregate
      Seq Scan

In the ordered path, compare the scan’s output with the output of Unique and Limit. A large difference indicates duplicate-heavy input. In the aggregation path, the aggregate consumes its input to establish the groups before the final ordering and limiting can finish. PostgreSQL’s plan documentation explains how scans feed sorting and aggregation nodes. Using EXPLAIN

Read actual rows alongside loops: row counts are averages per execution when a node runs repeatedly. Multiply them by the loop count when estimating total output from that node. Also inspect the input below a sort; a small number of rows emitted by the sort does not establish that its input was small. Using EXPLAIN

Keep three measurements separate: rows emitted by a node, block activity, and elapsed time. Buffer counts describe blocks rather than logical rows, and parent buffer totals include their children. Temporary tables use local buffers; temporary working files used by operations such as sorts appear as temp blocks. EXPLAIN

Reproduce the comparison from your Mac

Use a current Node.js LTS release, npm, and an existing local PostgreSQL database. This walkthrough uses PostgreSQL 18 as its documentation baseline. The program prints your server version so you can interpret the results against that version’s documentation. node-postgres documents installation with npm install pg and support for current and LTS Node.js releases. node-postgres

In Terminal, create a working directory and install the driver:

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

Save the following complete program as distinct-demo.mjs. It creates synthetic article rows in a temporary table, measures the ordinary query before and after adding an index, and compares an explicit successor-search query. It uses one Client throughout so every query runs in the same database session.

Connection settings come from the usual PostgreSQL environment variables. Configure those for your existing local database before running the program. node-postgres documents both environment-based connections and passing query values separately from SQL text. 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();
}

The data generator produces integers from 1 through 100,000 using PostgreSQL’s documented generate_series function. The arithmetic assigns each consecutive group of 1,000 articles the same topic. ANALYZE supplies table statistics that the planner uses when selecting a plan. Set Returning Functions, ANALYZE

Run the program, using the database name of your existing local database. For example, if that database is named postgres:

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

The expected topic names are topic-001 through topic-010; the assertions check the result count and agreement between the two queries. The JavaScript syntax and the 9,001-input streaming calculation were checked for this article. Database execution was unavailable in the research environment, so timings and captured PostgreSQL plans are intentionally omitted.

Why an index-only scan may still do substantial work

An Index Only Scan means the query can obtain its requested column values from the index. It does not mean one index entry per distinct result. Inspect its actual row count even if the table itself is rarely visited.

Heap access is a separate consideration. PostgreSQL checks the visibility map and may still visit heap tuples when the corresponding pages are not marked all-visible. Consequently, the freshly inserted demonstration rows may produce heap fetches even under an index-only plan. Check Heap Fetches alongside scan rows. Index-Only Scans and Covering Indexes

Adding extra payload columns with INCLUDE can make additional values available to an index-only scan, but it does not change the number of distinct topics requested. Choose index coverage to address demonstrated heap-access work; measure duplicate consumption separately. Index-Only Scans and Covering Indexes

Does PostgreSQL 18 skip scan solve this automatically?

PostgreSQL 18 introduced B-tree skip scan support. Its documented purpose is to improve some multicolumn-index searches when constraints on later columns can guide repeated searches across earlier-column values. That feature was introduced in version 18; do not assume it exists on PostgreSQL 17 or earlier. Release 18, Multicolumn Indexes

Automatically jumping from one distinct leading key to the next is commonly called a loose index scan. The PostgreSQL project wiki distinguishes this operation from predicate-oriented index skip scan and documents recursive SQL emulation. PostgreSQL 18’s skip scan announcement therefore does not establish automatic loose scanning for this single-column DISTINCT query. Loose indexscan

The program’s successor-search query adapts that emulation: find the smallest topic, then repeatedly find the smallest topic greater than the previous one. The recursive step stops after ten results or when no successor exists. With a suitable index, each search can seek beyond the preceding duplicate group. Compare its actual plan and execution time with the ordinary query rather than assuming a speedup. Loose indexscan

This example deliberately requires non-null topics, ascending order, the declared C collation, and a positive limit. A zero limit would still return the recursive anchor, so a generalized implementation must handle zero separately. Nullable topics need explicit treatment because a greater-than comparison will not enumerate nulls. If you add article filters, apply the same conditions to both the initial search and every successor search; otherwise the rewritten query can return a different set.

Choose the next change from the evidence

Use this decision aid after collecting the plans. It separates work that an ordering index can remove from work caused by repeated topic values.

Observed result What to investigate next
A scan feeds aggregation and a final sort Compare the existing plan with a B-tree index matching the topic ordering.
An ordered scan emits thousands of rows for ten topics Measure duplicate-group sizes and compare explicit successor searches.
An index-only scan reports many heap fetches Investigate visibility and heap-access work separately from duplicate counts.
Estimates differ substantially from actual rows Refresh statistics with ANALYZE, then measure again.
The indexed ordinary query is already inexpensive Keep the simpler query unless another measured requirement justifies a rewrite.

Avoid moving LIMIT 10 into a subquery that selects ten raw articles before applying DISTINCT. In the sample ordering, those ten articles all belong to topic-001, so the rewritten query returns one topic instead of ten. This changes the task.

Also keep the distinct key precise. Adding article_id to the selected columns makes each article a different output row, defeating topic deduplication. If the task changes to choosing one representative article per topic, PostgreSQL’s DISTINCT ON supports that operation, with its expressions matching the leftmost ordering expressions. It does not itself promise fewer scanned rows. SELECT

For the first ten distinct article topics, start by comparing the scan’s actual output with the ten returned results. Then test the matching index and, where duplicate groups dominate, the bounded successor-search query. Preserve the requested topic set and ordering while choosing the approach whose measured work fits your data.

Sources