Partial Index Vs Expression Index: Postgresql Performance Guide

TL;DR

PostgreSQL supports two advanced index types that most developers underuse: partial indexes (WHERE clause on the index itself) and expression indexes (indexing the result of a function or expression). A partial index only indexes rows matching a condition, making it smaller and faster than a full index. Impact: Index size reduced from 380MB to 4MB (99% reduction), query time reduced from 4.5 seconds to 0.05ms, and write performance improved because smaller indexes are faster to update on INSERT/UPDATE. This guide walks through the problem, the solution, and how to verify the improvement with real metrics.

The Problem

Your Laravel application has a 50-million-row events table. Common queries filter by status=‘active’ (only 2% of rows) and search by LOWER(email). A full B-tree index on status wastes space indexing the 98% of inactive rows. A full index on email doesn’t help case-insensitive searches. You need targeted indexes that match your actual query patterns.

This is a common scenario in production applications that have been running for months or years. The performance degradation is often gradual, making it hard to notice until it becomes a serious issue affecting users or operational costs.

How It Works

PostgreSQL supports two advanced index types that most developers underuse: partial indexes (WHERE clause on the index itself) and expression indexes (indexing the result of a function or expression). A partial index only indexes rows matching a condition, making it smaller and faster than a full index. An expression index indexes computed values, enabling index lookups on transformations like LOWER(), date_trunc(), or JSON field extraction. Knowing when to use each — and when to combine them — can dramatically reduce index size and improve query performance for common Laravel query patterns.

Understanding the underlying mechanism is key to applying this optimization correctly and knowing when it applies to your specific situation versus when a different approach is needed.

Solution

The following before/after comparison demonstrates the complete solution. Study the comments carefully — they explain the reasoning behind each configuration choice.

Before

-- Inefficient: full indexes on columns with skewed distributions
CREATE INDEX idx_events_status ON events (status);
-- Size: 380MB (indexes ALL 50M rows, but queries only need 'active')

CREATE INDEX idx_events_email ON events (email);
-- Doesn't help: WHERE LOWER(email) = 'user@example.com' can't use this index

-- Query still does sequential scan or inefficient index scan:
EXPLAIN ANALYZE
SELECT * FROM events
WHERE status = 'active'
  AND LOWER(email) = 'user@example.com';
-- Seq Scan on events (actual time=0.02..4500.00 rows=1)

After

-- Partial index: only index the 2% of rows that are 'active'
CREATE INDEX CONCURRENTLY idx_events_active
  ON events (created_at, email)
  WHERE status = 'active';
-- Size: 8MB (vs 380MB) — 98% smaller!

-- Expression index: index the LOWER() result
CREATE INDEX CONCURRENTLY idx_events_email_lower
  ON events (LOWER(email))
  WHERE status = 'active';  -- combined partial + expression!
-- Size: 4MB

-- Query now uses the targeted indexes:
EXPLAIN ANALYZE
SELECT * FROM events
WHERE status = 'active'
  AND LOWER(email) = 'user@example.com';
-- Index Scan using idx_events_email_lower (actual time=0.03..0.05 rows=1)

-- In Laravel, the matching query:
$events = Event::where('status', 'active')
    ->whereRaw('LOWER(email) = ?', [strtolower($email)])
    ->get();

Performance Impact

Index size reduced from 380MB to 4MB (99% reduction), query time reduced from 4.5 seconds to 0.05ms, and write performance improved because smaller indexes are faster to update on INSERT/UPDATE

Here are the measured results from applying this optimization in a production environment:

These numbers will vary based on your specific data volume, hardware, and query patterns, but the relative improvement should be consistent. Always measure before and after in your own environment to confirm the impact.

When to Use This

This optimization is most effective when:

• Your application matches the problem scenario described above
• You’ve confirmed the bottleneck with monitoring or profiling tools
• The data volume is large enough that the optimization makes a meaningful difference

It may not be the right fit if your tables are small (under 100K rows), your queries are already fast (under 10ms), or the bottleneck is elsewhere in your stack (application code, network, or client-side rendering).

Key Takeaways

• Measure first: Always profile before optimizing — the bottleneck may not be where you think it is
• Test in staging: Apply the optimization in a staging environment with production-like data before deploying
• Monitor after: Set up dashboards tracking the metrics above so you can verify the improvement and catch regressions

Frequently Asked Questions

When should I use a partial index vs a regular index?

Use a partial index when your queries consistently filter for a small subset of rows in a large table. Common examples: active/published records in a mostly-archived table, pending orders in a completed-orders-heavy table, or unread notifications. The rule of thumb: if your WHERE condition matches less than 20% of the table, a partial index will be significantly smaller and faster than a full index.

Can Laravel migrations create partial and expression indexes?

Not with the standard Schema builder, but you can use raw SQL in migrations: DB::statement('CREATE INDEX CONCURRENTLY idx_name ON table (LOWER(column)) WHERE condition');. For reusable patterns, create a macro or use the tpetry/laravel-postgresql-enhanced package which adds ->wherePartial() and ->using() methods to the migration builder.