Selecting an article body can rule out an index-only scan: PostgreSQL must retrieve a column the index does not contain. This guide shows how to diagnose that change on a Mac and measure its effect in Node.js. Index-only scan requirements.

Why does changing the selected columns change the plan?

Consider an article table with id, category and body, plus a B-tree index on (category, id). A query selecting IDs by category can obtain both its filter value and its result from that index. Adding body makes that index insufficient for an index-only scan. PostgreSQL indexes are separate from the table’s main storage, called the heap; an ordinary index scan retrieves table data too. Index-Only Scans and Covering Indexes.

The planner now compares ways to retrieve the required table rows. It might retain an Index Scan, combine a Bitmap Index Scan with a Bitmap Heap Scan, or choose a Seq Scan. Bitmap scans group heap accesses by physical location. When retrieving many dispersed rows, scanning the table can be cheaper than fetching them separately. A bitmap plan still uses an index even though its top scan node mentions the heap. Using EXPLAIN.

Also check the original plan carefully. An Index Only Scan can report nonzero Heap Fetches: PostgreSQL sometimes needs to visit table rows to establish visibility. Avoiding those visits depends on the table’s visibility map, as well as having the required columns in the index. Visibility checks for index-only scans.

Reproduce the comparison from your Mac

Use a running local PostgreSQL database and an installed, currently supported Node.js release with npm. The baseline example uses features available across PostgreSQL 14–18; the retrieved current PostgreSQL manual identifies itself as version 18. The additional serialization diagnostic below requires PostgreSQL 17 or later. The node-postgres documentation describes its Node.js compatibility and installation command. PostgreSQL documentation, node-postgres installation and compatibility.

Open Terminal and work in a new folder. These commands create the project and install the database driver:

mkdir article-plan-lab
cd article-plan-lab
npm init -y
npm install pg

The program uses your existing local connection settings through PGHOST, PGPORT, PGDATABASE and PGUSER. Configure them for a database you can already connect to. node-postgres reads these environment variables when constructing a Client without explicit connection options. Connecting with environment variables.

Save the following complete program as article-plan.cjs. It creates synthetic rows in a temporary table, builds an index, prints both execution plans, and then measures both ordinary queries. Both comparisons use the same connection and category value. The query configuration supplies SQL through text and the parameter through values, following the documented node-postgres API. Query configuration.

const { Client } = require('pg');
const { performance } = require('node:perf_hooks');

