Section 2 of 3 · 6 minutes

Choose multicolumn order deliberately

Your goal

What you will be able to do

Explain which queries can use the leftmost columns of a multicolumn B-tree index.

Why this matters at work

The practical reason

The same set of indexed columns can perform very differently when their order changes.

Learn

The idea in plain English

For a B-tree index on `(a, b, c)`, PostgreSQL can efficiently start from conditions on `a`, then use `b` and `c` where appropriate. A query on `b` alone cannot normally seek through the index in the same way.

Place equality columns that consistently define the search first. Put a range or ordering column after them when that matches the target query. Validate with the plan rather than relying on a rule of thumb alone.

Remember these points

The leftmost columns define the main searchable prefix.
Equality conditions usually come before the range column.
One index rarely serves every ordering of the same columns.

See it in SQL

Inspect index column order

Read the exact index definition stored by PostgreSQL.

SELECT
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'training_orders'
ORDER BY indexname;

`pg_indexes` shows the stored definition, including column order, sort direction, expressions, and predicates.

Compare this definition with the target WHERE and ORDER BY clauses before deciding that an index is suitable.

What you should see

The definitions of all indexes on training_orders, ordered by index name.

Useful words

Important terms

Leftmost prefix: The leading column or columns of a multicolumn B-tree index.

Quick check · Not graded

Check your understanding

Which query best matches an index on (customer_id, created_at)?

Choose one answerA query filtered only by created_at.A query for one customer ordered or filtered by creation time.A query that does not read training_orders.

Your progress is saved to your signed-in account.