An index is a separate data structure that lets PostgreSQL find rows without scanning an entire table. Without one, answering 'find the order with id 91827' in a million-row table means reading every row — a sequential scan. With the right index, PostgreSQL jumps almost directly to the matching rows. Indexes are the single most important lever for query performance, and PostgreSQL offers several types, each suited to a different access pattern.
The catch is that indexes are not free. Every index must be kept in sync on every insert, update, and delete, and each one consumes disk space and memory. So indexing is a deliberate trade: faster reads in exchange for slower writes and more storage. Choosing which columns to index, and with which index type, is where database performance is won or lost.
The five index types you will meet most are B-Tree for ordered, comparable data; Hash for pure equality; GIN for values containing many elements like arrays, JSONB, and full-text; GiST for geometric, range, and nearest-neighbour data; and BRIN for very large, naturally ordered tables. Knowing what each is good at is the foundation of every optimisation that follows.
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.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.