Optimizing Query Performance with Indexing Strategies

A query that returns in 40 milliseconds against your 5,000-row development database can take 4 minutes against the same schema in production at 8 million rows. The SQL is identical. The schema is identical. The only difference is volume—and without an index, the database has no option but to read every row in the table to answer the question. Indexing is the most impactful single change available for read performance, and it is also the most commonly over-applied and misconfigured.

The instinct is usually to add indexes everywhere on columns that appear in WHERE clauses. That instinct produces tables with 12 indexes, slow INSERT performance, bloated storage, and query planners that sometimes ignore the indexes anyway. The right approach starts with measurement—EXPLAIN ANALYZE—and then builds the minimum set of indexes that eliminates the expensive scans.

What an Index Actually Does

An index is a separate data structure—most commonly a B-tree—maintained alongside your table. Instead of scanning every row to find customer_id = 10042, the database descends the B-tree in O(log n) time and jumps directly to the matching rows. The cost is write overhead: every INSERT, UPDATE, or DELETE that touches an indexed column must also update the index. Reading gets faster; writing gets slightly slower. How much slower depends on how many indexes exist and how frequently the table is written.

Five Index Types and When to Use Each

The Diagnostic Workflow: EXPLAIN ANALYZE

Never guess whether a query is using an index. EXPLAIN ANALYZE executes the query and shows the actual execution plan, row estimates, and timing. The example below runs the same query before and after adding a composite index on a five-million-row orders table.

1-- No index on customer_id or status: full sequential scan
2EXPLAIN ANALYZE
3SELECT order_id, total, created_at
4FROM   orders
5WHERE  customer_id = 10042
6  AND  status = 'pending';
7
8-- Seq Scan on orders  (cost=0.00..98542.00 rows=4 width=28)
9--                     (actual time=0.05..1843.21 rows=4 loops=1)
10-- Planning Time: 2.1 ms  |  Execution Time: 1843.2 ms
11
12-- Create composite index — higher-cardinality column first
13CREATE INDEX idx_orders_cust_status
14    ON orders (customer_id, status);
15
16-- Same query — planner now uses the index
17EXPLAIN ANALYZE
18SELECT order_id, total, created_at
19FROM   orders
20WHERE  customer_id = 10042
21  AND  status = 'pending';
22
23-- Index Scan using idx_orders_cust_status on orders
24--   (cost=0.56..8.62 rows=4 width=28)
25--   (actual time=0.028..0.033 rows=4 loops=1)
26-- Planning Time: 0.3 ms  |  Execution Time: 0.1 ms

The Composite Index Column Order Rule

This is the rule most developers get wrong once and never forget. An index on (customer_id, status) supports queries that filter on customer_id alone, or on customer_id AND status together. It does not support queries filtering on status alone—because the leading column is absent. The leftmost prefix must be present. Put the column used in the most queries first, and within that, prefer higher cardinality. An index on (status, customer_id) is a completely different object with completely different query coverage.

Four Mistakes That Cost More Than They Help

Over-indexing. Each index on a table adds overhead to every write. A table with ten indexes and high INSERT volume will show measurable write latency in production that never appeared in testing. Start from EXPLAIN output on your slowest queries—not from the column list—and build only what those queries need. Periodically audit with pg_stat_user_indexes to find indexes with zero scans.

Indexing low-cardinality columns. A B-tree index on a boolean column or a status field with three values rarely helps. When only two or three distinct values exist, an index scan may read nearly as many rows as a sequential scan—and the query planner often ignores the index entirely, choosing the scan anyway. Index columns with high cardinality: unique IDs, email addresses, timestamps, foreign keys.

Ignoring partial indexes. If 95% of your queries filter on WHERE status = 'active', a partial index — CREATE INDEX ... WHERE status = 'active' — covers only those rows. It is a fraction of the size of a full-table index, faster to traverse, and imposes less write overhead. Most developers create full indexes by habit and never consider the partial alternative.

Indexing without checking the query planner. Adding an index does not guarantee the planner will use it. Stale table statistics, low row estimates, or a planner cost miscalculation can cause the database to choose a sequential scan even when an appropriate index exists. Run ANALYZE <tablename> to refresh statistics and re-run EXPLAIN ANALYZE to confirm the plan changed.

Key Takeaways