Partial Indexes and Expression Indexes in PostgreSQL: The Query Optimization Patterns That Cut Our Mobile API P99 Latency by 80%

TL;DR

Most teams over-index their PostgreSQL tables with generic B-tree indexes that bloat WAL, slow writes, and barely help reads. Replacing three broad indexes with two targeted partial indexes dropped a critical mobile API endpoint from 200ms P99 to 12ms. This post covers the exact patterns (partial indexes for soft deletes, expression indexes for JSONB, composite column ordering) with real EXPLAIN ANALYZE output and the diagnostic queries that tell you which indexes to kill.

---

Why index optimization compounds

Paul Graham writes about superlinear returns, where the gap between “good” and “great” isn’t linear but exponential. Database indexing works this way. A mediocre index strategy gets you from table scans to “acceptable.” But a precise index strategy delivers returns that compound: less I/O means smaller working sets, which means more fits in shared_buffers, which means fewer disk reads across all queries.

What most teams get wrong: they treat indexing as a checkbox exercise. Column shows up in a WHERE clause? Add an index. The result is index bloat that punishes writes and barely helps reads.

Pattern 1: Partial indexes for soft-deleted rows

The single highest-ROI index change I’ve made in production is the partial index on active records. Consider a typical orders table where 92% of rows are soft-deleted or completed, but the API only ever queries active ones:

-- Before: generic index scanning millions of dead rows
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- After: partial index on the 8% that matters
CREATE INDEX idx_orders_user_active ON orders (user_id, created_at DESC)
  WHERE status = 'active' AND deleted_at IS NULL;

Look at the EXPLAIN ANALYZE output before and after:

Metric	Generic B-tree	Partial Index
Index size	847 MB	68 MB
Index scan rows examined	~240,000	~1,200
Execution time (P99)	189 ms	11 ms
Heap fetches	240,012	0 (index-only scan)

The partial index is 12x smaller because it excludes rows the application never queries. Smaller index means it lives in memory. Living in memory means no disk I/O. The gains compound from there.

Pattern 2: Expression indexes for JSONB fields

Mobile APIs love JSONB columns for flexible metadata. But querying into JSONB without an expression index produces sequential scans that destroy latency at scale:

-- Expression index on a JSONB field
CREATE INDEX idx_devices_platform ON devices ((metadata->>'platform'))
  WHERE metadata->>'platform' IS NOT NULL;

-- Now this query hits the index
SELECT * FROM devices
WHERE metadata->>'platform' = 'ios' AND active = true;

I see teams reach for a GIN index on the entire JSONB column when they only ever query two or three keys. A targeted expression index on the specific path is an order of magnitude smaller and faster.

Pattern 3: Composite index column ordering

Column order in composite indexes is not alphabetical. It’s architectural. The rule: equality columns first, range columns last, sort columns matching query order.

-- Wrong: range column first forces broader scan
CREATE INDEX idx_bad ON events (created_at, tenant_id, event_type);

-- Right: equality predicates first, range last
CREATE INDEX idx_good ON events (tenant_id, event_type, created_at DESC);

Query Pattern	Wrong Order (rows scanned)	Right Order (rows scanned)
tenant_id = X AND event_type = Y AND created_at > Z	~85,000	~340
Index-only scan possible	No	Yes (with INCLUDE)

Diagnosing index bloat: the queries that matter

Before adding any index, run this against pg_stat_user_indexes to find what’s already dead weight:

-- Find unused indexes (excluding unique constraints)
SELECT schemaname, relname, indexrelname,
       idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;

On a recent production database serving a mobile app, this query surfaced 11 unused indexes consuming 3.2 GB. Dropping them reduced INSERT latency by 23% because every write was maintaining phantom indexes that nothing read.

Then find your load-bearing indexes, the ones that justify their write overhead:

-- Most-used indexes by scan count
SELECT indexrelname, idx_scan, idx_tup_read,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 20;

The covering index endgame

Once your partial index is narrowed to the right rows, add INCLUDE columns to enable index-only scans and eliminate heap fetches entirely:

CREATE INDEX idx_orders_user_covering ON orders (user_id, created_at DESC)
  INCLUDE (order_total, status)
  WHERE status = 'active' AND deleted_at IS NULL;

This took the endpoint from 12ms to consistently single-digit. Zero heap fetches means the query never touches the table.

What to actually do with this

Audit before you add. Run the pg_stat_user_indexes queries above. Drop unused indexes first. They’re free write performance you’re leaving on the table.

Default to partial indexes. If your query filters out more than 50% of rows (soft deletes, status checks, tenant isolation), a partial index will outperform a generic one by an order of magnitude at a fraction of the storage.

Treat column order as architecture. Equality columns first, range columns last. And always benchmark with EXPLAIN (ANALYZE, BUFFERS), not EXPLAIN alone. Buffer counts reveal the I/O reality that estimated costs hide.

A 12x smaller index doesn’t give you 12x faster queries. It gives you 16x faster queries because memory residency, reduced I/O contention, and faster vacuum cycles all stack. Index less, but index precisely.