Partial & Expression Indexes

A regular index covers every row in a table, using the raw value of the column as it's stored. Partial indexes and expression indexes relax each of those assumptions in a different direction — a partial index covers only some of the rows, and an expression index indexes a computed value rather than the raw column. Both are simple ideas that pay off in very common, very concrete situations.

Partial indexes: indexing a subset of rows

A partial index is created with a WHERE clause on the CREATE INDEX statement itself. Only rows matching that condition get an entry in the index — everything else is left out entirely. When the vast majority of your queries against a table only ever care about a small subset of its rows, indexing just that subset produces a much smaller index that is cheaper to maintain on every write, and often faster to scan too, since PostgreSQL never wastes time skipping over irrelevant entries.

A queue-processing table is the textbook case: most rows end up in a terminal state (completed, cancelled) and stay there forever, while only a small, constantly-changing slice sits in a pending state that background workers repeatedly poll for.

Indexing only pending orders

SQL

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  status   TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

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

A worker polling for the next order to process benefits directly, and the index stays small no matter how many millions of completed orders accumulate in the table over time — completed rows were never in it to begin with.

A query the partial index can serve

SQL

SELECT order_id FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10;

Index Scan using idx_orders_pending on orders (cost=0.15..8.32 rows=10 width=12)

Expression indexes: indexing a computed value

A plain index stores a column's raw value. An expression index instead stores the result of a function or expression applied to the column, which lets PostgreSQL use the index for queries that filter on that same expression — something a plain index on the raw column cannot help with at all.

Case-insensitive lookups are the classic example. A query filtering with LOWER(email) = ... cannot use a plain index on email, because the index stores the original-case values, not their lowercased form — PostgreSQL would still have to scan every row and compute LOWER() on it to compare. Indexing LOWER(email) directly fixes that.

Case-insensitive lookups with an expression index

SQL

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- This query can now use the index:
SELECT * FROM users WHERE LOWER(email) = 'jordan@example.com';

Index Scan using idx_users_email_lower on users  (cost=0.29..8.31 rows=1 width=64)
  Index Cond: (lower(email) = 'jordan@example.com'::text)

The key rule with an expression index is that the query has to use the exact same expression the index was built on — an index on LOWER(email) will not help a query that filters on UPPER(email), even though the underlying intent is similar. PostgreSQL matches expression indexes syntactically, not by inferring equivalent expressions.

Combining both

Partial and expression indexing are independent techniques and can be combined in the same CREATE INDEX statement — indexing a computed expression, but only for the subset of rows matching a condition — for cases that need both kinds of narrowing at once.

Combining a partial condition with an expression

SQL

CREATE INDEX idx_users_email_lower_active
  ON users (LOWER(email))
  WHERE is_active = true;

Both are genuinely distinctive PostgreSQL strengths

Not every relational database supports indexing an arbitrary expression, or restricting an index to a WHERE condition, as naturally as PostgreSQL does. Both features let you shape an index precisely around your actual query patterns instead of settling for a generic index on the raw column — often the difference between an index that helps and one that just adds write overhead for no benefit.

A partial index (CREATE INDEX ... WHERE ...) only covers rows matching the condition — smaller, cheaper to maintain.
An expression index covers a computed value (like LOWER(email)) rather than the raw column.
A query must use the exact same expression the index was built on to benefit from an expression index.
Partial and expression indexing can be combined in a single CREATE INDEX statement.