Composite index ignored in Postgres: wrong column order and functions on columns

The customer invoice page of our B2B invoicing product started timing out for the larger accounts. The endpoint lists the last 90 days of invoices for one customer, newest first. p95 went from 40 ms to 3.1 s over about two months while the invoices table grew past 40 million rows. Nothing had been deployed on the database side, and the query had an index that, on paper, covered both filter columns.

duration: 3104.882 ms  execute <unnamed>: SELECT id, number, total_cents, status, created_at
FROM invoices WHERE customer_id = $1 AND created_at >= $2 ORDER BY created_at DESC LIMIT 50

Reproducing it

We copied a production-sized snapshot into a staging instance and ran the query with buffers enabled:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, number, total_cents, status, created_at
FROM invoices
WHERE customer_id = 48213
  AND created_at >= now() - interval '90 days'
ORDER BY created_at DESC
LIMIT 50;

Limit  (cost=0.56..9812.44 rows=50 width=52) (actual time=2.113..2911.507 rows=50 loops=1)
  Buffers: shared hit=21877 read=104322
  ->  Index Scan Backward using invoices_created_customer_idx on invoices
        (cost=0.56..68021.90 rows=347 width=52) (actual time=2.111..2911.460 rows=50 loops=1)
        Index Cond: ((created_at >= (now() - '90 days'::interval)) AND (customer_id = 48213))
Planning Time: 0.214 ms
Execution Time: 2911.583 ms

The index was used, which is why nobody had looked twice. But 126,000 buffers to return 50 rows is the giveaway: the scan was walking a huge part of the index.

What we ruled out

• Stale statistics. We ran ANALYZE invoices and the plan and timing stayed the same. Row estimates were close to reality.
• Bloat. pgstattuple showed the index at about 12 percent free space, normal for this workload. A REINDEX CONCURRENTLY on staging did not change the timing.
• Cold cache only. The second run was faster (about 900 ms) but still far from 40 ms, and production ran with a warm cache anyway.

Root cause

The index was defined as (created_at, customer_id). A B-tree sorts entries by the first column and only uses the second column to break ties. With a range condition on created_at as the leading column, Postgres has to visit every index entry in the last 90 days, across all customers, and check customer_id on each one. For 40 million invoices that is several million entries. The index had been created years earlier for an admin report that filtered by date only, and someone later appended customer_id assuming the order did not matter.

While auditing we found two more queries in the same service with the same smell: a column wrapped in a function, and a column silently cast to another type. Neither could use any index at all.

What is happening under the hood

A multicolumn B-tree is a single sorted list of tuples. Postgres can use it efficiently when the query pins down a prefix of the columns with equality and then puts at most one range condition on the next column. Conditions on later columns can still be checked inside the index, but they do not narrow the part of the index being read. The official documentation says it directly: the leading columns with equality constraints, plus any inequality on the first column that lacks an equality constraint, limit the portion of the index scanned.

So for customer_id = X AND created_at >= Y:

The second layout also satisfies ORDER BY created_at DESC for free, so the LIMIT 50 stops after 50 entries.

Functions and casts are a different failure. An index on email stores the raw email values. A predicate on lower(email) asks about a value the index does not contain, so the planner cannot use it, and the query falls back to a sequential scan. The same thing happens when a parameter type does not match the column: if the driver sends a numeric, Postgres compares customer_id::numeric = 48213.0, which again is an expression over the column rather than the column itself.

Environment

Minimal reproduction

This builds a small table that shows all three problems.

CREATE TABLE inv_demo (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  email       text   NOT NULL,
  created_at  timestamptz NOT NULL
);

INSERT INTO inv_demo (customer_id, email, created_at)
SELECT (random() * 20000)::bigint,
       'User' || g || '@Example.com',
       now() - (random() * interval '730 days')
FROM generate_series(1, 2000000) AS g;

CREATE INDEX inv_demo_created_customer ON inv_demo (created_at, customer_id);
CREATE INDEX inv_demo_email ON inv_demo (email);
ANALYZE inv_demo;

-- 1. range column first: index used, but reads a large slice
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM inv_demo
WHERE customer_id = 42 AND created_at >= now() - interval '90 days'
ORDER BY created_at DESC LIMIT 50;

-- 2. function on the column: sequential scan
EXPLAIN SELECT * FROM inv_demo WHERE lower(email) = 'user77@example.com';

-- 3. cast on the column: sequential scan
EXPLAIN SELECT * FROM inv_demo WHERE customer_id = 42.0;

The third plan shows Filter: ((customer_id)::numeric = 42.0) on a Seq Scan, which is how we spotted the reporting job.

Who is affected

Any B-tree based engine behaves this way, including MySQL InnoDB, where the rule is usually called the leftmost prefix rule. PostgreSQL 16 has no skip scan for B-tree indexes, so a query that does not constrain the leading column cannot jump through the index efficiently. ORMs make the cast problem easy to hit: a Decimal, a float, or a string literal compared against an integer or date column can all introduce a cast on the column side.

