---
title: "Check a new PostgreSQL index against the query the app sends | Proofline"
description: "A migration adds an index for a slow query, and EXPLAIN with a literal value shows an index scan."
url: https://proofline.sh/learn/postgres-index-parameter-types/
---

[Learning center](/learn/)

# 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.

Breaking migrations The Proofline team November 20, 2026 3 min read

## 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](https://www.postgresql.org/docs/16/sql-prepare.html) 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](https://www.postgresql.org/docs/16/indexes-partial.html) 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](/learn/postgres-migration-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](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Retrying a failed PostgreSQL CREATE INDEX CONCURRENTLY](/learn/postgres-create-index-concurrently-retry/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
    
-   ### [Which PostgreSQL migration needs a rehearsal before the next deploy](/learn/postgres-migration-rehearsal/)
    
    Breaking migrations The Proofline team November 20, 2026 4 min read
