Indexes Composite Keys and Query Plans

01 The idea

The problem this lesson solves

An owner asks for the newest 20 links.

Prerequisite: earlier lessons introduce the vocabulary and shared examples used here. Follow the module order.

02 Worked example

Follow one worked example

Trace the example in order. Follow the same identities, units and state transitions through the diagram and the events below.

Choose an index for the filter and order of an important query, then verify the plan on representative data.

03 The method

Mechanisms decisions and a guided tutorial

Work through the explanation in order, then apply it in the tutorial. Each comparison names a cost as well as a benefit.

An index is a second structure to maintain

A B-tree index organizes key values so a query can find a relevant range without scanning every table row. It consumes disk and memory, and inserts, deletes or changes to indexed values must maintain it. Index every column and writes become more expensive without necessarily helping useful queries. On small tables, or when most rows match, a sequential scan can be the right plan. Use EXPLAIN to inspect the planned access path; EXPLAIN ANALYZE executes the statement, so practice it on disposable data.

Order a composite index for the hot query

For ‘this user's most recent bookings’, an index on (user_id, created_at DESC, id DESC) matches equality on user_id followed by ordered traversal. Leading columns generally determine how effectively a multicolumn B-tree narrows its scan. Avoid saying non-leading columns can never use an index: planners may use broader index scans and newer PostgreSQL versions can use skip scans in suitable cases. The useful question is how much work the actual plan performs for this data distribution.

Start with a concrete query

The owner’s recent-link page asks WHERE owner_id = ? ORDER BY created_at DESC, id DESC LIMIT 20. An index beginning with owner_id then the ordered tuple can narrow to the owner’s range and traverse recent rows. An unrelated index on destination does not help merely because the table now has an index.

A table scan reads many rows and filters them. An index can locate candidate keys quickly, but fetching many table rows through it may cost more than a sequential scan. Table size, distribution, cached pages and the fraction returned all matter. An index is an option for the planner, not a command to use it.

The leading columns shape the useful range

An index on (owner_id,created_at,id) groups each owner’s entries. Restricting owner_id makes the remaining order useful within that range. A query only by created_at may require broader work because the first grouping is unconstrained.

Avoid the absolute claim that skipping a leading column makes index use impossible. PostgreSQL can use broad scans and newer versions can apply skip scans in suitable distributions. The design lesson is to put the important access pattern in a strong position and inspect the actual plan rather than memorize an unconditional slogan.

Test reads and writes together

An insert must update the table and each relevant index. Indexed-column updates and deletes also require maintenance. More indexes use storage, cache and maintenance time; unused indexes are not free insurance.

Use EXPLAIN for the planned operations. EXPLAIN ANALYZE runs the query and reports actual timing and row observations, so use disposable data for write experiments. Compare estimates with actual rows and keep statistics representative. A plan on ten toy rows cannot prove behavior on fifty million rows.

Guided tutorial

1. Create practice rows

Use a disposable table with owner_id, created_at and id.

2. Inspect without an index

Run EXPLAIN on the listing query.

3. Add the candidate

Create the composite index and inspect again.

4. Vary the distribution

Compare one owner with many rows and many owners with few rows; explain why results may differ.

-- Run setup once in a disposable PostgreSQL database.
CREATE TABLE sd_lab_links (
  id integer PRIMARY KEY, owner_id integer NOT NULL,
  created_at timestamp NOT NULL
);
INSERT INTO sd_lab_links
SELECT i, i % 100, timestamp '2026-01-01' + i * interval '1 second'
FROM generate_series(1,10000) AS i;
ANALYZE sd_lab_links;
EXPLAIN SELECT id, created_at FROM sd_lab_links
WHERE owner_id = 7 ORDER BY created_at DESC, id DESC LIMIT 20;
CREATE INDEX sd_links_owner_recent
ON sd_lab_links (owner_id, created_at DESC, id DESC);
EXPLAIN SELECT id, created_at FROM sd_lab_links
WHERE owner_id = 7 ORDER BY created_at DESC, id DESC LIMIT 20;

04 Cheat sheet

Review the decisions

Use this table to explain the mechanism and its limit without memorizing a product name.

05 Where & why

Check the primary references

Use official references for protocol and implementation details. The questions here are teaching prompts.

06 Interview questions

Explain the reasoning out loud

Answer before opening the explanation. The questions progress from vocabulary to decisions and failure behavior.

07 Practice problems

Practice a new case

Solve the new cases before revealing hints. Complete worked solutions follow the set.

Worked solutions