SQL Academy · Lesson

Partial and Expression Indexes

Index just the rows you care about with WHERE clauses, and index computed expressions (lower(email), date_trunc('day', ts)).

Partial and Expression Indexes is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Partial Index: Index a Subset

A partial index covers only rows that match a WHERE clause at index creation. Smaller, faster, and great for the common-case query:

CREATE INDEX users_active_email_idx
  ON users(email)
  WHERE deleted_at IS NULL;

When Partial Indexes Win

Use when:

You always filter by the same predicate (e.g. soft-delete)
One value dominates the column (e.g. 95% of rows have status='closed')
You want a small, hot index in cache

Example: Active Sessions

Most sessions are expired; only active ones are queried:

CREATE INDEX sessions_active_idx ON sessions(user_id)
  WHERE expires_at > NOW();
-- Caveat: planner can't use NOW() in the index predicate — use a fixed timestamp
-- and reindex periodically, OR use a boolean column.

Better: Use a Stable Predicate

The index predicate must be immutable. Time-dependent expressions (NOW()) don't qualify. Use a column like is_active instead:

CREATE INDEX sessions_active_idx ON sessions(user_id)
  WHERE is_active = true;

-- The query must use the same predicate to match the index:
SELECT * FROM sessions WHERE is_active = true AND user_id = 42;

Expression Index

Index a function of a column:

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

SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Example.com');
-- Uses the expression index.

Common Expression Indexes

LOWER(email) for case-insensitive search
(price * tax_rate) for computed sort
date_trunc('day', created_at) for daily reports
(data ->> 'user_id')::BIGINT for JSONB extracts

Expression Must Be IMMUTABLE

The expression must be marked IMMUTABLE — its result depends only on the input. RANDOM(), NOW(), CURRENT_USER don't qualify.

Partial + Expression Combination

Both can be combined:

CREATE INDEX articles_active_title_idx
  ON articles(LOWER(title))
  WHERE published_at IS NOT NULL;

Unique Partial Indexes

The classic "one active row" pattern:

CREATE UNIQUE INDEX users_one_active_email
  ON users(email)
  WHERE deleted_at IS NULL;

-- Same email can exist many times in deleted users, but only once active.

Index Predicate Must Match Query

The planner only uses the partial index when the query's WHERE clause "implies" the index predicate:

-- Index:  WHERE is_active = true
-- Match:  WHERE is_active = true AND user_id = 42       ✓
-- Match:  WHERE is_active AND user_id = 42             ✓
-- No:     WHERE user_id = 42                           ✗
-- No:     WHERE is_active IS NOT FALSE                 ✗ (logically same but planner may not realise)

Don't Overuse

Many partial indexes covering disjoint subsets can slow writes (every INSERT must update all relevant indexes). Use sparingly for hot queries.

Recap

Partial and expression indexes pack more punch per byte.

Partial: index only the subset you query
Expression: index computed values
Both require IMMUTABLE predicates
The query must use the same predicate to match

Quick Check

You want case-insensitive lookups by email. Which index is most efficient?

Frequently asked questions

Is the “Partial and Expression Indexes” lesson free?

Yes — the full text of “Partial and Expression Indexes” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Partial and Expression Indexes”?

Index just the rows you care about with WHERE clauses, and index computed expressions (lower(email), date_trunc('day', ts)). You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Partial and Expression Indexes” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy