Skip to content
Tutorials Center
My learning

Topics

Lesson 40 of 90 ยท Indexes

Partial and Expression Indexes

Index only the rows you query with partial indexes, index computed values like lower(email), meet the IMMUTABLE rule, and enforce soft-delete uniqueness.

A plain index covers every row of the table and indexes the column exactly as stored. Two features remove those limits. A partial index includes only the rows that satisfy a WHERE condition, so a table with a million finished jobs and a hundred waiting ones can have a tiny index that holds only the waiting ones. An expression index is built on a computed value, such as lower(email), so a query that searches by that computed value can use an index instead of scanning.

Both are small ideas with outsized effects: smaller indexes that stay in memory, faster writes because fewer rows are indexed, and queries and uniqueness rules that a plain index cannot express. Better still, SQLite supports both, so the live examples here use the same syntax you will use in PostgreSQL. Differences, such as how clever the planner is at matching conditions, are called out where they matter. If indexes are new to you, start with How Indexes Work in PostgreSQL and B-tree Indexes.

Partial indexes

Add a WHERE clause to CREATE INDEX and only matching rows enter the index:

SQL
-- Only open orders are indexed
CREATE INDEX orders_pending_date_idx
  ON orders (ordered_at)
  WHERE status = 'pending';

Why would you want that? Think about the typical "work queue" table. Most rows are in a final state (done, delivered, cancelled), and your application keeps asking for the few that are not. A full index on status would store a million entries to help find a hundred. The partial index stores a hundred.

The benefits stack up:

  • Smaller index: less disk, more of it stays cached, and scans are shorter.
  • Cheaper writes: inserts and updates of rows that do not match the predicate do not touch this index at all.
  • Sharper statistics: the planner knows the index covers a small set of rows.

Try it with the shop's orders. The index covers only pending orders, and the plan shows when the query is allowed to use it:

SQL A partial index and who may use it ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first plan says SEARCH orders USING COVERING INDEX orders_pending_idx (ordered_at>?); the second falls back to SCAN orders. The index holds only the 24 pending orders (out of 120), so it is a fifth of the size of a full one.

The planner must be able to prove the match

An index can only be used when the planner can prove that every row the query wants is in the index. The query's WHERE clause must imply the index predicate. In the example above, status = 'pending' literally repeats the index condition.

PostgreSQL's planner is reasonably good at this proof: it knows, for instance, that status = 'pending' implies an index predicate of status IN ('pending', 'shipped'), and that amount > 100 implies amount > 50. SQLite is more literal and may ignore a partial index unless the condition is written the same way. In both systems, write the predicate in your query exactly as it appears in the index definition if you want to be sure.

There is one PostgreSQL trap worth knowing. A query sent with a parameter, WHERE status = $1, may be planned before the value is known (a generic plan), and the planner cannot prove $1 = 'pending', so it skips the partial index. If a partial index is ignored in application code but works when you type the literal in psql, this is usually why. Either put the constant in the SQL text, or look at the plan_cache_mode setting.

SQL
-- Works: literal in the query
SELECT id FROM orders WHERE status = 'pending' AND ordered_at >= '2025-03-01';

-- May not use the partial index when planned generically
PREPARE find_orders (text) AS
  SELECT id FROM orders WHERE status = $1 AND ordered_at >= '2025-03-01';

Typical partial indexes: unprocessed jobs (WHERE processed_at IS NULL), active records (WHERE deleted_at IS NULL), the rare half of a skewed column, and the "only these rows must be unique" rule shown below.

Expression indexes

The classic trap: you index email, and then search with WHERE lower(email) = '[email protected]'. The index is sorted by email, not by lower(email), so it is useless for that query and the table is scanned. An expression index sorts by the computed value instead:

SQL
CREATE INDEX customers_email_lower_idx ON customers (lower(email));

SELECT id FROM customers WHERE lower(email) = '[email protected]';

The rule is strict: the query must use the same expression. lower(email) in the index serves lower(email) = ... but not upper(email) = ..., and not a plain email = .... You can see all three cases in SQLite:

SQL An expression index must match the query ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first plan shows SEARCH customers USING COVERING INDEX customers_lower_city_idx (<expr>=?), and the other two SCAN customers. The final query returns the customers in Lyon, found through the index.

Useful expression indexes include case-insensitive lookups (lower(email)), a date extracted from a timestamp, a field pulled out of a jsonb column such as ((attributes ->> 'sku')), and a computed key like (first_name || ' ' || last_name). Note the double parentheses around an expression in PostgreSQL when it is not a simple function call: CREATE INDEX ON products ((price * quantity)). A function call such as lower(email) needs only the usual single pair.

Expression indexes have a side benefit. PostgreSQL gathers statistics on the indexed expression, so the planner estimates how many rows lower(email) = 'x' will match instead of guessing.

