The partial index and the parameter

Run a real systemNeeds Docker, Python

Problem

A partial index covers only the rows that match its predicate, so an index on the 1,000 pending jobs of a 5-million-row queue is tiny and always hot. The planner can use it only when it can prove that the query's filter implies the index predicate. A literal status = 'pending' proves it; a parameter status = $1 does not, unless the planner plans for the actual value. PostgreSQL plans a prepared statement's first executions with the actual values (custom plans) and may switch to a generic plan, which cannot use the partial index.

Create a queue table with 5 million rows, 1,000 of them 'pending', and CREATE INDEX ON q (id) WHERE status = 'pending'. Compare EXPLAIN for the literal query, for a prepared statement executed seven times with 'pending', and with a generic plan forced. Also compare the index sizes.

The question. How much smaller is the partial index, and which forms of the query use it?

Predict

How many times smaller is the partial index on 1,000 pending rows than an index on the whole 5-million-row column?

Under 100×100 to 1,000×Thousands of times

Run it

1Install uv once. It fetches Python and the lab's packages for you. This lab also runs a real system, so you need Docker.
Terminal
```
curl -LsSf https://astral.sh/uv/install.sh | sh
```

2Get the script. It is the whole lab: read it, then download it.

the-partial-index-and-the-parameter.py45 linesDownload

View the code

the-partial-index-and-the-parameter.py

# /// script
# requires-python = ">=3.10"
# dependencies = ["psycopg[binary]"]
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""The partial index and the parameter.

A 5-million-row queue table with 1,000 pending rows and a partial index
WHERE status = 'pending'. Plans for the literal query, for a prepared
statement with a parameter over repeated executions, and for a full-column
index; and the sizes of both indexes.
Needs the PostgreSQL service from the compose file (port 55521).
"""
import argparse, json
import psycopg

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--dsn", default="postgresql://postgres:lab@localhost:55521/postgres", help="Connection string.")
p.add_argument("--rows", type=int, default=5_000_000, help="Rows.")
p.add_argument("--pending", type=int, default=1000, help="Pending rows.")
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()
con = psycopg.connect(args.dsn, autocommit=True)
con.execute("DROP TABLE IF EXISTS q")
con.execute(f"CREATE TABLE q AS SELECT g AS id, CASE WHEN g % ({args.rows} / {args.pending}) = 0 THEN 'pending' ELSE 'done' END AS status, md5(g::text) AS payload FROM generate_series(1, {args.rows}) g")
con.execute("CREATE INDEX q_pending ON q (id) WHERE status = 'pending'"); con.execute("ANALYZE q")
plan = lambda sql: [r[0] for r in con.execute("EXPLAIN " + sql)][0].split("(")[0].strip()
rows = [{"query": "literal: WHERE status = 'pending'", "plan": plan("SELECT * FROM q WHERE status = 'pending'")}]
con.execute("PREPARE p(text) AS SELECT * FROM q WHERE status = $1")
for i in range(1, 8):
    rows.append({"query": f"prepared, execution {i}", "plan": plan("EXECUTE p('pending')")})
con.execute("SET plan_cache_mode = force_generic_plan")
rows.append({"query": "prepared, generic plan forced", "plan": plan("EXECUTE p('pending')")})
con.execute("RESET plan_cache_mode")
partial = con.execute("SELECT pg_relation_size('q_pending')").fetchone()[0]
con.execute("CREATE INDEX q_status ON q (status)"); con.execute("ANALYZE q")
full = con.execute("SELECT pg_relation_size('q_status')").fetchone()[0]
for r in rows: print(f"{r['query']:<36} {r['plan']}")
print(f"partial index {partial / 1e3:,.0f} KB   full-column index {full / 1e6:,.1f} MB   ({full / partial:,.0f}x)")

if args.json:
    with open(args.json, "w") as f:
        json.dump({"tables": {"plans": rows, "summary": [{"partial_kb": partial / 1e3, "full_mb": full / 1e6, "ratio": full / partial}]}}, f)

3Get the services it runs against, pinned to the versions we measured. Save it next to the script.

the-partial-index-and-the-parameter.compose.yml13 linesDownload

View the code

the-partial-index-and-the-parameter.compose.yml

# PostgreSQL for this lab.
services:
  postgres:
    image: postgres:17.6
    environment:
      POSTGRES_PASSWORD: lab
    command: ["postgres", "-c", "shared_buffers=256MB"]
    ports: ["55521:5432"]
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "postgres"]
      interval: 1s
      retries: 30

4Start the services, run the script with the defaults first, then change a knob from the table below and run it again. Remove the services when you are done.

Terminal

docker compose -f the-partial-index-and-the-parameter.compose.yml up -d --wait
uv run the-partial-index-and-the-parameter.py
uv run the-partial-index-and-the-parameter.py --help     # every knob
docker compose -f the-partial-index-and-the-parameter.compose.yml down -v

Knobs
Knob · default	What it changes
--dsnpostgresql://postgres:lab@localhost:55521/postgres	Connection string.
--rows5,000,000	Rows.
--pending1000	Pending rows.

Our run

Which plan each query got

Which plan each query got
Query	Plan
literal: WHERE status = 'pending'	Index Scan using q_pending on q
prepared, execution 1	Index Scan using q_pending on q
prepared, execution 2	Index Scan using q_pending on q
prepared, execution 3	Index Scan using q_pending on q
prepared, execution 4	Index Scan using q_pending on q
prepared, execution 5	Index Scan using q_pending on q
prepared, execution 6	Index Scan using q_pending on q
prepared, execution 7	Index Scan using q_pending on q
prepared, generic plan forced	Seq Scan on q

Reference runApple M2 · 8 cores · 8 GB

Output · uv run the-partial-index-and-the-parameter.py

literal: WHERE status = 'pending'    Index Scan using q_pending on q
prepared, execution 1                Index Scan using q_pending on q
prepared, execution 2                Index Scan using q_pending on q
prepared, execution 3                Index Scan using q_pending on q
prepared, execution 4                Index Scan using q_pending on q
prepared, execution 5                Index Scan using q_pending on q
prepared, execution 6                Index Scan using q_pending on q
prepared, execution 7                Index Scan using q_pending on q
prepared, generic plan forced        Seq Scan on q
partial index 41 KB   full-column index 34.7 MB   (846x)

macOS 26.6.2 · Python 3.13.3 · psycopg 3.3.6 · postgres:17.6 · Docker 28.5.1 · run 2026-10-20

What we found

The partial index took 41 KB against 34.7 MB for an index on the whole column, 846× smaller: hundreds of times rather than thousands, because an index has a fixed minimum of a few pages. The literal query used it, and so did all seven executions of the prepared statement (Index Scan using q_pending on q): PostgreSQL kept planning them with the actual value, because the custom plan was far cheaper than any generic one. With a generic plan forced, the query could not prove $1 = 'pending' and fell back to Seq Scan on q.

Read it

A partial index is a promise that only some queries can use, and whether a query qualifies is decided at plan time. Literal values and custom plans see the predicate; a generic plan, which a driver or connection pool may cause without your choosing, does not. If a partial index matters, check the plan through the same driver and statement path your application uses.