Compare PostgreSQL plans by pairing repeated execution times with root-node buffer counts. This Mac Terminal walkthrough uses synthetic article rows to test whether an index reduces database work, while accounting for cache effects that can make one run misleading.

What should you measure together?

Your task is to compare the same article-list query before and after adding an index. Keep the selected columns, category, ordering, limit and data unchanged. Record both elapsed execution time and the buffer activity needed to produce the result.

Use EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON). The ANALYZE option executes the query; TIMING OFF disables individual-node clock measurements while retaining whole-statement execution time. Machine-readable output makes repeated measurements easier to collect. PostgreSQL: EXPLAIN

Read these measurements together:

Measurement What it tells you
Execution Time Server execution duration, excluding planning
Planning Time Time spent generating and optimizing the plan
Shared Hit Blocks Buffer accesses satisfied from PostgreSQL’s shared cache
Shared Read Blocks Blocks brought into shared buffers
Shared hits plus reads A useful comparison of shared-buffer access volume
Temp Read Blocks and Temp Written Blocks Temporary working-file activity, such as a sort spilling beyond memory

Shared blocks belong to regular tables and indexes. Local blocks belong to temporary tables and indexes; temporary working-file blocks are a separate category. A parent node includes its children’s buffer activity, so use the root node for execution totals and inspect children to locate the work. Never sum every node’s counts. PostgreSQL: EXPLAIN

Buffer counts represent non-distinct accesses: revisiting a block can count again. Also, planner costs are arbitrary units rather than milliseconds. Compare actual execution times with actual buffer counts; keep estimated costs for understanding the planner’s choice. PostgreSQL: Using EXPLAIN

A shared read does not prove that your Mac’s storage device performed a physical read. PostgreSQL’s I/O statistics cannot distinguish data fetched from storage from data already available in the operating system’s page cache. Describe the measurement as shared-buffer reads unless you have separate storage evidence. PostgreSQL: The Cumulative Statistics System

Prepare the Mac experiment and check version boundaries

Start with a running local PostgreSQL database and Node.js with npm available in Terminal. This walkthrough targets PostgreSQL 18 and uses Node.js 22 or 24. The node-postgres documentation lists those Node.js versions as compatible and documents installation with npm install pg. node-postgres

The comparison command also appears in PostgreSQL 16 and 17 documentation. PostgreSQL 18 automatically includes buffer output with EXPLAIN ANALYZE; spelling out BUFFERS keeps the intent explicit across these versions. Do not assume newer optional settings exist on older servers. PostgreSQL 16: EXPLAIN, PostgreSQL 17: EXPLAIN, PostgreSQL 18 release notes

In a new working directory, install the driver:

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

Use an existing local practice database that your usual PostgreSQL connection can open. The script uses new Client(), which reads the same connection environment variables as psql. For example, if that database is named plan_lab, run the script with PGDATABASE=plan_lab. Add your existing PGHOST or PGPORT settings if needed. node-postgres: Connecting

The program creates 100,000 synthetic articles inside a transaction, compares two schema states, and rolls the transaction back. It uses a regular table so the scan appears under shared buffers. PostgreSQL documents that ROLLBACK discards the transaction’s updates. If the demonstration table name already exists, creation fails instead of replacing it. PostgreSQL: ROLLBACK

Run a complete before-and-after comparison

Save the following complete program as compare-plans.mjs. It generates its own input, performs one unrecorded warm-up per phase, retains seven measured runs, and prints the first measured plan for each phase. The row generator uses PostgreSQL’s documented generate_series function. 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();
  }
}

Run it from the same directory, substituting your practice database’s actual name:

PGDATABASE=plan_lab node compare-plans.mjs

The program sends SQL and parameter values separately through client.query(text, values). It reads the plan from the returned column named QUERY PLAN; node-postgres maps result columns to object properties and parses PostgreSQL JSON into JavaScript objects. node-postgres: Queries, node-postgres: pg.Result, node-postgres: Data Types

The standalone ANALYZE command collects table statistics for the planner. It differs from the execution-measuring ANALYZE option inside EXPLAIN. Here, collecting statistics after loading the synthetic rows gives both phases the same starting information about the table. PostgreSQL: ANALYZE

