Indexes & Query Performance

The Index Mental Model

An index is a maintained, redundant access path from searchable keys to rows. It can replace broad inspection with targeted navigation, but every extra structure consumes space and write work, and the planner may rationally choose another path.

Logical promise and physical structure differ.
An index maps keys to row locators or stored rows.
Access has navigation and fetch phases.
Every write maintains applicable indexes.
An index is useful only through supported operators.
Benefit belongs to a workload, not a table.

Commerce lookup and maintenance paths

Cost ledger for `(customer_id, status, placed_at)`
Operation	Potential gain	Potential cost
Recent paid-order lookup	Navigate to one ordered customer/status interval	Tree pages plus possible heap fetch
Customer range	Read adjacent matching entries	Many locators can mean scattered fetches
Insert order	New searches become possible immediately	Extra search, WAL/logging, page modification
Change indexed status	New searches become possible	Old entry removal and new entry insertion
Delete	Future lookup avoided	Logical deletion, vacuum/purge or merge work varies

One canonical commerce access path

CREATE INDEX orders_customer_status_placed_idx
  ON orders (customer_id, status, placed_at DESC);

SELECT order_id, status, placed_at
FROM orders
WHERE customer_id = 7 AND status = 'PAID'
ORDER BY placed_at DESC;

The optimizer compares alternatives rather than obeying the mere existence of this index. If customer 7 owns most rows, pages are already cached, or the query returns most columns and rows, scanning may cost less than navigating and fetching many scattered tuples.