Index Scan

An Index Scan walks a B-tree to the entries that match the Index Cond, then fetches each matching row from the table, in index order. In the sample, Index Scan using order_items_order_idx on order_items finds one order's items, 3 rows per lookup, and runs 206,880 times because a Nested Loop sits above it. Index Scan using customers_pkey on customers runs 620,640 times, one row each.

Why it matters

It is the right plan for a few rows out of many: one customer's orders, a primary key lookup. It goes wrong in two ways. Too many rows, and fetching them one by one costs more than a Seq Scan. Too many loops, and a cheap lookup becomes expensive by repetition: 3 rows × 206,880 loops is 620,640 fetches. Physical order matters too. order_items.order_id has a correlation of 0.97, so one order's items share a page. customers.id has 0.01, so each of the 620,640 lookups lands on a page of its own.

What to do

1. Look at the loop count before the row count. 206,880 loops is a planner decision: it expected 612. ANALYZE commerce.orders corrects that and the planner switches to a Hash Join.
2. A Filter line under the Index Cond means the index does not cover the whole WHERE. Add the filtered column, equality columns first.
3. A scan returning a large share of the table is the planner's call. With fresh statistics it picks a Bitmap Heap Scan or a Seq Scan on its own.

In the Plan tab

The wheel's hub is the index and the spokes are the lookups. The sample has one Index Scan of each kind, and the TABLE WHEEL guide shows all five ways a scan reaches a table so you can match yours.

Under the bands, ↻ ×207K and ↻ ×621K are the loops, and the inspector's Rows per loop ruler reads 3 and 1.