Partial, covering, and expression indexes in PostgreSQL

The outbox dispatcher polls five times a second for unpublished messages. There is an index on queued_at, the query filters on status = 'pending' and orders by queued_at, and the poll takes four seconds against a table of 40 million rows of which roughly 300 are pending at any moment. The ticket has sat open for a week as "index exists, still slow", and nobody can say why the planner is not using the index.

It is using the index. It walks it from the oldest message forward, in queued_at order, and discards every entry whose row turns out to be published, which is nearly all of them. The plan says so in one line:

Limit  (cost=0.56..412.18 rows=100 width=310)
  ->  Index Scan using ix_outbox_queued_at on outbox_messages
        Filter: (status = 'pending')
        Rows Removed by Filter: 39999700
        Buffers: shared hit=118204 read=302411
Execution Time: 4127.882 ms

The index is not wrong. It answers the question "rows in queued_at order", which is not the question the dispatcher asks. If you already know how a B-tree lookup works and still watch plans read most of the table, the mismatch is almost always one of three: the index carries millions of entries the query never wants, the index finds the row but not the columns the query needs, or the query compares an expression of the column rather than the column. PostgreSQL has a distinct index shape for each.

The planner matches shapes, not names

A B-tree can do exactly one thing quickly: find the entries whose key satisfies a condition, in key order, each pointing at a table row. Everything the planner decides follows from whether your query's question fits that form against the index you gave it.

Same query, two index shapes

The planner matches shapes, not names

EXPLAIN (ANALYZE, BUFFERS)

Partial index

hot subset

WHERE status = 'pending' ORDER BY queued_at LIMIT 100

plain B-tree mismatch

ON outbox (queued_at)

Index Scan, Filter: status = 'pending'

Rows Removed by Filter: 39,999,700

reshape the index ↓

partial shape match

ON outbox (queued_at) WHERE status = 'pending'

Index Scan over ~300 entries

Rows Removed by Filter: 0

the query predicate must imply the index predicate

Covering index

index-only scan

SELECT created_day, amount WHERE merchant_id = 42

plain B-tree mismatch

ON payments (merchant_id)

Index Scan, then one heap page per row

84,000 table visits for 84,000 rows

reshape the index ↓

covering shape match

ON payments (merchant_id) INCLUDE (created_day, amount)

Index Only Scan

Heap Fetches: 0 (while the visibility map says all-visible)

every selected column must live in the index

Expression index

function match

WHERE lower(email) = '[email protected]'

plain B-tree mismatch

ON accounts (email)

Seq Scan, Filter: lower(email) = ...

Rows Removed by Filter: 2,999,999

reshape the index ↓

expression shape match

ON accounts (lower(email))

Index Scan, Index Cond: lower(email) = ...

3 index pages, 1 heap page

the indexed expression must appear verbatim in the predicate

Partial: index only the rows you read

The outbox is the textbook case (transactional outbox in .NET covers the table itself). Published rows are kept for audit and replay, so the table is almost entirely history, and the only rows anyone reads are the unpublished tail. A partial index stores entries for the rows that satisfy its WHERE clause and nothing else:

CREATE INDEX ix_outbox_pending_queued
    ON outbox_messages (queued_at)
    WHERE status = 'pending';

Against the same table, that index has about 300 entries instead of 40 million. The dispatcher's plan becomes Index Scan using ix_outbox_pending_queued, Rows Removed by Filter: 0, and a handful of buffer hits. Inserting a published row never touches it, publishing a message removes one entry, and its size is bounded by the pending count rather than by history, so it stays in cache.

The rule that makes it work is in the PostgreSQL manual: the planner uses a partial index only when it can prove the query's WHERE clause implies the index predicate. WHERE status = 'pending' proves it. WHERE status IN ('pending', 'failed') does not, and neither does a status that arrives as a bind parameter the planner cannot see when it plans, which the manual calls out explicitly: matching happens at planning time, so a parameterized clause is not guaranteed to match. In EF Core a constant in the lambda is inlined into the SQL as a literal and a captured local becomes a parameter, so write the hot-path predicate as a constant and confirm the plan once with EXPLAIN.

Covering: never open the table

A merchant's daily totals endpoint runs SELECT created_day, amount FROM payments WHERE merchant_id = $1, and an ordinary index on merchant_id serves it with an Index Scan: find the entries, then visit one table page per row to read the two columns the index does not carry. For 84,000 payments that is 84,000 heap visits into wide rows that are rarely all in cache.

INCLUDE puts extra columns into the index's leaf entries without making them part of the key:

CREATE INDEX ix_payments_merchant_covering
    ON payments (merchant_id) INCLUDE (created_day, amount);

Now every value the query selects lives in the index, and the plan changes shape:

Index Only Scan using ix_payments_merchant_covering on payments
  Index Cond: (merchant_id = 42)
  Heap Fetches: 0
  Buffers: shared hit=291