async function main() {
  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_plan_demo (
      id integer PRIMARY KEY,
      category integer NOT NULL,
      body text NOT NULL
    )`);
    await client.query(`INSERT INTO article_plan_demo
      SELECT g, g % 10, repeat('Synthetic article paragraph. ', 40)
      FROM generate_series(1, 50000) AS s(g)`);
    await client.query(`CREATE INDEX article_plan_demo_category_id
      ON article_plan_demo (category, id)`);
    await client.query('VACUUM (ANALYZE) article_plan_demo');

    const queries = [
      ['IDs', 'SELECT id FROM article_plan_demo WHERE category = $1'],
      ['Bodies', 'SELECT id, body FROM article_plan_demo WHERE category = $1']
    ];

    for (const [label, text] of queries) {
      const plan = await client.query({
        text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ${text}`,
        values: [3]
      });
      console.log(label);
      console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));

      const start = performance.now();
      const result = await client.query({ text, values: [3] });
      const elapsed = performance.now() - start;
      const bodyBytes = result.rows.reduce((sum, row) =>
        sum + (row.body === undefined ? 0 : Buffer.byteLength(row.body)), 0);
      console.log({
        rows: result.rows.length,
        elapsedMs: elapsed.toFixed(2),
        bodyBytes
      });
    }
  } finally {
    await client.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

Run it from the same folder:

node article-plan.cjs

The setup sends VACUUM (ANALYZE) as its own command. That command performs vacuuming followed by statistics collection and cannot run inside a transaction block. Keep it outside any transaction wrapper you add to the example. VACUUM.

The JavaScript passed a syntax check, and a separate deterministic calculation confirmed the fixture’s row count and body-byte totals. PostgreSQL was unavailable in the research environment, so these database queries were inspected but not executed here. No particular plan or runtime is presented as an observed result.

Read the plans in a useful order

Start with the scan names, then compare estimated and actual rows. Next inspect table access and buffer activity. This sequence separates a change in available access methods from an inaccurate estimate or an expensive result. VERBOSE also exposes each node’s output columns. Using EXPLAIN, EXPLAIN options.

Observation Interpretation Next check
IDs use Index Only Scan; bodies use Index Scan The body query needs table data Compare buffers and ordinary-query time
Bodies use Bitmap Heap Scan Inspect its child for index use Compare heap blocks and rows returned
Bodies use Seq Scan The planner prefers scanning the table Compare estimated with actual matching rows
IDs show substantial Heap Fetches Index-only execution still visits the heap Check whether this reflects recent table changes
Estimates differ substantially from actual rows Statistics may not describe the data well Collect statistics and compare again

ANALYZE collects statistics that the planner uses to choose plans. If you change the synthetic category distribution, collect statistics again before interpreting the next comparison. Sampling means estimates can vary slightly between runs. ANALYZE.

Treat width as estimated output bytes per row, rather than a measurement of bytes transferred. Likewise, planner costs use relative units rather than milliseconds. Buffer hits indicate blocks found in PostgreSQL’s cache; they do not mean that no work occurred. Plan estimates, Buffer reporting.

For this fixture, both queries should return 5,000 rows. Each synthetic body contains 1,160 ASCII bytes, so the body query returns 5,800,000 body bytes, about 5.53 MiB, before IDs and protocol overhead. That is a calculation from the sample inputs, not a database performance measurement. The program’s bodyBytes field lets you verify that distinction.

The ordinary query runs after its plan measurement, so its elapsed time reflects that warmed execution order. Repeat comparisons and alternate the order before drawing conclusions from small timing differences. A single run is useful for diagnosis, but it does not establish a reliable performance ratio.

Why can fetching bodies take longer than EXPLAIN suggests?

Large text values may be compressed or stored outside the main table through PostgreSQL’s TOAST mechanism. Retrieving a row’s main entry and producing its complete body for the client can therefore involve different amounts of work. The short, repetitive fixture above is intended to demonstrate column coverage; it does not represent every body-storage pattern. TOAST storage.

By default, EXPLAIN ANALYZE discards query results without converting them for delivery. On PostgreSQL 17 and later, add SERIALIZE TEXT to investigate output conversion, including fetching out-of-line values when necessary. PostgreSQL 17 introduced this option. EXPLAIN serialization behavior, PostgreSQL 17 release notes.

To try it, replace the program’s existing EXPLAIN template line with this fragment and rerun the whole program:

text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SERIALIZE TEXT) ${text}`,

On PostgreSQL 14–16, retain the original line and use the ordinary-query timer to measure receiving the result. SERIALIZE still does not send result data to the client, so it cannot measure transmission or JavaScript processing. EXPLAIN limits.

node-postgres normally collects result rows into JavaScript objects. Its rowMode: 'array' option changes the result representation; it does not remove selected body values from the SQL result. Consequently, it cannot restore the column coverage lost when body is added to this query. Row mode.

Choose a change that matches the article task

If the desktop task needs only a list of IDs, keep the projection narrow. If it needs full article text, evaluate the body query’s actual elapsed time and result size. The diagnostic goal is to identify unnecessary work while preserving the requested result.

For a screen that displays only the first 50 articles in a category, limiting the requested result is a meaningful alternative. In the program’s queries array, replace the body query string with this SQL:

SELECT id, body
FROM article_plan_demo
WHERE category = $1
ORDER BY id
LIMIT 50

This deliberately changes the task from retrieving every matching article to retrieving one ordered page. With category fixed, the existing (category, id) index provides a candidate path in ID order. A matching B-tree index can make ORDER BY with LIMIT efficient because execution can stop after enough rows are found. The chosen plan remains something to inspect. Indexes and ORDER BY.

Avoid treating INCLUDE (body) as an automatic repair. Included columns duplicate table data, enlarge the index, and can make insertion fail if an index tuple exceeds its size limit. Small payload columns can be useful candidates for measured experiments. INCLUDE was introduced in PostgreSQL 11; it is unavailable on earlier versions. Covering-index cautions, PostgreSQL 11 release notes.

Finish the investigation with three concrete findings: whether selecting bodies removed index-only eligibility, whether the new scan performs acceptably, and how much time the full result takes to reach Node.js. Those findings tell you whether to keep the plan, request fewer articles, or test a different index for the actual query.

Sources