Partial and Expression Indexes

Your queries are slow — every SELECT on a big table scans thousands of rows even though you only care about a tiny slice of data, and every WHERE lower(email) = '...' forces the database to compute a function on every row. You could throw a standard B-tree index at it, but that bloats storage and still doesn't help because the index doesn't match your condition. The fix is partial and expression indexes — indexes that store only what your queries actually need, computed how your queries actually use it. In this lesson, you'll learn what they are, why they matter, and how to build them for real-world speed and disk savings.

The problem this lesson solves

Picture a orders table with 10 million rows. You run SELECT * FROM orders WHERE status = 'open' — only 5,000 rows match, but PostgreSQL scans all 10 million to find them. A standard index on status helps, but it still stores every one of those 10 million entries, most of which you'll never query. Similarly, WHERE lower(email) = 'bob@example.com' can't use a plain index on email because the expression lower(email) isn't stored. Your queries end up doing seq scans or expensive per-row casts, and your disk fills up with index cruft. This lesson solves two distinct pains:

• Storage bloat — your index is larger than it needs to be because it indexes rows you never filter on.
• Function calls in WHERE — you can't use a normal index because the query applies a function (like lower()) or a computation (like date_trunc()) to the column.

Partial indexes let you index only the subset of rows that matter. Expression indexes let you index the result of a function or expression. Combined, they give you indexes that are smaller, faster, and tailored to your real query patterns.

Core concept / mental model

Think of a standard index as a phone book for your whole table — every row gets an entry. A partial index is a phone book for just one neighborhood: you tell PostgreSQL to index only rows that meet a WHERE condition, so the book is thinner and faster to flip through. An expression index is a phone book sorted by a transformed version of the name — like indexing by last name in uppercase, so you can look up "SMITH" without converting every entry on the fly.

In database terms:

• Partial index — created with a WHERE clause. It only contains rows where that predicate is true. PostgreSQL can use it when your query's WHERE matches the same predicate (or a subset).
• Expression index — created on an expression like (lower(email)) or (date_trunc('day', created_at)). The index stores the computed value, so queries with the identical expression can do an index scan instead of a sequential scan.

Why this works: indexes are inherently ordered structures. A partial index is smaller, so reads and writes touch fewer pages. An expression index pre-computes the value, so PostgreSQL doesn't need to evaluate the function for every row during the scan — it just looks up the stored result.

How it works step by step

Creating these indexes is a one-liner, but choosing where and what requires a clear process. Here's the step-by-step mental workflow:

1. Identify the hot query — find the slow query in your logs or pg_stat_statements. Look for a restrictive WHERE or a function on a column.
2. Check the predicate — for a partial index, the WHERE clause must be a constant, immutable condition (e.g., status = 'open', is_deleted = false). It can't reference other tables or volatile functions.
3. Check the expression — for an expression index, the expression must be immutable (same input → same output, always). lower() is fine, now() is not.
4. Create the index — use CREATE INDEX ... ON table (expression) WHERE predicate;. You can combine both!
5. Verify with EXPLAIN — confirm your query now uses the index (look for Index Scan or Bitmap Index Scan).

The cause-effect chain: a partial index reduces the number of index entries → smaller pages → fewer disk reads → faster scans. An expression index moves the cost of computation from query time to write time → queries become index lookups.

Hands-on walkthrough

Let's build a real example. We'll create a table, populate it with some rows, and then add both partial and expression indexes.

Setup

-- Create a simple orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status TEXT NOT NULL,
    customer_email TEXT NOT NULL,
    total NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Insert some sample data (mix of statuses)
INSERT INTO orders (status, customer_email, total) VALUES
('open', 'alice@example.com', 99.99),
('shipped', 'bob@example.com', 199.00),
('open', 'carol@example.com', 59.50),
('cancelled', 'dave@example.com', 0.00),
('open', 'eve@example.com', 299.99),
('shipped', 'frank@example.com', 149.00);

Create a partial index

-- Index only open orders
CREATE INDEX idx_orders_open ON orders (status) WHERE status = 'open';

-- Check if it's used
EXPLAIN SELECT * FROM orders WHERE status = 'open';

Expected output (simplified):

Index Scan using idx_orders_open on orders
  Index Cond: (status = 'open'::text)

Create an expression index

-- Index on lowercase email
CREATE INDEX idx_orders_email_lower ON orders (lower(customer_email));

-- This query can now use the index
EXPLAIN SELECT * FROM orders WHERE lower(customer_email) = 'alice@example.com';

Expected output shows an Index Scan using idx_orders_email_lower.

Combine both

-- Index only open orders, by lowercased email
CREATE INDEX idx_orders_open_email_lower ON orders (lower(customer_email)) WHERE status = 'open';

Compare options / when to choose what

Not every index type fits every situation. Here's a comparison table to guide your choice:

Rule of thumb: Use a partial index when you repeatedly query a small subset of rows that matches a constant condition. Use an expression index when your WHERE or JOIN uses a function on a column. Combine them when both patterns overlap.

Variations to consider: - If you're on PostgreSQL 17+, you can use optimized B-tree for MIN/MAX with expression indexes (but that's advanced). - For JSONB columns, use a GIN index with an expression like (data->'key'). - Use CREATE UNIQUE INDEX with a partial predicate to enforce conditional uniqueness.

Troubleshooting & edge cases

Even experienced DBAs hit these gotchas. Here's how to fix them:

My query isn't using the partial index

• Check the predicate match — your query's WHERE must be a superset of the index predicate. If you index WHERE status = 'open', a query with WHERE status = 'open' AND created_at > '2024-01-01' will use it, but WHERE status != 'cancelled' will not (PostgreSQL can't prove that status != 'cancelled' implies status = 'open').
• Run ANALYZE — the planner may have stale statistics. Run ANALYZE orders; and re-test.
• Check for hidden casts — if you wrote status = 'open' but the column is varchar, ensure the literal type matches (use ::text).

The expression index isn't used

• Expression mismatch — the query must have the exact expression. If your index is on lower(email) and your query is WHERE lower(email) = ..., it works. But if you write WHERE email ILIKE 'alice%', that's different (use lower(email) LIKE instead).
• Function volatility — expression indexes only work with immutable functions. If you use now() or a user-defined function that's not marked IMMUTABLE, PostgreSQL will refuse to create the index, or it won't be used. Stick to built-ins like lower(), upper(), date_trunc(), ABS(), etc.

Partial index + unique constraint

• If you want a unique constraint only on a subset, use CREATE UNIQUE INDEX ... WHERE .... But note: null values are not considered equal, so you can have multiple rows with NULL in the indexed expression — that's usually fine.

Index bloat on updates

• If the indexed column changes frequently, the partial index may become bloated. Run VACUUM and consider REINDEX CONCURRENTLY if needed.

What you learned & what's next

You've learned how to create partial indexes to shrink index size and speed up queries that filter on a common constant, and expression indexes to pre-compute function results for faster lookups. You also know how to combine them for maximum benefit, and you can now verify their usage with EXPLAIN. These tools are essential for any PostgreSQL workload where query patterns are predictable and storage matters.

As a next step in this tutorial track, you'll likely move on to index maintenance strategies — how to monitor index usage, drop unused ones, and rebuild bloated ones. That will complete your index optimization journey. For now, practice what you've learned: pick a table from your own project, identify a hot query that filters on a constant or uses a function, and create an appropriate partial or expression index. Then run EXPLAIN to confirm the planner uses it. You'll see the difference in query time immediately.