Partial, expression, and covering indexes

A tasks table has 100M rows. 80% have status = 'done' and are never queried by the dashboard. The full B-tree on (workspace_id, created_at) is 2 GB and every insert touches it. A partial index WHERE status IN ('open','in_progress') is 400 MB, 5× cheaper to maintain, and the dashboard queries run just as fast — because it only indexes the rows the dashboard actually needs.

Partial indexes

A partial index is a B-tree (or any index type) built only over rows matching a WHERE predicate.

CREATE INDEX ON orders(user_id) WHERE status = 'pending';

This indexes only pending orders — typically a small fraction of the table. The result: smaller index, faster lookup on the hot subset, cheaper write overhead (the index is only touched when status = 'pending').

For the planner to use a partial index, the query’s WHERE clause must logically imply the index’s predicate. A query WHERE user_id = 42 AND status = 'pending' can use the index. A query WHERE user_id = 42 alone cannot (it would need to match all statuses, but the index only covers pending rows).

Production use cases:

• Soft-delete pattern: WHERE deleted_at IS NULL — only active rows are indexed; historical deleted rows are excluded.
• Hot-subset filters: a task table where 95% of rows are status = 'done' — index only 'open' and 'in_progress'.
• Feature flags: WHERE feature_x_enabled = true — index only rows where the flag is active.

Partial unique indexes are a powerful variant. CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL enforces “email is unique among non-deleted users.” This lets you soft-delete a user and later re-register the same email — impossible with a simple UNIQUE constraint on the email column.

Expression indexes

An expression index indexes a function of one or more columns, not the columns themselves.

CREATE INDEX ON users (LOWER(email));

This lets WHERE LOWER(email) = 'alice@x.com' use the index. Without it, LOWER(email) is evaluated on every row, defeating any index on the email column.

Common patterns:

• Case-insensitive search: LOWER(email), UPPER(code).
• Date truncation: DATE_TRUNC('day', created_at) for group-by-day queries.
• JSON path extraction: (data->>'event_type') — indexes one field inside a JSONB column.
• Computed values: COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') for name-search.

The query must use the exact expression for the planner to recognize the index. WHERE LOWER(email) = ? uses the index. WHERE email = ? does not (unless the email is already lowercase and a regular index exists).

Covering indexes with INCLUDE

A regular B-tree index stores key columns in both internal nodes and leaf pages. The INCLUDE clause (Postgres 11+) adds extra columns to the leaf pages only — they are not part of the sort key, but they are stored in the leaf, available for the query.

When a query’s SELECT projection needs only columns that are present in the index (either as key columns or INCLUDE columns), Postgres can answer the query without fetching the row from the heap at all — this is an index-only scan. It is 10–100× faster than an index scan for hot reads because it eliminates the heap fetch entirely.

CREATE INDEX CONCURRENTLY idx_orders_workspace_pending_recent
ON orders (workspace_id, created_at DESC)
WHERE status = 'pending'
INCLUDE (id, total_cents);

This index:

• Filters by workspace_id (leading column, tenant scope).
• Sorts by created_at DESC (serves the ORDER BY without a Sort step).
• Partial WHERE status = 'pending' — only covers ~20% of the table.
• INCLUDE (id, total_cents) — the typical dashboard projection is covered, no heap fetch needed.