Partial and Expression Indexes

Smaller indexes for the rows that matter

A partial index indexes only the rows matching a WHERE clause. It's smaller, faster to maintain, and often dramatically faster for queries that always filter the same way.

CREATE INDEX idx_active_users ON users(email) WHERE archived = 0;

Use partial indexes when:

Your queries always filter by the same predicate (e.g., 'active', 'unprocessed', 'in the last 30 days' for some workloads).
Most of the table doesn't match — partial indexes shine when they're a small fraction of the full table.

An expression index indexes the value of an expression rather than a column. Useful for case-insensitive lookups or normalized comparisons.

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

Then SELECT * FROM users WHERE lower(email) = ? uses the index. Without it, SQLite has to lower() every email on every query.

Tip: Partial indexes are a perfect fit for queues: CREATE INDEX idx_queue_pending ON jobs(created_at) WHERE status = 'pending'. The index is tiny (only pending rows) and the SELECT-then-mark workflow stays fast even when the table grows to millions of historical rows.

Code

Partial index for a queue table·sql

CREATE TABLE jobs (
  id INTEGER PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'pending',
  payload TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

-- Only pending jobs are indexed -> tiny + fast even with 10M historic rows
CREATE INDEX idx_jobs_pending ON jobs(created_at) WHERE status = 'pending';

-- The optimizer uses it for queries that include the same predicate:
EXPLAIN QUERY PLAN
SELECT * FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 10;
-- USING INDEX idx_jobs_pending

Expression index for case-insensitive lookups·sql

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

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE lower(email) = ?;
-- USING INDEX idx_users_email_lower

Exercise

On a table with at least 100k rows where most rows are 'inactive' / 'closed' / 'archived', design a partial index that only covers the 'active' subset. Time the same query with and without the partial index — and compare disk size with SELECT * FROM dbstat WHERE name='your_index'. Then add an expression index for a case-insensitive search and confirm the plan picks it.

Partial and Expression Indexes

Smaller indexes for the rows that matter

Code

External links

Exercise

Progress

Comments 0