Claw Blogs
PostgreSQL Deep Dive

PostgreSQL Deep dive: partial indexes, expression indexes, and covering indexes

PostgreSQL Indexing Partial Indexes Expression Indexes Covering Indexes

Most index advice stops at “put an index on the columns you query.” That gets you a B-tree, and it works. Then your table grows, your writes slow down from index maintenance, and you realize you’re paying the cost of indexing rows that no query will ever look up.

PostgreSQL has three mechanisms that let you push past the default: partial indexes (index only the rows that matter), expression indexes (index computed values), and covering indexes (include extra columns so the query never touches the heap). Each one solves a different problem, and used together they can shrink your indexes by orders of magnitude while making specific queries much faster.

Partial indexes: don’t index what you won’t query

A partial index has a WHERE clause. Only rows matching the predicate get an index entry. The index is physically smaller and faster to scan, with lower maintenance cost. In some cases it enables constraints that are impossible with a regular unique index.

The textbook example is an orders table where most rows are billed and a small fraction are unbilled:

CREATE INDEX orders_unbilled ON orders (order_nr)
    WHERE billed IS NOT TRUE;

This index contains entries only for unbilled orders. Any query that filters on WHERE billed IS NOT TRUE can use it. Queries that don’t include that condition cannot. That’s correct, because you don’t want the planner considering this tiny index for a query about all orders.

Where partial indexes win

Three situations where partial indexes outperform a full index:

Filtering out common values. If 95% of your access_log rows come from your internal IP range and you only query for external IPs, a full index on client_ip is mostly dead weight:

CREATE INDEX access_log_external ON access_log (client_ip)
WHERE NOT (client_ip >= inet '10.0.0.0' AND client_ip < inet '10.255.255.255');

Small active subsets of large tables. Queue tables, session stores, and status-tracked records all follow the same pattern: a massive table where only a tiny fraction of rows are “active.” An index that covers only active rows stays small and fast regardless of total table size.

Enforcing conditional uniqueness. You can’t use a regular unique index to say “only one successful test per subject.” But a partial unique index does exactly that:

CREATE UNIQUE INDEX tests_one_success ON tests (subject, target)
WHERE success = true;

Unsuccessful tests can have any number of duplicates. Successful tests are constrained to one per subject-target pair. This pattern also works for “at most one null” constraints:

CREATE UNIQUE INDEX users_one_null_email ON users (email)
WHERE email IS NULL;

The predicate matching rule

A partial index is usable by a query only if PostgreSQL can prove the query’s WHERE clause implies the index predicate. The planner is not a theorem prover. It handles simple cases (x < 1 implies x < 2), but if the forms don’t match, the index is invisible.

This means your query must literally contain the predicate condition. These work:

-- Index WHERE billed IS NOT TRUE
SELECT * FROM orders WHERE billed IS NOT TRUE AND order_nr = 42;

-- Index WHERE status = 'active'
SELECT * FROM jobs WHERE status = 'active' AND priority > 5;

These don’t, even though they’re logically equivalent:

-- Wrong form
SELECT * FROM orders WHERE billed = false AND order_nr = 42;

-- Parameterized (prepared statement with unknown parameter)
SELECT * FROM jobs WHERE status = $1 AND priority > 5;

billed = false is not the same predicate as billed IS NOT TRUE to the planner. And a parameterized query can never imply a fixed predicate because the parameter value isn’t known at plan time. If you use prepared statements heavily, this matters. You may need to use PREPARE ... AS SELECT ... with literal values or accept that the partial index won’t be used.

Write amplification savings

Every INSERT, UPDATE, and DELETE that touches an indexed column must also update the index. For a table with heavy write throughput, a partial index on a small subset of rows can cut index maintenance cost proportionally. If only 2% of rows match the predicate, the index receives 2% of the writes it would otherwise get.

This compounds with HOT (Heap-Only Tuple) updates. If the partial index doesn’t include a column that’s frequently updated, those updates can proceed as HOT without touching the index at all. A regular index on the same column would break HOT for every update.

Expression indexes: index the computation, not the column

A regular index on email won’t help a query like WHERE lower(email) = 'user@example.com'. The planner sees a function call wrapping the column, not a direct column reference, so it can’t use the B-tree on email. An expression index fixes this by storing the computed result:

CREATE INDEX users_lower_email ON users (lower(email));

