PostgreSQL Indexing and Query Optimization: From EXPLAIN to Compound Indexes
A practical method for reading EXPLAIN ANALYZE, choosing B-tree and partial/covering indexes, and avoiding write-heavy index sprawl.
Ali Mortazavi
Founder, Paradise Code
Slowness is often the wrong index, not a missing one
Indexing every filtered column taxes writes and vacuums without saving reads. Good indexes come from real WHERE, JOIN, and ORDER BY shapes—not guesses. Find frequent slow queries in logs/`pg_stat_statements` first, then design indexes for those patterns.
The goal is fewer pages read and fewer unnecessary sorts/seq scans—not “many indexes.”
Reading EXPLAIN ANALYZE without mystique
Do not panic at `Seq Scan`; on small tables it can be correct. Danger appears when estimated vs actual rows diverge, or a `Nested Loop` hammers a large set at high cost. Look at `Buffers` and real time per node to separate I/O from CPU/sort pain.
Stale statistics are a silent killer. After bulky data changes, take `ANALYZE` seriously. If the planner disagrees with reality, fix stats and selectivity before adding another index.
B-tree, compound indexes, and column order
For equality and range on scalars, B-tree is the right default. In compound indexes, column order should match frequent equality filters first, then range/sort columns. `(tenant_id, created_at)` fits “one tenant in a time range”; the reverse often does not.
When a hot query returns few columns, a covering/index-only scan with `INCLUDE` can cut heap fetches. Measure it on hot paths—do not apply it to every SELECT.
Partial and expression indexes for SaaS
In multi-tenant systems, partial indexes like `WHERE deleted_at IS NULL` or `WHERE status = 'active'` keep indexes small and relevant. Expression indexes on `LOWER(email)` matter for case-insensitive lookup—only if queries use the same expression.
Name partial indexes after the product access pattern so the team knows why they exist. Ownerless indexes become orphaned across migrations.
Locks, writes, and index maintenance cost
Every extra index taxes INSERT/UPDATE/DELETE. On write-heavy tables, two correct medium indexes beat six “might need later” indexes. For large-table migrations use `CREATE INDEX CONCURRENTLY` so long locks do not take the service down.
Monitor bloat. Bloated indexes slow reads too. On append-heavy tables, time-based partitioning often beats one giant index.
Rewrite the query before adding more indexes
Sometimes the index is not the bug: `SELECT *`, ORM N+1s, or filtering on a client-computed column. Push filters into SQL, make joins explicit, and replace deep OFFSET pagination with keyset pagination. OFFSET 100000 is repeated wasted work.
For heavy reporting, split the online path: summary tables, materialized views, or an analytics store. Do not strangle OLTP with BI scans.
A practical optimization loop for teams
Review top queries weekly: p95, calls, and plan changes. Every new index should record before/after queries and a business reason. That discipline prevents folklore indexes.
Sustainable optimization is part of Done: a feature with a new filter and no index plan is intentional debt.
Frequently asked questions
Should every foreign key get an index?
If you JOIN/DELETE/WHERE on it, usually yes. A FK alone does not guarantee a separate index—decide with measurement.
When is GIN the right choice?
For JSONB, arrays, and full-text search. For simple equality on scalar columns, B-tree is cheaper and more appropriate.
Why do I still see seq scans after indexing?
The table may be small, the predicate not sargable, stats stale, or selectivity too low. Re-check EXPLAIN ANALYZE with real filters.
Insights
Need these ideas implemented in your product?
Paradise Code supports you from consult to full delivery.