Section 1 of 3 · 7 minutes
Match an index to the query
Your goal
What you will be able to do
Identify the filter, join, and ordering columns that determine a useful access path.
Why this matters at work
The practical reason
PostgreSQL can use an index only when its structure matches the work the query asks for.
Learn
The idea in plain English
Start with the query, not the table. Mark the columns used for equality filters, range filters, joins, and ordering. Then consider how many rows each condition is likely to match.
A B-tree index is the usual choice for equality, ranges, and ordered access. Specialist indexes such as GIN or GiST serve different operators and data types. Do not choose one from the column type alone.
Remember these points
- Design from an important query and its workload.
- B-tree supports common equality, range, and ordering needs.
- An index that returns most of a table may not be useful.
See it in SQL
Create an index for open customer orders
Support equality on customer, equality on status, and newest-first ordering.
CREATE INDEX training_orders_customer_status_created_idx
ON training_orders (customer_id, order_status, created_at DESC);The leading customer column supports queries scoped to one customer. Status narrows that customer's rows, and the final column can provide the requested order.
This does not automatically help a query filtered only by `created_at`; the leftmost columns matter for a multicolumn B-tree.
A multicolumn B-tree index whose order matches the target query's filters and sorting.
Useful words
Important terms
- Selectivity
- How strongly a condition reduces the number of matching rows.
- Access path
- The method PostgreSQL uses to reach rows, such as a table scan or index scan.
Quick check · Not graded
Check your understanding
What should drive the design of a new index?
Your progress is saved to your signed-in account.