0PricingLogin▼
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 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

All lessons in this course

  1. B-tree vs Hash vs GiST vs GIN Indexes
  2. Composite Indexes and Column Order
  3. Partial and Expression Indexes
  4. Index Maintenance and Bloat
← Back to SQL Academy