The planner's choice
This query asks for the row of tenk1 whose unique1 is 42. It says what to find, not how to find it. Before anything runs, a part of PostgreSQL called the planner must choose how.
First the planner guesses how many rows will match. Its statistics say tenk1 holds 10,000 rows on 345 pages, and no two unique1 values are the same. So it expects 1 row.
Plan A is a sequential scan: read all 345 pages in order and test all 10,000 rows. Its estimated cost is 345 × 1.0 for the pages plus 10,000 × 0.0125 for the rows: 470.
Plan B is an index scan. It walks the B-tree index down to 42, then reads the one table page that holds the row. Each jump to a random page costs 4.0, so the estimate is about 8.30.
The planner compares the estimates and keeps the cheapest: 8.30 beats 470. Cost is not time. Its unit is one page read in order, so 470 means about 470 such reads of work.
Reading a plan
Put EXPLAIN in front of a query and PostgreSQL prints the plan instead of running it. cost=0.29..8.30 is the cost before the first row, then the total. rows=1 is the guess; width=244 is bytes per row.
Without EXPLAIN, PostgreSQL's executor runs the chosen plan: into the index, down to 42, across to one table page, and back with the row. That is 2 index pages and 1 table page, not all 345.
EXPLAIN ANALYZE really runs the query and prints what happened beside each estimate: actual rows=1, as guessed. Its times are in milliseconds and change from run to run, so compare rows with rows, not cost with time.
When the best plan changes
Now ask for unique1 < 7000. The statistics predict 7,000 rows. Fetching them through the index means jumping between table pages again and again, so one sequential sweep, still 470, is now the cheapest plan.
Every choice rests on row estimates from statistics that ANALYZE collects. Missing or stale statistics mean a bad guess, and maybe a bad plan. SQLite shows it: before ANALYZE it uses the index here; after, it scans.
What happens
;; step 1/10 · query
the app sends:
SELECT *
FROM tenk1
WHERE unique1 = 42
tenk1 10,000 rows; unique1 holds 0 .. 9999,
each value once, in no particular order
tenk1_unique1 a B-tree index on unique1
the planner must choose HOW to find the row
The costs are the ones printed in the PostgreSQL 17 documentation for its tenk1 test table, with the default cost settings. Your server may print slightly different numbers: statistics come from samples, and costs depend on the version and platform. Which table page holds unique1 = 42 depends on how the rows were loaded; the highlighted page in the figure is illustrative.