The JavaScript median and buffer-total calculations were checked with deterministic synthetic inputs. The complete database experiment was not executed during preparation because PostgreSQL was unavailable in the research environment; the article supplies no measured benchmark results.

What should change in the two plans?

The candidate index puts the equality-filtered category first, followed by the requested ordering columns. PostgreSQL documents that equality conditions on leading B-tree columns constrain the scanned index range. That makes this index a reasoned candidate for the query. PostgreSQL: Multicolumn Indexes

Look for a scan and sort before indexing, then an ordered index scan beneath the limit afterward. These are expectations to check in your output, not promised plan shapes. A matching B-tree index can supply ordered rows directly, and an ordering combined with a limit can avoid processing the remaining matches. PostgreSQL: Indexes and ORDER BY

Inspect three things in the printed plans:

  1. Confirm the root returns 50 rows in both phases and that the program reports no result mismatch.
  2. Find the nodes responsible for scanning and sorting. Check their row estimates against actual rows and examine where buffer activity occurs.
  3. Compare root shared accesses and repeated execution times. A change in node names alone does not establish a useful improvement.

This is a phased, warm-cache comparison. Row insertion, the correctness queries, warm-ups and index creation all affect cache state. Keep the same Mac workload and database configuration during both phases. Preserve the seven individual observations so an unusually slow run remains visible; a median alone can conceal variation.

For a stronger follow-up, repeat the entire experiment and test other categories and limits. If you test a larger limit, label it as a separate workload. The original comparison answers whether this index helps the specific 50-article request under the observed local conditions.

How do you distinguish less work from warmer buffers?

The following numbers are illustrative inputs, not captured output. They show why reads, hits and time should stay together:

Illustrative run Execution Shared hits Shared reads Hits + reads
A, initial observation 12 ms 900 100 1,000
A, repeated observation 10 ms 1,000 0 1,000
B, candidate plan 3 ms 80 0 80

Between the two A observations, reads disappear while total shared accesses remain 1,000. That is consistent with a cache-state change; it does not demonstrate reduced shared-buffer access volume. B combines a shorter duration with fewer accesses. If that pattern repeats with equivalent results, it supports the narrower conclusion that B performs less shared-buffer work for this task.

Use this decision aid when your own results differ:

Observed pattern Interpretation and next action
Lower time and fewer shared accesses Evidence of improvement for the tested workload; repeat to check consistency
Lower time, fewer reads, similar total accesses Cache state may explain the change; compare warmed repetitions
Fewer accesses but similar or higher time Inspect sorting, expressions and timing variation before choosing
Zero reads but many hits Cached execution still performs buffer accesses; inspect high-activity nodes
Temporary reads or writes appear Inspect the working operation that produced them before changing settings

Avoid turning a cache-hit percentage into the deciding metric. In the illustration, both warmed A and B have a 100% shared-buffer hit rate, yet their access counts differ substantially. Keep the raw counts and the result size beside the timings.

How do these measurements relate to application speed?

Server Execution Time excludes planning, while ordinary EXPLAIN ANALYZE also omits result transmission and, by default, output conversion. Instrumentation itself adds overhead. Consequently, the script compares instrumented database execution rather than measuring the complete time to display an article list. PostgreSQL: Using EXPLAIN

To investigate output conversion separately on PostgreSQL 17 or 18, add SERIALIZE TEXT to the same explanation options. PostgreSQL 16 does not list that option. Serialization still does not transmit the query’s result rows, so it cannot measure network delivery. Keep serialization settings identical across the compared runs. PostgreSQL 17: EXPLAIN, PostgreSQL 16: EXPLAIN

For an application measurement, time the ordinary article SELECT through completion of its awaited driver call, with the connection already established. Record that as a separate client elapsed measurement. Preserve the application’s actual query configuration: adding a node-postgres query name enables per-connection prepared-statement behavior and changes the comparison conditions. node-postgres: Queries

Choose the candidate when equivalent results, repeated timings and buffer counts tell a consistent story for the article request you need to serve. Retain the full plans, individual runs, server version and query parameters so the conclusion remains specific and reproducible.

Sources