Indexes Explained With Query Plans: Which Columns to Index

With four appointments, every query is instant and no index matters. This part generates 200,000 of them for 20,000 patients, runs the three queries a clinic runs all day, and reads the plans before and after each index. The plans are the lesson: they say whether the engine read the whole table or went straight to the rows, and they count the pages it touched, which is a number that does not depend on how fast your laptop is.

The data and the three queries

Section 2 generates 100 appointments a day, five minutes apart, over 2,000 days, so that "today", fixed at 25 September 2026, has exactly 100 rows, and every patient has exactly ten visits. Nine in ten appointments are Done; twelve are CheckedIn today. The queries are the front desk's: Q1, today's appointments; Q2, one patient's history; Q3, the live queue, checked-in today in time order. A fourth, the no-shows since 1 January, joins later. In PostgreSQL each is wrapped in EXPLAIN (ANALYZE, BUFFERS); the SQL Server script uses SET SHOWPLAN_TEXT ON for the plan and SET STATISTICS IO, TIME ON for the page counts. Section 4 runs them with nothing but the primary key:

EXPLAIN (ANALYZE, BUFFERS) SELECT id, patient_id, starts_at, status FROM appointments
WHERE starts_at >= '2026-09-25' AND starts_at < '2026-09-26' ORDER BY starts_at;

 Gather Merge  (cost=4449.34..4460.17 rows=95 width=29) (actual time=4.117..5.279 rows=100.00 loops=1)
   ...
         ->  Parallel Seq Scan on appointments  (cost=0.00..3447.71 rows=56 width=29) (actual time=3.161..3.177 rows=50.00 loops=2)
               Filter: ((starts_at >= '2026-09-25 00:00:00+00'::timestamp with time zone) AND (starts_at < '2026-09-26 00:00:00+00'::timestamp with time zone))
               Rows Removed by Filter: 99950
               Buffers: shared hit=1683
 Execution Time: 5.289 ms

A sequential scan: two workers each read half the table, threw away 99,950 rows, and touched 1,683 pages of 8 KB to return 100 rows. SQL Server's plan says Clustered Index Scan(OBJECT:(...[pk_appointments]), WHERE:(...)) under a Sort, and its statistics say logical reads 1291; its table is its clustered index, so the scan is the whole table there too. All three queries look like this. Five milliseconds on a warm laptop is not slow. It is the shape that matters: the cost is the size of the table, and the table grows.

One index per filter column

Section 5 creates an index on the date and one on the foreign key:

CREATE INDEX ix_appointments_starts_at ON appointments (starts_at);
CREATE INDEX ix_appointments_patient_id ON appointments (patient_id);  -- the FK column (Part 3)

 Index Scan using ix_appointments_starts_at on appointments  (cost=0.42..10.34 rows=96 width=29) (actual time=0.010..0.017 rows=100.00 loops=1)
   Index Cond: ((starts_at >= '2026-09-25 00:00:00+00'::timestamp with time zone) AND (starts_at < '2026-09-26 00:00:00+00'::timestamp with time zone))
   Index Searches: 1
   Buffers: shared hit=4 read=1
 Execution Time: 0.028 ms

From 1,683 pages to 5, and the Sort is gone, because the index already holds the rows in date order. SQL Server goes from 1,291 reads to 318: an Index Seek on the new index finds the 100 entries, and a Clustered Index Seek ... LOOKUP fetches each row's other columns from the table, about three pages a row. That lookup is the cost of an index that does not contain everything the query asks for; section 7 removes it. The patient's history, Q2, goes from 1,291 to 33 reads on SQL Server and from 1,721 to 12 buffers on PostgreSQL, where the planner chose a Bitmap Heap Scan to fetch ten scattered rows in page order. This is why Part 3 insisted on indexing foreign key columns.

Column order in a composite index

The queue query filters on status and on a date range, and a two-column index can be built in either order. Section 6 builds (starts_at, status), then (status, starts_at), and runs Q3 and Q4 under each:

For today's queue the order does not matter: one day is a hundred entries either way. For the year's no-shows it matters sixteen times over. With the date first, the engine seeks to 1 January and then walks 29,700 entries checking the status of each; SQL Server's plan shows it, SEEK:([starts_at] > ...), WHERE:([status]=N'NoShow'), the status a residual filter rather than a seek key. With the status first it seeks to the 792 no-show entries after 1 January and reads nothing else. The rule: equality columns first, then the range column, so the range is a contiguous run inside the index.

A covering index

The queue query still needs patient_id, which the composite index does not hold, so every match costs a lookup into the table. Section 7 adds the column to the index without making it part of the key:

CREATE INDEX ix_appointments_status_starts_at ON appointments (status, starts_at) INCLUDE (patient_id);

 Index Only Scan using ix_appointments_status_starts_at on appointments  (cost=0.42..4.44 rows=1 width=16) (actual time=0.013..0.014 rows=12.00 loops=1)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=4
 Execution Time: 0.019 ms

Index Only Scan with Heap Fetches: 0 means the table was never touched. SQL Server's plan loses its Clustered Index Seek ... LOOKUP line and its reads fall from 39 to 3. Both engines spell it INCLUDE. The included column costs space in the index and nothing in seek time, which is why it is not a key column.

The index the planner ignores

Section 8 counts the Done appointments, with the status index in place, and both engines scan the table anyway: Clustered Index Scan, 1,291 reads; Parallel Seq Scan, 1,683 buffers, Rows Removed by Filter: 10249. The same query for InProgress uses the index and touches 4 pages. The planner is right both times. When nine rows in ten match, following the index to each of them costs more than reading the table once; an index earns its keep on selective conditions, and a column with three or four values that split the table evenly is rarely worth indexing on its own.

The leftmost column, and PostgreSQL 18

Section 9 drops the single date index and runs Q1 again, with only (status, starts_at) available. The textbook says an index can be used only from its first column, and SQL Server follows the book: Index Scan(OBJECT:(...[ix_appointments_status_starts_at]), WHERE:(...)), 1,042 logical reads, the whole index, chosen over the table only because it is narrower. PostgreSQL 18 does something new:

   ->  Index Scan using ix_appointments_status_starts_at on appointments  (cost=0.42..286.65 rows=96 width=29) (actual time=0.015..0.046 rows=100.00 loops=1)
         Index Searches: 5
         Buffers: shared hit=16 read=6

Five searches, one per status value: it jumps to each status and seeks the date range inside it, a skip scan, and reads 22 pages instead of a thousand. The rule is still worth knowing, because it holds on SQL Server 2022 and on every PostgreSQL before 18, and because a skip scan only pays off when the leading column has a handful of values. Design the column order for the queries you have, and treat the skip scan as a safety net.

Frequently asked

Which columns should I index?

The columns your queries filter and join on, when the condition is selective: foreign keys, dates you range over, codes you look up. Read the plan before and after: a scan that becomes a seek, and a page count that drops by orders of magnitude, is an index worth having. A status with three values is usually not.

Does the column order in a composite index matter?

Yes. Put the columns compared with equals first and the range column last, so the rows you want are one contiguous run in the index. On 200,000 rows the year's no-shows read 129 pages with the date first and 8 with the status first.

What is a covering index?

An index that holds every column a query needs, so the engine never visits the table. Add the extra columns with INCLUDE on both SQL Server and PostgreSQL. The plan shows it as no Key Lookup in SQL Server and as Index Only Scan with Heap Fetches: 0 in PostgreSQL.

Next: Part 10, soft delete, audit columns and history, where deleting a patient stops meaning DELETE.