Index Design: Partial, Composite, and Expression Indexes

Knowing the index types is only half the skill; designing the right index for a specific query is the other half. PostgreSQL lets you go well beyond 'index this column': you can index a subset of rows, a combination of columns in a deliberate order, the result of an expression, or include extra columns purely to satisfy a query from the index alone. These techniques are how you make an index match a query precisely.

A precisely designed index is smaller, faster, and more likely to be chosen by the planner than a blunt single-column one. The goal is to give the planner an index whose entries align exactly with how a hot query filters, joins, and orders — so it can do an efficient index scan, ideally an index-only scan, instead of falling back to the heap or a sequential scan.

This lesson covers composite indexes and the critical role of column order, partial indexes that index only the rows you care about, expression indexes for queries that filter on a computed value, and covering indexes that carry extra columns. Each targets a common situation where a naive index would be ignored or inefficient.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.