Database Indexing

Covering, partial, and expression indexes

A covering index contains the values needed by a query, potentially avoiding visits to the table’s row storage. Visibility rules can still require heap checks in MVCC engines. A partial index stores only rows matching a predicate, such as open orders, which reduces size when that subset is small. An expression index stores a computed search value, such as a normalized email. The query must use compatible expressions and predicates for the planner to benefit.

The heap is the table's own storage. A normal index lookup finds the key in the index and then reads the heap page for the rest of the row; a covering index skips that second read, a partial index indexes fewer rows, and an expression index stores a computed value such as LOWER(email) so the planner can use it when the query writes the same expression.

CREATE INDEX idx_cover ON orders(user_id, status)
INCLUDE (total, created_at);

SELECT total, created_at FROM orders
WHERE user_id = 42 AND status = 'shipped';

CREATE INDEX idx_active ON users(email)
WHERE deleted_at IS NULL;

CREATE INDEX idx_lower_email ON users(LOWER(email));

Composite column order

For recent rides, an index on rider_id, created_at, and id groups one rider’s entries together and orders them by time with a stable tie-breaker. Equality on rider_id narrows the region; the timestamp ordering lets the database retrieve the newest 20 without sorting that rider’s full history. A query for all rides in a time range has a different leading constraint and may need a different index or a scan. Engine-specific skip scans can sometimes use later columns, so “never usable without the first column” is too absolute.

A composite B-tree index is ordered lexicographically: the first key groups entries, the next orders within those groups, and so on. Leading equality filters usually make the strongest range restriction, followed by an appropriate range or ordering column. Later-column filters can still help, and some engines support skip scans, but efficiency depends on cardinality and the chosen plan. Verify the actual query instead of treating the leftmost-prefix rule as a universal prohibition. See PostgreSQL multicolumn-index documentation for the precise contract.

CREATE INDEX idx_optimal ON users(country, created_at, age);

Plans and EXPLAIN

Read estimated row counts beside actual counts to find where the planner’s assumptions diverge from reality. An index is not automatically faster when a query returns most rows: many scattered table lookups may cost more than one sequential scan. EXPLAIN ANALYZE executes the statement, so a mutating statement can actually change data. Validate safely on representative data and inspect buffers, sorts, and scanned rows rather than judging only the plan’s node names.

EXPLAIN prints the plan the planner picked; EXPLAIN ANALYZE runs the query and adds real row counts and timings. An ORM (object-relational mapper, the library that generates SQL from application code) hides the plan from you, which is exactly why you read it yourself. The two plans below are the same query on the same 50M-row table, before and after an index.

Seq Scan on orders (cost=0..1250000 rows=50)
  Rows Removed by Filter: 49,999,950
  Execution Time: 3200.000 ms  ← FULL TABLE SCAN

Index Scan using idx_user_status on orders
  Buffers: shared hit=4
  Execution Time: 0.052 ms  ← 60,000x FASTER

Monitoring usage

A plan proves an index works today; usage statistics prove it is still earning its write cost months later. Every major engine exposes a catalog view of scan counts and cache hits, which the pseudo-SQL below queries.

-- Find unused indexes with your engine's catalog stats
SELECT table_name, index_name, scan_count, size_bytes
FROM index_usage_stats
WHERE scan_count = 0
ORDER BY size_bytes DESC;

-- Monitor index cache hit ratio
SELECT index_cache_hits / NULLIF(index_cache_hits + index_cache_reads, 0)
  AS index_cache_hit_ratio
FROM database_io_stats;

Debugging a slow query

When a slow query lands on your desk, the same tools apply in a fixed order: ask what is slow, read the plan, then check the metrics that show whether a new index helped or hurt.