To diagnose repeated PostgreSQL lookups, separate candidate construction from candidate consumption. Read the inner scan’s execution count, check whether caching avoids repeated work, then compare grouped reuse, CTE inlining, or an indexed temporary table.

This walkthrough is for a developer working on a Mac who needs to explain a slow article query: select candidate articles once, then find the highest candidate article ID within each article’s topic. It uses synthetic rows throughout. The goal is to identify where work repeats and choose a query shape that preserves the result.

Set up a reproducible candidate query

Use an existing local PostgreSQL installation with psql available. Open Terminal, run psql with your usual connection settings, and keep the same session open throughout the SQL examples. PostgreSQL documents psql as its interactive terminal for submitting queries and viewing results. psql

First check the server version with SHOW server_version;. The current documentation retrieved for this article identifies PostgreSQL 18 as current. The examples use PostgreSQL 12 or later syntax; that is a feature boundary, not a recommendation to use an older release. PostgreSQL 12 introduced CTE inlining and the MATERIALIZED and NOT MATERIALIZED controls. WITH Queries, Release 12

Run this setup once in a fresh session. It creates 20,000 synthetic articles and an index for topic-and-ID lookups. The candidate predicate used below selects the first 2,000 articles.

CREATE TEMP TABLE article_demo (
    id integer PRIMARY KEY,
    topic_id integer NOT NULL,
    title text NOT NULL
);

INSERT INTO article_demo (id, topic_id, title)
SELECT g, 1 + (g % 50), 'Article ' || g
FROM generate_series(1, 20000) AS s(g);

CREATE INDEX article_demo_topic_id_idx
    ON article_demo (topic_id, id);

ANALYZE article_demo;

These temporary tables last for the session by default. Run ANALYZE explicitly after loading them: PostgreSQL’s autovacuum process does not analyze temporary tables. CREATE TABLE

Now capture the baseline. The lateral subquery computes the highest candidate ID for the current article’s topic. Here, “highest ID” is simply the defined task; it does not imply a publication date.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate AS x
    WHERE x.topic_id = c.topic_id
) AS m;

The explicit MATERIALIZED makes the experiment’s reuse boundary clear. A materialized CTE can be built once while its consumers read the stored rows repeatedly. PostgreSQL’s documentation illustrates how a materialized, multiply referenced CTE loses the benefit of the underlying table’s indexes when searching that intermediate result. Common Table Expression Materialization

The SQL examples have been inspected but were not executed against a PostgreSQL server for this article. The synthetic row-count arithmetic was checked separately; no execution times or captured plans are presented as measurements.

Read the plan to locate repeated work

Start at the candidate-producing branch, then follow each consumer. Under a nested loop, identify the outer input and the inner lookup. PostgreSQL reports loops as a node’s execution count; actual rows and node times are averages per execution. A correlated scalar-subquery version may instead show repeated work under SubPlan, so the diagnosis should follow the executing nodes rather than require a particular label. Using EXPLAIN

Record four observations from your actual plan:

  1. How many rows enter the outer lookup?
  2. How many times does the underlying inner scan execute?
  3. Does that scan read candidates and then discard most through a filter?
  4. Is there a Memoize node between the outer rows and the expensive inner work?

Keep the levels separate. An aggregate might return one maximum per execution while its child scans many rows to calculate it. A candidate producer running once therefore does not establish that candidate consumption is cheap.

For this exact sample, 2,000 candidates span 50 topics, with 40 articles per topic. The following calculation describes possible work patterns, not promised plan output or runtime:

Consumption pattern Illustrative candidate-row examinations
Full candidate scan for every outer article 2,000 × 2,000 = 4,000,000
Full scan once per topic, with all later lookups cached 50 × 2,000 = 100,000
Group candidates once by topic 2,000 rows entering the grouping step, plus subsequent join work

The distinction matters on PostgreSQL 14 and later, which introduced memoization of inner nested-loop results. Current documentation describes Memoize as caching parameterized scan results and allowing the underlying scan to be skipped on a cache hit; entries can also be evicted. Inspect the cache key and any reported hits, misses, evictions, and overflows before attributing thousands of outer rows to thousands of full scans. Release 14, Query Planning

Also compare estimated rows with actual rows at the earliest substantial mismatch. PostgreSQL recommends checking these estimates, and cautions that toy-table plans do not necessarily extrapolate to larger inputs. Avoid adding parent and child timings together: they describe overlapping execution work. Using EXPLAIN

Choose a rewrite that matches the repeated task

The useful question is how much work each consumer needs from the candidate set. Use this decision aid to select the first experiment:

Observed requirement First experiment Condition to preserve
Every candidate needs the same per-topic maximum Group once, then join One summary row per topic
Each consumer needs a small indexed portion of the source Try NOT MATERIALIZED Keep the full candidate predicate
Several statements need an explicitly indexed candidate relation Create an indexed temporary table Keep the same session and include setup cost
Repeated parameters already produce many cache hits Compare total execution time before changing shape Confirm that underlying scans are actually costly

Compute the per-topic result once