The IMMUTABLE requirement

PostgreSQL only lets you index functions that always return the same output for the same input, forever. Such functions are marked IMMUTABLE. If the result could ever change, the index entries would silently go wrong, so PostgreSQL refuses:

SQL
CREATE INDEX bad_idx ON orders (date_trunc('day', created_at));
-- ERROR:  functions in index expression must be marked IMMUTABLE

Here created_at is a timestamptz, and truncating it to a day depends on the session's TimeZone setting, so the function is only STABLE. The usual fix is to pin the time zone yourself, which makes the expression immutable:

SQL
CREATE INDEX orders_day_idx
  ON orders (date_trunc('day', created_at AT TIME ZONE 'UTC'));

Queries must then use the identical expression. now(), random() and anything that reads from another table are never allowed. SQLite has the same rule under the name deterministic, and says so when you break it:

SQL The rule: no non-deterministic functions (fails on purpose) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first index is created, and the second stops with non-deterministic functions prohibited in index expressions. PostgreSQL's equivalent message mentions IMMUTABLE. When you write your own SQL function for use in an index, you must declare it IMMUTABLE yourself, and it is your responsibility that it really is, because PostgreSQL trusts the label.

Unique partial indexes and soft deletes

The most valuable combination of the two features is the partial unique index. "Soft delete" means marking a row deleted (deleted_at is set) rather than removing it. A normal UNIQUE (email) is then too strict: a user who deleted their account could never sign up again with the same address. You want email to be unique only among live rows, and case-insensitively:

SQL
CREATE UNIQUE INDEX accounts_email_live_uq
  ON accounts (lower(email))
  WHERE deleted_at IS NULL;

Soft-deleted rows are not in the index, so they do not collide with anything. This works in SQLite too, and the last statement here fails on purpose:

SQL Unique among live rows only (fails on purpose)

Press Run (or Ctrl + Enter) to see the result here.

After the first two inserts you see the deleted [email protected] (id 1) and the live [email protected] (id 2). The third insert fails with UNIQUE constraint failed: index 'accounts_email_live', because lower() makes [email protected] collide with the live row.

A PostgreSQL detail: INSERT ... ON CONFLICT can target a partial or expression unique index, but you must repeat the index definition so PostgreSQL can infer which index you mean:

SQL
INSERT INTO accounts (email) VALUES ('[email protected]')
ON CONFLICT (lower(email)) WHERE deleted_at IS NULL
DO NOTHING;

You will use this pattern in INSERT ON CONFLICT and RETURNING. A PostgreSQL unique constraint cannot be partial, but a unique index can, and it enforces exactly the same rule.

Two ways to index less, or differently

Partial Index only rows that match a WHERE condition.
Expression Index a computed value such as lower(email).
Unique + partial Unique only among live rows.

The query must repeat the same condition or expression.

1 / 2

When not to bother

Partial and expression indexes are tools for specific needs. If a plain index serves the query and the table is small, keep it simple. Also avoid creating many overlapping partial indexes on the same column. Each needs its own maintenance and makes plan choices harder to reason about. Add one when you can name the query it serves, and confirm with EXPLAIN that it is used (EXPLAIN and EXPLAIN ANALYZE).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main benefit of a partial index such as WHERE status = 'pending'?

    Rows outside the predicate are not in the index, so the index is smaller, stays cached more easily, and rows that do not match do not cost an index write.
  2. 2. An index exists on lower(email). Which query can use it?

    The index is sorted by lower(email), so only a query using that same expression can search it.
  3. 3. Why does PostgreSQL reject an index on date_trunc('day', created_at) when created_at is a timestamptz?

    Index expressions must always give the same result for the same input. A time-zone-dependent result can change, so you pin the zone, for example with AT TIME ZONE 'UTC'.
  4. 4. How do you let a user re-register an email address after soft-deleting their account?

    A partial unique index only contains live rows, so deleted rows never conflict, while live ones stay unique regardless of letter case.

Summary

  • A partial index (CREATE INDEX ... WHERE) stores only matching rows: smaller, faster to maintain, ideal for queues, active rows and rare values.
  • The planner must prove the query implies the index predicate. PostgreSQL's proof is smarter than SQLite's, but generic plans for parameterized queries can defeat it.
  • An expression index sorts by a computed value and is used only when the query repeats the same expression.
  • Index expressions must be IMMUTABLE (SQLite: deterministic). Pin the time zone for timestamptz date functions.
  • A partial unique index gives "unique among live rows" for soft deletes, and ON CONFLICT can target it.
  • Use these when you can name the query they serve, and verify with EXPLAIN.

Next: GIN and GiST Indexes, the index types for JSON, arrays, text search and ranges.