Indexes Composite Keys and Query Plans

01 The idea

The problem this lesson solves

An owner asks for their twenty newest links. A table has a million rows, but only a small fraction belong to that owner. The database can inspect the whole table and sort the matches, or use an ordered structure to reach the relevant region and stop early. An index changes the work required by a query; it is not a universal “make database fast” switch.

You should recognize API resources and failure responses. SQL examples introduce their own tables and constraints.

02 Worked example

Find one owner’s 20 newest links

03 The method

Understand the mechanism

Understand what the index adds

A table stores rows. An index is an additional structure organized by selected keys, with enough information to locate or sometimes answer for matching rows. A B-tree keeps keys in an order that supports navigation to a value or range without starting at the first table row.

Imagine a printed book. A directory organized by owner and date lets you find one owner’s recent entries without reading every page. The analogy has limits: database indexes have physical pages, caches, concurrency and maintenance costs. The important idea is that you pay to maintain another access path.

An index can reduce rows examined, avoid a sort, support a uniqueness rule or enable a more efficient join. It does not automatically reduce the size of the returned result or eliminate network transfer. A query returning most of the table may remain expensive even with an index.

Every indexed insert, deletion or relevant update must maintain that structure. Index pages occupy disk and compete for cache memory. Adding indexes without a workload can slow writes and increase operational cost while providing little read benefit.

Build the composite key from the query

Our query filters owner_id=7 and orders by created_at DESC, id DESC with LIMIT 20. An index on (owner_id,created_at DESC,id DESC) groups one owner’s entries and orders that group the way the query needs.

Conceptually, the database can navigate to owner 7’s range and read the first twenty qualifying entries in order. The id tie-breaker makes equal timestamps deterministic. This same ordering supports cursor pagination using the last returned timestamp and id.

Column order is part of the access path. An index starting with created_at can be useful for globally recent links, but it does not organize one owner’s full group in the same way. The right key order depends on the predicates and ordering of the hot query.

Avoid an absolute rule that non-leading columns can never use a multicolumn index. PostgreSQL can choose several forms of index access, including suitable skip scans in versions that support them. The practical question is how much work the actual plan performs, not whether the word Index appears anywhere in its name.

Account for selectivity and data distribution

Selectivity describes how much of the data a condition keeps. If owner 7 has only 100 rows among 10,000, narrowing to that owner may help substantially. If a status filter matches 95% of the table, using an index for each row can be less attractive than a sequential scan.

Distribution can be skewed. Most owners may have ten links while one large customer has millions. A plan that works well for a typical owner may perform differently for the large one. Statistics help the planner estimate row counts, but stale or incomplete statistics can lead to poor choices.

Small tables are another case where a sequential scan can be efficient. Reading a few pages directly may cost less than navigating an index and then fetching rows. Forcing an index merely to make a plan look sophisticated is not a performance goal.

Benchmark representative data and parameter values. Include the large tenant, recent hot range and common filters. If a service only tests tiny random data, it may miss the skew that drives production tail latency.

Read a plan as a description of work

EXPLAIN shows the planned operations without executing the query. Look for the scan type, index conditions, filters, sort operations, estimated rows and the order in which nodes feed each other. An estimated cost is the planner’s model, not a wall-clock time in milliseconds.

EXPLAIN ANALYZE executes the statement and adds observed timings and row counts. That makes it valuable for practice reads but dangerous to use casually on statements with side effects. Run experiments on disposable data and understand whether the statement writes.

Compare estimated and actual rows when you can safely execute the query. A large mismatch can explain why the planner chose an unsuitable join or scan strategy. Look at rows removed by filters, repeated loops and sorting work instead of only the final total duration.

For the newest-twenty query, ask whether the plan reaches the owner’s range, obtains the requested order efficiently and stops after enough rows. If it scans thousands of rows and sorts them to return twenty, investigate whether the index matches the query and whether statistics reflect the data.

Understand covering, pagination and the write cost

An index that contains all requested data can sometimes support an index-only access path, but actual behavior depends on database-specific visibility and storage rules. In PostgreSQL, visibility checks can still require heap access. Do not promise zero table reads merely because the selected columns appear in an index.

Including large fields such as a destination URL can inflate the index. A smaller key with occasional row fetches may be preferable to duplicating large payloads into every access path. Measure the read benefit against index size and update cost.

Cursor pagination can keep later pages near a key boundary rather than repeatedly discarding a large offset. That benefit depends on a matching order and a useful access path. A cursor encoded in the API is not a database optimization unless the query uses it effectively.

Review the full workload before retaining an index. A write-heavy system pays maintenance cost on every affected change. An infrequently used administrative query may not justify a large additional index if it can run as a controlled offline operation.

Use an evidence-based tuning loop

Start with the exact query and a baseline. Record result correctness, representative latency, rows examined and resource use. Add the candidate index in the practice environment, refresh statistics when appropriate and compare the plan and observations.

Keep the result set constant during the comparison. Changing filters and adding an index at the same time makes it difficult to attribute the improvement. Repeat enough to distinguish cold-cache effects from steady-state behavior, and report the workload and environment rather than claiming a universal speedup.

Check writes too. Measure insertion or update cost and inspect storage growth. A read improvement can be worth that cost, but the decision should acknowledge it. Index creation and removal on live systems also need operational planning, especially for large tables.

An effective design answer names the access pattern, proposes a key order, predicts the work it should reduce and explains how to verify the prediction. This reasoning remains useful even when a database version chooses a different plan from your first expectation.

-- 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