For this task, a grouped summary expresses the reuse directly. Run the following after the setup, in the same session:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
), topic_max AS (
    SELECT topic_id, max(id) AS highest_id
    FROM candidate
    GROUP BY topic_id
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
JOIN topic_max AS m USING (topic_id);

This rewrite produces one maximum for each topic and attaches it to every candidate article. PostgreSQL defines max as the maximum non-null input value. The sample’s non-null topic and ID columns make the equivalence straightforward: every candidate belongs to a group containing itself. Aggregate Functions

Expect 2,000 result rows when running the query without EXPLAIN. For example, article 1 belongs to topic 2 and receives highest_id = 1951; topic 1 receives highest_id = 2000. These are deterministic consequences of the sample generator.

Check result meaning before generalizing. Grouping by extra columns could create multiple summary rows per topic and multiply the join output. Allowing null topic IDs also requires revisiting the equality semantics. If the task requires the title of the highest-ID article, joining the maximum back to the candidate rows is an additional step; taking a separate maximum of the title would answer a different question.

The rewrite permits a different plan, but does not mandate a hash join or guarantee a speedup. Compare the measured grouping and join work with the baseline’s actual inner executions.

Test whether source indexes can replace intermediate scans

For an eligible CTE, replace AS MATERIALIZED in the complete baseline query with AS NOT MATERIALIZED, then rerun it. This allows joint optimization with the source relation, potentially trading stored candidate scans for source lookups. It can also duplicate candidate computation. Common Table Expression Materialization

The sample’s (topic_id, id) index matches equality on the leading topic column and a restriction on the ID column. That makes it a reasonable index to test; verify whether the actual plan uses it and how much it scans. PostgreSQL documents leading-column equality and the next column’s inequality as conditions that limit a multicolumn B-tree scan. Multicolumn Indexes

Preserve id <= 2000 inside the candidate definition. Looking up the maximum across all 20,000 source rows would change the answer. Also preserve the documented boundary: NOT MATERIALIZED is ignored for recursive CTEs and CTEs that are not side-effect-free. For an ineligible CTE, use a supported relational rewrite or explicitly stage its result instead. SELECT

Stage an indexed candidate relation when reuse spans statements

A CTE belongs to one statement. If several statements need the same candidate rows and explicit indexes, a temporary table provides that different lifetime and indexing choice. Run this alternative after the original setup:

CREATE TEMP TABLE candidate_demo AS
SELECT id, topic_id, title
FROM article_demo
WHERE id <= 2000;

CREATE INDEX candidate_demo_topic_id_idx
    ON candidate_demo (topic_id, id);

ANALYZE candidate_demo;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT c.id, c.title, m.highest_id
FROM candidate_demo AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate_demo AS x
    WHERE x.topic_id = c.topic_id
) AS m;

Temporary-table indexes are temporary too, and the relation is session-scoped. Include creation, index building, and analysis when assessing whether staging helps the complete task. Do not compare only its final lookup against the entire unstaged operation. CREATE TABLE

Capture the same experiment with node-postgres on your Mac

To inspect the application query path, use a currently supported Node.js release and install the documented package with npm install pg. Save the complete program below as diagnose.mjs in that project and run node diagnose.mjs. It creates its own synthetic input, so it does not depend on the earlier psql session. node-postgres

The program uses your existing PostgreSQL connection environment. node-postgres documents the same connection environment variables used by psql, including PGHOST, PGPORT, and PGDATABASE. Connecting

import pg from 'pg';
const { Client } = pg;
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_demo (
      id integer PRIMARY KEY,
      topic_id integer NOT NULL,
      title text NOT NULL
    );
    INSERT INTO article_demo (id, topic_id, title)
    SELECT g, 1 + (g % 50), 'Article ' || g
    FROM generate_series(1, 20000) AS s(g);
    CREATE INDEX article_demo_topic_id_idx
      ON article_demo (topic_id, id);
    ANALYZE article_demo;
  `);

  const sql = `
    WITH candidate AS MATERIALIZED (
      SELECT id, topic_id, title
      FROM article_demo
      WHERE id <= $1::integer
    )
    SELECT c.id, c.title, m.highest_id
    FROM candidate AS c
    CROSS JOIN LATERAL (
      SELECT max(x.id) AS highest_id
      FROM candidate AS x
      WHERE x.topic_id = c.topic_id
    ) AS m
  `;

  const result = await client.query({
    text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) ${sql}`,
    values: [2000]
  });

  for (const row of result.rows) {
    console.log(row['QUERY PLAN']);
  }
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await client.end();
}

The query configuration uses the documented text and values fields. Adding a name creates a prepared statement on each connection; it does not cache candidate rows or remove repeated inner execution. Queries

If an existing application uses a pool, keep temporary-table creation and consumption on one checked-out client, then release it in finally. Separate pool.query calls can use different clients. Likewise, node-postgres requires all statements in a transaction to use the same client instance. Pooling, Transactions

For named statements whose behavior changes with parameter values, investigate planning separately. PostgreSQL can choose generic or parameter-specific custom plans; its documented inspection route is EXPLAIN EXECUTE for the prepared statement. A freshly explained unnamed query may therefore differ from an established prepared execution. PREPARE

Decide whether the repeated work has actually improved

Use the same candidate predicate, selected columns, and input values for each comparison. Run an initial pass and repeat comparable passes to observe cache variation. Record the server version, candidate count, inner scan executions, cache behavior, and total execution time. PostgreSQL 17 improved CTE planning using column statistics and sort order, so retain the version when comparing results from different machines. Release 17

For a second diagnostic pass, EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) retains row counts and statement execution time while avoiding per-node clock measurements. Buffer totals on parent nodes include child activity, so do not sum the entire tree. EXPLAIN ANALYZE executes the query but does not measure transmission of its result rows to Node.js. EXPLAIN

Treat planner switches as experiments. Turning enable_nestloop off discourages nested loops but cannot eliminate them entirely; enable_material also cannot suppress all materialization. Neither switch establishes that candidate consumption has become efficient. Query Planning

Finish with the query shape that preserves the required rows and reduces the measured work for the complete task. For this per-topic maximum, grouped reuse is the clearest first experiment. Keep indexed lookups when they prove cheap, and use explicit staging when its additional setup earns its place across repeated consumers.

Sources