SQL Academy · Lesson

Composite Indexes and Column Order

Pick the column order for composite indexes, understand the left-prefix rule, and design indexes that cover multiple queries.

Composite Indexes and Column Order is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a Composite Index?

An index on more than one column. PostgreSQL B-trees can index up to 32 columns; in practice 2–4 is typical.

CREATE INDEX orders_user_created_idx ON orders(user_id, created_at DESC);

Why Use Composite?

Two scenarios:

The query filters on multiple columns at once
The query filters on one column and sorts by another

Leftmost-Prefix Rule

An index on (a, b, c) can serve:

WHERE a = ?
WHERE a = ? AND b = ?
WHERE a = ? AND b = ? AND c = ?

It does NOT serve WHERE b = ? alone, nor WHERE c = ?.

Order Matters

Put the most selective AND most frequently filtered column first. Equality columns before range columns:

CREATE INDEX orders_user_total_idx ON orders(user_id, total);

-- Best for:  WHERE user_id = 42 AND total > 100
-- Also OK:  WHERE user_id = 42
-- Useless:  WHERE total > 100   (without user_id)

Equality vs Range

Equality predicates use the index efficiently; ranges use it but limit further usefulness. Equality columns should come first:

-- Index: (status, created_at)
WHERE status = 'paid' AND created_at >= ...   -- both columns used
WHERE created_at >= ... AND status = 'paid'  -- index used for status, range scan on created_at

Index Order = Sort Order

An index on (user_id, created_at DESC) serves "user's orders, newest first" without a sort step:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC LIMIT 10;
-- Index Scan, no Sort node needed

Including Non-Key Columns (INCLUDE)

PG 11+ supports INCLUDE for "covering" indexes — extra columns stored in leaves but not used for ordering:

CREATE INDEX orders_user_idx ON orders(user_id)
  INCLUDE (total, status);

-- Enables Index-Only Scan for queries that need just these columns.

When NOT to Make Composites

If the columns are queried independently, two single-column indexes can serve more queries. Composites trade flexibility for "one query has it all".

Bitmap Index Combination

PostgreSQL can combine multiple single-column indexes via Bitmap Index Scan:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';
-- BitmapAnd of two index scans — works without a composite, but slower than the right composite.

Watch Index Bloat

Composite indexes are wider. Wider indexes = larger files = less of the index fits in cache. Be deliberate.

Picking Column Order — A Worked Example

Query: "orders for one user in a date range, ordered by date".

-- Index design:
CREATE INDEX orders_user_created_idx
  ON orders(user_id, created_at DESC);

-- Query (efficiently uses the index):
SELECT * FROM orders
WHERE user_id = 42
  AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC LIMIT 10;

Recap

Composite indexes serve multi-column filters and avoid sort steps.

Leftmost-prefix rule
Equality columns first
INCLUDE for cover-only columns
Two singles vs one composite — depends on query mix

Quick Check

You have an index on (user_id, status, created_at). Which query CANNOT use it efficiently?

Frequently asked questions

Is the “Composite Indexes and Column Order” lesson free?

Yes — the full text of “Composite Indexes and Column Order” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Composite Indexes and Column Order”?

Pick the column order for composite indexes, understand the left-prefix rule, and design indexes that cover multiple queries. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Composite Indexes and Column Order” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy