PostgreSQL Partial Index vs BRIN vs Expression Index: Measured on 3M Rows

You have a slow query on a big Postgres table and several index options that all claim to help. I ran each one on the same 3-million-row table in postgres:17-alpine and recorded sizes and EXPLAIN (ANALYZE) output, including the cases where the index does nothing. You'll get a decision rule for partial, BRIN, expression and covering (INCLUDE) indexes, plus a CTE setting that turned a 1 ms query into a 2.5 s one. Everything is reproducible from the commands below.

One laptop (Docker Desktop on Windows, 8 cores), each query run five times, median reported. Treat the absolute milliseconds as relative, and treat any difference under 2x as noise: even the medians moved that much between my trial runs.

Setup: container, data, timing helper

docker run -d --name ngb-pg17 -e POSTGRES_PASSWORD=pw postgres:17-alpine \
  -c shared_buffers=512MB -c jit=off -c max_parallel_workers_per_gather=0

jit=off and no parallel workers keep the plans simple. SELECT version() reports PostgreSQL 17.11 on x86_64-pc-linux-musl, which is what postgres:17-alpine pulled on 2026-10-07.

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  user_id int NOT NULL,
  status text NOT NULL,
  email text NOT NULL,
  amount numeric(10,2) NOT NULL,
  created_at timestamptz NOT NULL,
  note text
);

-- 3M rows in time order, one per 10 seconds; 0.5% pending, 1.5% failed, 98% shipped
INSERT INTO orders (user_id, status, email, amount, created_at)
SELECT 1 + (random() * 99999)::int,
       CASE WHEN r < 0.005 THEN 'pending' WHEN r < 0.02 THEN 'failed' ELSE 'shipped' END,
       'User' || (random() * 250000)::int || '@Example.COM',
       round((random() * 500)::numeric, 2),
       timestamptz '2025-01-01 00:00:00+00' + i * interval '10 seconds'
FROM (SELECT i, random() AS r FROM generate_series(0, 2999999) AS i) s;

-- same rows in random physical order, to test BRIN's weak spot
CREATE TABLE orders_shuf AS SELECT * FROM orders ORDER BY random();
ALTER TABLE orders_shuf ADD PRIMARY KEY (id);
VACUUM ANALYZE orders;
VACUUM ANALYZE orders_shuf;

Result: 15,026 pending, 44,726 failed, 2,940,248 shipped rows; heap 264 MB, primary key 64 MB. pg_stats.correlation for created_at is 1 on orders and -0.0079 on orders_shuf. Check that number before you create any BRIN index.

Timing helper (bash): five EXPLAIN (ANALYZE) runs per query, median of the Execution Time lines.

P="docker exec -i ngb-pg17 psql -U postgres -At"
med() { for i in 1 2 3 4 5; do echo "EXPLAIN (ANALYZE) $1;" | $P | grep 'Execution Time' | awk '{print $3}'; done \
        | sort -n | awk '{a[NR]=$1} END{print "median", a[3], "ms (min", a[1] ", max", a[5] ")"}'; }

PostgreSQL partial index: small, but not always faster

The job: a dashboard lists the 50 newest pending orders.

-- Q50
SELECT id, created_at FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 50;
-- QC
SELECT count(*) FROM orders WHERE status = 'pending';

No index: seq scan, 419 ms for Q50 and 591 ms for QC.