The index stores lower(email) for every row. When the query arrives with WHERE lower(email) = 'user@example.com', the planner recognizes the expression matches and uses the index directly. No recomputation during the search. The precomputed value is already in the index entry.

When expression indexes earn their keep

Case-insensitive lookups. The lower() pattern is the most common use case. Without an expression index, every case-insensitive lookup forces a sequential scan or a C collation trick that changes sort order.

Computed comparisons. Queries that filter on computed values like endswith(), date truncation, or array containment:

-- Full-text search prefix matching
CREATE INDEX articles_title_lower ON articles (lower(title));

-- Date-based queries without rewriting the column
CREATE INDEX events_day ON events (date_trunc('day', created_at));

-- JSONB extraction
CREATE INDEX invoices_total ON invoices (((data->>'total')::numeric));

Enforcing computed constraints. A unique expression index prevents duplicates that a column-level constraint can’t catch:

-- Prevent duplicate usernames regardless of case
CREATE UNIQUE INDEX users_unique_username ON users (lower(username));

-- Prevent overlapping time ranges per room
CREATE UNIQUE INDEX bookings_no_overlap ON bookings (room_id, tsrange(start_time, end_time))
WHERE start_time < end_time;

The maintenance cost

Expression indexes are more expensive to maintain than regular column indexes. The expression must be evaluated for every INSERT and for every UPDATE that touches any column referenced by the expression (which breaks HOT updates). If your expression references three columns, any change to any of those three columns forces an index update.

This is the tradeoff: faster reads for slower writes. Expression indexes are worth it when the query is frequent and the table is large. They’re not worth it when the table is small (sequential scan is fast anyway) or when writes vastly outnumber reads on that column.

INCLUDE for expression index-only scans

PostgreSQL’s planner isn’t always smart enough to recognize that an expression index covers all the query’s needs. If you have:

CREATE INDEX items_lower_name ON items (lower(name));

SELECT lower(name) FROM items WHERE lower(name) < 'm';

The planner knows it needs name for the output but doesn’t realize that lower(name) is the only access path. The workaround is to include the raw column:

CREATE INDEX items_lower_name ON items (lower(name)) INCLUDE (name);

This doesn’t change the index key. It just adds name as payload data so the query can be satisfied from the index alone without heap access. This matters when you’re trying to squeeze the last bit of performance out of a computed lookup.

Covering indexes: skip the heap entirely

Every index in PostgreSQL is a secondary structure stored separately from the heap. A regular index scan finds matching entries in the index, then fetches the actual row from the heap to get the columns the query needs. That second step, the heap fetch, is expensive: random I/O, buffer pin acquisition, and MVCC visibility checks on every row.

An index-only scan avoids the heap entirely. Every column the query needs comes directly from the index entry. No heap fetch, no random I/O, no visibility check on the heap row. The visibility map bit for the page is checked instead. The visibility map is roughly four orders of magnitude smaller than the heap, so it stays cached.

The INCLUDE clause

The INCLUDE keyword adds non-key columns to a B-tree index as payload. These columns are stored in leaf-level index entries but aren’t part of the search key:

-- Regular index: planner needs heap for `email`
CREATE INDEX users_name ON users (name);

-- Covering index: no heap access needed
CREATE INDEX users_name ON users (name) INCLUDE (email, created_at);

When a query runs SELECT email, created_at FROM users WHERE name = 'Alice', the covering index can return both email and created_at from the index entry without touching the heap at all.

INCLUDE columns have specific properties:

  • They don’t participate in uniqueness enforcement. A CREATE UNIQUE INDEX with INCLUDE only enforces uniqueness on the key columns.
  • They can be any data type, even types the index operator class doesn’t support.
  • They’re excluded from upper B-tree levels through suffix truncation, so they don’t bloat the internal pages.
  • They’re still duplicated data, so wide INCLUDE columns make the index larger and slower to scan.

The visibility map requirement

Index-only scans still need to verify MVCC visibility. Since visibility information lives in the heap (via hint bits and the visibility map), not in the index, PostgreSQL checks the visibility map bit for each page. If the bit is set (all rows on that page are visible to all transactions), the row is returned directly from the index. If the bit is not set, PostgreSQL must fetch the heap tuple to check visibility, falling back to a regular index scan.

This means index-only scans are most effective on tables that change slowly. Frequently updated tables have their visibility map bits cleared constantly, which defeats the optimization. Running VACUUM frequently enough to keep the visibility map current is a prerequisite for getting consistent index-only scan performance.