Two things keep this honest. First, name one column outside the index and the plan drops back to an Index Scan with a heap visit per row; SELECT * is the usual way to lose it, and so is a projection that grew a field last sprint. Second, the index does not know whether a row is visible to your transaction. PostgreSQL checks the visibility map, one bit per table page meaning every row on it is visible to everyone; pages written since the last VACUUM have the bit cleared, so under heavy update churn Heap Fetches climbs towards the row count and the index-only scan costs what a plain one did. Covering indexes pay off on read-mostly data; on a hot table, watch Heap Fetches and autovacuum before widening the index.

The planner does not use an index because it exists. It uses an index whose shape lets it answer the query's exact question: these rows, these columns, this expression.

Expression: index what you actually compare

Sign-ups arrived through three clients over the years and none normalized the address, so accounts holds [email protected] and [email protected] as separate rows. Login has to treat them as one address, which means the predicate is lower(email) = $1. The plain index on email stores the raw values in raw order, so there is no sorted structure for lower(email) to descend, and the plan is a Seq Scan with Filter: (lower(email) = ...) and three million rows removed.

An expression index stores the result of the expression instead of the column:

CREATE INDEX ix_accounts_lower_email ON accounts (lower(email));

The matching rule is textual. The planner uses this index for a predicate that contains the exact expression lower(email) on the column side; the other side can be a literal, a parameter, or lower($1). It does not use it for email = $1, nor for upper(email) = upper($1), even though the answers would be identical. The expression must also be immutable: lower(email) qualifies, and anything that depends on the session time zone or the clock does not.

The mirror image is the bug you have probably met already: a plain index and a query that wraps the column in a function anyway, the non-sargable trap in reading EF Core query plans. Same rule from the other direction: the expression in the predicate and the expression in the index must be the same text.

The EF Core side

The fluent API on the EF Core indexes page covers the first two shapes directly. HasFilter takes the partial predicate as provider SQL, and IncludeProperties adds the non-key columns:

modelBuilder.Entity<OutboxMessage>()
    .HasIndex(m => m.QueuedAt)
    .HasDatabaseName("ix_outbox_pending_queued")
    .HasFilter("status = 'pending'");

modelBuilder.Entity<Payment>()
    .HasIndex(p => p.MerchantId)
    .HasDatabaseName("ix_payments_merchant_covering")
    .IncludeProperties(p => new { p.CreatedDay, p.Amount });

There is no fluent call for an index on an arbitrary expression. The first honest route is raw SQL in the migration, which is where DDL belongs anyway:

migrationBuilder.Sql(
    "CREATE INDEX ix_accounts_lower_email ON accounts (lower(email));");

The second is a stored generated column, HasComputedColumnSql("lower(email)", stored: true), with an ordinary HasIndex on it: a few bytes per row for a plain column you can query by name.

On the query side, db.Accounts.Where(a => a.Email.ToLower() == normalized) translates to WHERE lower(a.email) = @p, which matches the expression index verbatim. Folding the address only in C# before the call is half a fix: the parameter is normalized and the stored side is not, so the lookup misses the rows written in capitals.

When not to add the index

Each shape has its own bill. A partial index is close to free for rows outside its predicate, which is the point. INCLUDE widens every leaf entry, and an update to an included column now has to maintain the index and loses the cheap heap-only update path. An expression index computes the expression on every insert and every update of the column. None of that matters at ten thousand rows and all of it matters at forty million, so check pg_stat_user_indexes a month after the deploy and drop what shows zero scans.

Selectivity still rules. A partial index over WHERE status = 'published' on the same outbox table would hold 39.9 million entries and help nobody. These shapes fix mismatches between a query and an index; they cannot make a predicate that matches a third of the table cheap to serve through one, and the planner is right to refuse. The sorted leaf pages that make ORDER BY ... LIMIT cheap are also what keyset pagination relies on, and only when the index order is the query order.

Graded on the plan the database chose

Nothing in the C# looks different when the index shape is wrong, so the durable version of all this comes from watching plans respond to your own queries, with the row counts visible.

Katabench's Database track has five exercises built on exactly these three shapes. "The Index That Only Covers the Unsent" is the outbox above: a partial index exists and the starter's predicate does not imply it. "Never Touch the Table" hands you a covering index and a query that keeps dropping to a heap visit per row. "The Index Was on the Function All Along" is the lower(email) mirror, and "Do Not Wrap the Indexed Column" is its opposite. "Let the Index Serve ORDER BY + LIMIT" is the sorted-leaf-page case. Each runs against a real PostgreSQL database, and the grader reads the execution plan the database actually chose, so a correct answer that arrives by scanning everything fails with the plan on screen next to the rows. After enough of those, Rows Removed by Filter stops being a line you scroll past.