Consider SELECT email FROM users WHERE user_id = 42 with a B-tree index on user_id. This is a plausible plan, not a promise: PostgreSQL may choose a sequential scan if its estimates say that is cheaper.
1. Parse and plan
PostgreSQL parses the statement, resolves names and types, applies transformations, and considers physical plans. Statistics help estimate selectivity and row counts. The planner compares startup and total costs, which are estimates in internal cost units rather than measured milliseconds.
2. Walk the index
If an index scan is selected, the executor asks the buffer manager for index pages and follows B-tree separators toward the leaf range for key 42. Pages may be in shared buffers or need storage reads. The leaf entry identifies candidate table tuple locations.
3. Check the table row
An ordinary index scan may visit the heap page to retrieve email and check transaction visibility under MVCC. An index-only scan may avoid some heap visits when the needed columns are in the index and the visibility map permits it. Dead tuples, cache state, and concurrent updates affect actual work.
4. Return rows
Qualifying rows flow to the client through the executor and protocol layers. EXPLAIN shows the selected plan; EXPLAIN ANALYZE executes the statement and reports observations with instrumentation overhead. Compare estimated and actual row counts before changing indexes.
PostgreSQL's EXPLAIN guide documents plan nodes, cost estimates, and the difference between estimated and observed work.