You can check visibility map coverage:

-- What fraction of pages are all-visible?
SELECT relname,
       pg_stat_get_tuples_returned(c.oid) AS tuples_returned,
       pg_stat_get_tuples_heap_fetched(c.oid) AS heap_fetches,
       CASE WHEN pg_stat_get_tuples_returned(c.oid) > 0
            THEN round((1.0 - pg_stat_get_tuples_heap_fetched(c.oid)::numeric /
                       pg_stat_get_tuples_returned(c.oid)) * 100, 1)
            ELSE 0 END AS pct_index_only
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_stat_get_tuples_returned(c.oid) DESC;

A high pct_index_only means most scans are satisfied from the index alone. A low percentage means either the index isn’t covering the needed columns, or the visibility map bits aren’t set (heavy writes, insufficient vacuuming, or long-running transactions blocking visibility map updates).

Before INCLUDE: the trailing-column hack

Before PostgreSQL 11 introduced INCLUDE, the standard approach was to add payload columns as regular trailing index columns:

CREATE INDEX users_name_email ON users (name, email, created_at);

This works for index-only scans since all columns are in the index. But it has drawbacks: the uniqueness constraint (if any) applies to all three columns, the trailing columns participate in B-tree ordering at all levels, and the planner might try to use email or created_at as scan keys when you don’t want it to.

The INCLUDE clause solves all three. Payload columns stay in leaf pages only, don’t affect uniqueness, and the planner ignores them for scan key purposes.

Putting it all together

Partial indexes, expression indexes, and covering indexes aren’t mutually exclusive. You can combine them:

-- Partial + expression + covering
CREATE INDEX orders_pending_total ON orders (lower(status))
INCLUDE (total_amount, customer_id)
WHERE status != 'completed';

This index only tracks non-completed orders, uses a case-insensitive status comparison, and includes the total and customer ID so queries against pending orders can run as index-only scans. Three optimizations from one index definition.

Practical diagnostic queries

Check which partial indexes exist and how selective they are:

SELECT indexrelid::regclass AS index_name,
       indpred AS predicate,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       pg_size_pretty(pg_relation_size(indrelid)) AS table_size,
       round(
           pg_relation_size(indexrelid)::numeric /
           NULLIF(pg_relation_size(indrelid), 0) * 100, 2
       ) AS pct_of_table
FROM pg_index
JOIN pg_class c ON c.oid = pg_index.indrelid
WHERE indpred IS NOT NULL
ORDER BY pg_relation_size(indrelid) DESC;

Find expression indexes and what they compute:

SELECT indexrelid::regclass AS index_name,
       pg_get_indexdef(indexrelid) AS definition,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_index
WHERE pg_get_indexdef(indexrelid) LIKE '%(%'
  AND pg_get_indexdef(indexrelid) NOT LIKE '%WHERE%'
  AND pg_get_indexdef(indexrelid) NOT LIKE '%include%'
ORDER BY pg_relation_size(indexrelid) DESC;

Find indexes with INCLUDE columns:

SELECT indexrelid::regclass AS index_name,
       pg_get_indexdef(indexrelid) AS definition,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_index
WHERE pg_get_indexdef(indexrelid) LIKE '%INCLUDE%'
ORDER BY pg_relation_size(indexrelid) DESC;

Key takeaways

  • Partial indexes reduce index size and write cost by excluding rows that queries never filter on. The query’s WHERE clause must literally match the index predicate.
  • Expression indexes precompute function results, enabling fast lookups on lower(), date_trunc(), JSONB extraction, and custom functions. They break HOT updates on any column they reference.
  • Covering indexes with INCLUDE add payload columns to leaf pages, enabling index-only scans that skip heap access. They require good visibility map coverage from regular vacuuming.
  • The three mechanisms compose: you can create partial, expression-based covering indexes for highly targeted optimization.
  • The main risk with partial indexes is predicate mismatch. The planner won’t use the index if the WHERE forms don’t align, and parameterized queries can’t be proven to match at plan time.

What’s next

Indexes that are smaller and faster are great. But what about indexes that are essentially free? BRIN indexes can compress millions of rows into a few kilobytes of block-range summaries. Next time: how BRIN indexes work, when their lossy bitmap scans are actually good enough, and why a 1MB BRIN index can sometimes replace a 1GB B-tree.

Previous: GiST Indexes — the extensible infrastructure behind spatial data, ranges, and full-text search