The fix

1. Put equality columns first, the range column last. We built the correct index without blocking writes, checked it, and only then dropped the old one.

CREATE INDEX CONCURRENTLY invoices_customer_created_idx
  ON invoices (customer_id, created_at);

-- confirm it is valid before relying on it
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indexrelid = 'invoices_customer_created_idx'::regclass;

The admin report that originally needed created_at alone still had a plain index on created_at, so the old composite index was pure overhead:

DROP INDEX CONCURRENTLY invoices_created_customer_idx;

2. Match the expression, or stop using one. For the case-insensitive email lookup we added an expression index and made the query use exactly the same expression:

CREATE INDEX CONCURRENTLY customers_lower_email_idx ON customers (lower(email));

- Customer.objects.filter(email__iexact=email)
+ Customer.objects.annotate(email_l=Lower("email")).filter(email_l=email.lower())

Django's iexact lookup on Postgres compiles to UPPER(...) = UPPER(...), which would not match a lower() index, so we spelled the expression out.

For a daily report that filtered on created_at::date = '2024-11-01' we did not add an expression index. Casting timestamptz to date depends on the session time zone, so it is not immutable, and Postgres refuses it:

ERROR:  functions in index expression must be marked IMMUTABLE

Instead we rewrote the predicate as a half-open range on the raw column, which uses the existing index:

- WHERE created_at::date = '2024-11-01'
+ WHERE created_at >= '2024-11-01 00:00:00+00'
+   AND created_at <  '2024-11-02 00:00:00+00'

3. Send the right parameter type. The reporting job read customer ids from a CSV through a library that produced Decimal. We convert them to int before querying:

- cur.execute(SQL, (row["customer_id"],))
+ cur.execute(SQL, (int(row["customer_id"]),))

Alternatives and trade-offs

• Keep both orders. If one hot query filters by date across all customers and another by customer plus date, two indexes are justified. Each one costs write throughput and memory, so we kept only what pg_stat_user_indexes proved was used.
• Covering index. Adding INCLUDE (number, total_cents, status) would allow index-only scans. We measured it and the gain was small because the visibility map on this table is often stale, so we skipped it.
• Generated column. For more complex expressions, a stored generated column with a normal index is easier for ORMs to target than an expression index.

How to test the fix

On staging, the same query after the new index:

Limit  (cost=0.56..187.40 rows=50 width=52) (actual time=0.041..0.198 rows=50 loops=1)
  Buffers: shared hit=54
  ->  Index Scan Backward using invoices_customer_created_idx on invoices ...
        Index Cond: ((customer_id = 48213) AND (created_at >= (now() - '90 days'::interval)))
Execution Time: 0.231 ms

We added a test that runs EXPLAIN (FORMAT JSON) against a seeded test database and fails if the plan for the invoice list query contains a Seq Scan or uses an index whose first column is not customer_id:

pytest tests/db/test_query_plans.py -k invoice_list -q

Confirming in production

After deploy we watched three things for a week:

-- the new index is being used, the old one is gone
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'invoices'
ORDER BY idx_scan DESC;

-- mean time of the statement dropped
SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms,
       shared_blks_read
FROM pg_stat_statements
WHERE query LIKE 'SELECT id, number, total_cents%invoices%customer_id%';

Mean execution time for the statement went from 1,870 ms to 0.4 ms, and shared blocks read per call fell by more than three orders of magnitude. Disk read IOPS on the primary dropped by about 35 percent during business hours because this endpoint had been the largest reader.

Regression guard

• The plan test above runs in CI against a seeded schema.
• auto_explain is enabled with auto_explain.log_min_duration = '500ms' so any slow plan lands in the logs with its full shape.
• New migrations that add a multicolumn index need a reviewer to answer one question in the PR: which query uses this, and which column is the equality filter.

Security implications

None beyond availability. The slow query was reachable by any authenticated customer, so a few large accounts refreshing the page in parallel could saturate database I/O for everyone, which is a cheap denial of service. Fixing the plan removed that lever. The rewritten queries keep using bound parameters, so no injection surface was added.

Prevention checklist

• Order composite index columns as equality filters first, then the one range or sort column.
• Never wrap an indexed column in a function or cast in a WHERE clause; transform the constant instead.
• Check parameter types coming from ORMs and CSV loaders against column types.
• Read Buffers in EXPLAIN (ANALYZE, BUFFERS), not just the node name; an index scan can still be a slow scan.
• Review pg_stat_user_indexes quarterly and drop indexes nobody scans.

References

• PostgreSQL docs, Multicolumn Indexes: https://www.postgresql.org/docs/16/indexes-multicolumn.html
• PostgreSQL docs, Indexes on Expressions: https://www.postgresql.org/docs/16/indexes-expressional.html
• PostgreSQL docs, Using EXPLAIN: https://www.postgresql.org/docs/16/using-explain.html
• PostgreSQL docs, auto_explain: https://www.postgresql.org/docs/16/auto-explain.html