An index supplies another access path
An index is an auxiliary structure for locating rows without inspecting every row in the same way. A B-tree can exploit value ordering for equality or range conditions. Creating one provides a possible access path; it does not guarantee that every query will become faster or even use that index.
Fetching a few rows and fetching almost the entire table are different jobs. Following many index entries to table rows may cost more than reading the table sequentially. Do not label every Index Scan a success and every Seq Scan a failure. Ask how much work the query needs and why the planner selected that path.
Relate column order to the query
An orders query may filter by user_id and a range of created_at values while requesting time order. The order of columns in a composite index interacts with these conditions. Having an index somewhere on every column does not make all access patterns equivalent.
The example combines an equality condition and a time range. A matching composite index is a candidate to evaluate, not an automatic performance promise. Avoid universal claims that a later indexed column can never be useful by itself: available scan strategies vary by product and version, and the planner also uses statistics and cost estimates.
EXPLAIN SELECT order_id, created_at
FROM orders
WHERE user_id = 42
AND created_at >= TIMESTAMP '2026-01-01'
ORDER BY created_at;Separate estimated plans from execution
EXPLAIN introduces the selected plan tree and its estimates. A large difference between estimated and actual row counts can influence choices elsewhere, including joins. Consider selectivity, skew, and statistics instead of memorizing node names as a list of good and bad operations.
EXPLAIN ANALYZE executes the statement. Even a read creates load, and a modifying statement can change data. Begin with ordinary EXPLAIN and choose a suitable test environment for measurements. Planner cost values are model quantities, not generally a direct report of elapsed milliseconds.
Include maintenance in the decision
Indexes consume storage and require maintenance as rows are inserted, updated, or deleted. Adding an index for every imagined query can increase write work and operational burden. Review real workload frequency, redundant structures, and actual usage alongside the benefit to reads.
Cache state, I/O, concurrency, visibility, and data volume affect results. A specific speedup cannot be guaranteed without those conditions. Compare before and after under explicit conditions, and check whether improving one query made another important part of the workload worse. An index decision is a workload decision, not a slogan.
Account for repeated execution of a plan node
In an illustrative EXPLAIN ANALYZE node, actual rows=2 and loops=1000 imply approximately 2000 output rows overall. For repeated execution, displayed actual rows is an average per execution. An estimate of rows=1 therefore differs by a factor of two in the same units, not by 2000. Display rounding makes the multiplication approximate.
A parent node includes work performed by its children, so adding every node time also double-counts work. Investigate why loops multiply, where filtering discards rows, and where estimates diverge before choosing an index experiment. These figures demonstrate interpretation; they do not report an observed plan. Compare changes under consistent data and conditions, including unchanged result semantics.