Learning center

Check a new PostgreSQL index against the query the app sends

A migration adds an index for a slow query, and EXPLAIN with a literal value shows an index scan. The application can still scan the whole table, because it sends a parameter of another type or runs a prepared statement with a generic plan. Rehearse with the statement the application prepares, including its parameter types.

A job queue and a partial index#

A worker picks the next pending jobs. Almost all rows are done, so a partial index on pending jobs stays small. We ran this on PostgreSQL 16.15.

CREATE TABLE jobs (
    id bigint PRIMARY KEY,
    status text NOT NULL,
    queue text NOT NULL,
    run_at timestamptz NOT NULL
);
INSERT INTO jobs
SELECT g,
       CASE WHEN g % 1000 = 0 THEN 'pending' ELSE 'done' END,
       'mail',
       now() - (g || ' seconds')::interval
FROM generate_series(1, 1000000) g;

CREATE INDEX jobs_pending_run_at ON jobs (run_at) WHERE status = 'pending';
ANALYZE jobs;

EXPLAIN (COSTS OFF)
SELECT id FROM jobs WHERE status = 'pending' ORDER BY run_at LIMIT 10;
--  Limit
--    ->  Index Scan using jobs_pending_run_at on jobs

This is the check a reviewer usually sees in the pull request. It shows what the planner does with a literal, which the application never sends.

A parameter of another type#

A support page looks up one job by ID. The application binds the ID as numeric, for example because the value reaches the query as a decimal type:

PREPARE by_id(numeric) AS SELECT status FROM jobs WHERE id = $1;
EXPLAIN (COSTS OFF) EXECUTE by_id(4200);
 Gather
   Workers Planned: 2
   ->  Parallel Seq Scan on jobs
         Filter: ((id)::numeric = '4200'::numeric)

PostgreSQL has no bigint = numeric operator in the primary key's index operator class, so it casts the column to numeric for every row and cannot use the index. With a bigint parameter the same query uses jobs_pkey. Nothing fails: the page is slow, and the time grows with the table. A new index does not help either, for the same reason. The fix belongs in the application, where it binds the parameter with the column's type.

A generic plan cannot use the partial index#

PostgreSQL plans a prepared statement for its parameter values at first, and may switch to a generic plan that works for any value after five executions. A generic plan does not know that $1 will be 'pending', so it cannot prove that the partial index's condition holds:

PREPARE next_jobs(text) AS
  SELECT id FROM jobs WHERE status = $1 ORDER BY run_at LIMIT 10;

SET plan_cache_mode = force_generic_plan;
EXPLAIN (COSTS OFF) EXECUTE next_jobs('pending');
 Limit
   ->  Gather Merge
         Workers Planned: 2
         ->  Sort
               Sort Key: run_at
               ->  Parallel Seq Scan on jobs
                     Filter: (status = $1)

That execution took 25 ms, against 0.3 ms with the index. In the default mode, auto, PostgreSQL kept choosing parameter-specific plans in our run: after seven executions, pg_prepared_statements showed 7 custom plans and 0 generic ones, because the generic plan's estimated cost was much higher. The PREPARE documentation describes how it makes that comparison. A connection or role with plan_cache_mode = force_generic_plan, or a data distribution where the costs come closer, can make the switch. If the status is always 'pending', write it into the query as a literal so the planner can match the partial index's condition every time.

What to rehearse#

Capture the statements the application sends to the table, with their parameter types, from the driver's logging or from pg_stat_statements. On a staging copy with the production row count and statistics, prepare each one with the same types and run it at least six times, then EXPLAIN it again. Check pg_prepared_statements for its plan counts. The rehearsal passes when every execution uses the index you added.

The partial index documentation explains when the planner can match a partial index's condition. A new index also adds work to every write on the table, so measure write latency in the same rehearsal.

Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading