SQL · Guides · October 30, 2026 · 13 min read

SQL indexes explained for analysts: B-tree, EXPLAIN, when they help

An index is a separate, sorted structure that lets the database find rows by a column’s value without reading the whole table, like the index at the back of a book. Without an index on book_ref, finding the tickets of one booking means checking all 366,733 rows of tickets; with one, the database walks down a small sorted tree and reads a handful of pages. Indexes speed up queries that pick a small share of rows (WHERE on a selective column, the lookup side of a JOIN, ORDER BY ... LIMIT), cost extra disk space and slow down every insert and update, and do little for the large scans and aggregations that make up most analytics. Columnar engines such as DuckDB, BigQuery and Snowflake take a different approach and rely mainly on min/max statistics per block (“zone maps”) instead of B-tree indexes.

The examples use the airline database from the Nomto SQL course. The Nomto SQL room is read-only and runs SELECT queries only, so CREATE INDEX and EXPLAIN are not available there. To show real plans, the same tables were loaded into PostgreSQL 14 on a laptop; every plan below is the real output, trimmed to the relevant lines, with timings that will differ on your machine. The DuckDB plan at the end comes from the course data itself.

What an index is

Take the tickets table. A booking has 1.4 tickets on average:

SELECT COUNT(*) AS tickets,
       COUNT(DISTINCT book_ref) AS bookings,
       ROUND(COUNT(*) * 1.0 / COUNT(DISTINCT book_ref), 2) AS tickets_per_booking
FROM tickets;

The rows are stored in the order they were loaded, not by book_ref. To answer WHERE book_ref = '06B046', PostgreSQL without an index has no choice but to read every row:

EXPLAIN ANALYZE
SELECT * FROM tickets WHERE book_ref = '06B046';

Seq Scan on tickets  (cost=0.00..10728.16 rows=2 width=104) (actual time=0.004..24.796 rows=2 loops=1)
  Filter: ((book_ref)::text = '06B046'::text)
  Rows Removed by Filter: 366731
Execution Time: 24.805 ms

Seq Scan (sequential scan) means a full read of the table: 366,731 rows were read and thrown away to find 2. Now create an index:

CREATE INDEX tickets_book_ref_idx ON tickets (book_ref);

The same query:

Index Scan using tickets_book_ref_idx on tickets  (cost=0.42..12.46 rows=2 width=104) (actual time=0.014..0.014 rows=2 loops=1)
  Index Cond: ((book_ref)::text = '06B046'::text)
Execution Time: 0.023 ms

From about 25 ms to about 0.02 ms, roughly a thousand times faster, because the database now reads a few pages instead of the whole 48 MB table. The index itself takes 7.5 MB.

How a B-tree index works

The default index type in PostgreSQL, MySQL (InnoDB), SQL Server and Oracle is the B-tree: a balanced tree of pages. The leaf pages hold every book_ref value in sorted order, each with a pointer to its row in the table. Above them, a few levels of pages hold “signposts”: values that tell the database which page to go down to next, the way the letter tabs in a dictionary send you to the right section.

For the 366,733 tickets, the book_ref index has three levels: a root page, one level of internal pages, and the leaves. A lookup reads one page per level and then the table page with the row, about four page reads instead of thousands. Because the tree stays balanced as rows are added, the depth grows very slowly: each extra level multiplies the capacity by a few hundred.

Since the leaves are sorted, a B-tree helps with:

• equality: WHERE book_ref = '06B046';
• ranges: WHERE book_date >= '2026-08-01' AND book_date < '2026-08-02', BETWEEN, <, >;
• prefix search: LIKE 'PG05%' (in PostgreSQL, only with a C collation or a text_pattern_ops index);
• sorting: ORDER BY book_date DESC LIMIT 5 reads the first leaves and stops;
• MIN and MAX of the indexed column.

It does not help with LIKE '%05' (no fixed prefix), or with an expression on the column, as shown below.

Primary keys create indexes

A primary key is a column, or set of columns, whose value uniquely identifies a row; SQL joins explained shows how keys link tables. Declaring one makes the database enforce uniqueness, and to do that quickly, it builds a unique B-tree index automatically:

ALTER TABLE flights ADD PRIMARY KEY (flight_id);

Indexes:
    "flights_pkey" PRIMARY KEY, btree (flight_id)

A lookup by flight_id now uses it:

Index Scan using flights_pkey on flights  (cost=0.29..8.31 rows=1 width=63) (actual time=0.511..0.512 rows=1 loops=1)
  Index Cond: (flight_id = 1001)

UNIQUE constraints create indexes the same way. Foreign keys do not in PostgreSQL: ticket_flights.flight_id refers to flights, but nothing indexes it unless you do. MySQL’s InnoDB is the exception and creates an index for a foreign key if none exists.

When an index helps

Selective WHERE filters

An index pays off when the filter keeps a small share of the rows. A day of bookings is about 2% of the table:

Bitmap Heap Scan on bookings  (cost=103.28..1869.26 rows=6132 width=20) (actual time=0.381..1.451 rows=5746 loops=1)
  Recheck Cond: ((book_date >= '2026-08-01 00:00:00'::timestamp without time zone) AND (book_date < '2026-08-02 00:00:00'::timestamp without time zone))
  Heap Blocks: exact=1623
  ->  Bitmap Index Scan on bookings_book_date_idx  (cost=0.00..101.74 rows=6132 width=0) (actual time=0.267..0.267 rows=5746 loops=1)
Execution Time: 1.578 ms

That is the plan for WHERE book_date >= '2026-08-01' AND book_date < '2026-08-02' with an index on book_date. A bitmap scan is the middle ground between an index scan and a full scan: PostgreSQL first collects all matching row locations from the index (Bitmap Index Scan), then reads those table pages in physical order (Bitmap Heap Scan). It is typical when the filter matches thousands of rows.

JOINs

When you join a few rows to a big table, an index on the big table’s join column changes the plan completely. Tickets on one flight, without an index on ticket_flights.flight_id:

SELECT f.flight_no, tf.fare_conditions, tf.amount
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
WHERE f.flight_no = 'PG0405' AND f.scheduled_departure::date = '2026-08-01';

Hash Join  (cost=971.63..22889.28 rows=32 width=20) (actual time=4.489..117.512 rows=97 loops=1)
  Hash Cond: (tf.flight_id = f.flight_id)
  ->  Seq Scan on ticket_flights tf  (actual time=0.009..68.813 rows=1045726 loops=1)
  ->  Hash  (actual time=2.591..2.592 rows=1 loops=1)
        ->  Seq Scan on flights f  (actual time=0.004..2.586 rows=1 loops=1)
Execution Time: 117.545 ms

All 1,045,726 rows of ticket_flights are read to find 97. After CREATE INDEX ON ticket_flights (flight_id):

Nested Loop  (cost=4.96..1235.71 rows=32 width=20) (actual time=0.018..1.992 rows=97 loops=1)
  ->  Seq Scan on flights f  (actual time=0.004..1.942 rows=1 loops=1)
  ->  Bitmap Heap Scan on ticket_flights tf  (actual time=0.012..0.042 rows=97 loops=1)
        ->  Bitmap Index Scan on ticket_flights_flight_id_idx  (actual time=0.005..0.006 rows=97 loops=1)
              Index Cond: (flight_id = f.flight_id)
Execution Time: 2.013 ms

The plan switches to a nested loop: find the one flight, then look up its tickets in the index. From 117 ms to 2 ms. When a join covers most of both tables, as in a monthly revenue report, a hash join with full scans is the faster plan, and the index is not used.

ORDER BY with LIMIT

“The five latest bookings” with an index on book_date:

Limit  (cost=0.42..0.66 rows=5 width=20) (actual time=0.011..0.013 rows=5 loops=1)
  ->  Index Scan Backward using bookings_book_date_idx on bookings  (actual time=0.010..0.012 rows=5 loops=1)
Execution Time: 0.016 ms

The index is already sorted, so the database reads it from the end (Index Scan Backward) and stops after five rows, without sorting 262,788 bookings.

When an index does not help

The filter matches most of the table

ticket_flights.fare_conditions has three values, and Economy is 88% of the rows:

SELECT fare_conditions, COUNT(*) AS rows,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM ticket_flights
GROUP BY fare_conditions
ORDER BY rows DESC;

With an index on fare_conditions, PostgreSQL still ignores it for Economy and uses it for Comfort:

-- WHERE fare_conditions = 'Economy'
Seq Scan on ticket_flights  (cost=0.00..21786.58 rows=917799 width=31)
  Filter: ((fare_conditions)::text = 'Economy'::text)

-- WHERE fare_conditions = 'Comfort'
Bitmap Heap Scan on ticket_flights  (cost=205.17..9147.19 rows=18161 width=31)
  ->  Bitmap Index Scan on ticket_flights_fare_idx  (cost=0.00..200.63 rows=18161 width=0)

Jumping from the index to the table row by row is more expensive per row than reading the table straight through. When a filter keeps a large share of rows, a full scan wins, and the planner, using its statistics, picks it. This is why most analytical queries (“revenue by month for the whole year”) barely benefit from indexes at all.

A function or cast on the indexed column

The index on book_date stores timestamps. A filter on CAST(book_date AS date) asks about a different value, which the index does not contain:

SELECT * FROM bookings WHERE CAST(book_date AS date) = '2026-08-01';

Seq Scan on bookings  (cost=0.00..5615.82 rows=1314 width=20) (actual time=0.012..16.072 rows=5746 loops=1)
  Filter: ((book_date)::date = '2026-08-01'::date)
  Rows Removed by Filter: 257042
Execution Time: 16.209 ms

The same rows with a half-open range on the raw column took 1.6 ms with a bitmap index scan (see above). The same applies to date_trunc('month', book_date) = ..., LOWER(email) = ..., amount * 1.2 > ...: keep the column bare on one side of the comparison, or create an index on the expression itself (CREATE INDEX ON bookings ((book_date::date)) in PostgreSQL). SQL date functions shows the range pattern for days and months.

Small tables and type mismatches

On a table of a few hundred rows, such as airports, a full scan is a single page or two and an index adds nothing. And a comparison between different types can force a cast on the column side: comparing a VARCHAR code with a number, for example, may make the database convert the column on every row, which skips the index (or fails outright in PostgreSQL). See SQL data types.

Composite indexes: column order matters

An index can cover several columns. For “flights from SVO on 1 August”:

CREATE INDEX flights_dep_time_idx ON flights (departure_airport, scheduled_departure);

The entries are sorted by airport first, and by departure time within each airport, like a phone book sorted by last name, then first name. That makes three filters behave differently:

-- WHERE departure_airport = 'SVO' AND scheduled_departure >= '2026-08-01' AND scheduled_departure < '2026-08-02'
Bitmap Index Scan on flights_dep_time_idx  (actual time=0.019..0.019 rows=50 loops=1)
Execution Time: 0.064 ms

-- WHERE departure_airport = 'SVO'
Bitmap Index Scan on flights_dep_time_idx  (actual time=0.165..0.165 rows=2981 loops=1)
Execution Time: 0.403 ms

-- WHERE scheduled_departure >= '2026-08-01' AND scheduled_departure < '2026-08-02'
Seq Scan on flights  (actual time=0.006..1.788 rows=568 loops=1)
  Rows Removed by Filter: 32553
Execution Time: 1.809 ms

The index works for both columns together and for the first column alone, but not for the second column alone: the departures of 1 August are scattered across every airport’s section, just as a phone book cannot find everyone named “Anna”. This is the leftmost prefix rule. The usual guidance for ordering columns:

1. Columns compared with = first, the range column (>=, <, BETWEEN) last.
2. Among equality columns, put first the one your queries filter on most often.

Newer engines can sometimes “skip scan” a composite index when the leading column has few distinct values (MySQL 8.0.13 and later, Oracle, PostgreSQL 18), but do not design around it.

CREATE INDEX syntax

-- One column
CREATE INDEX tickets_book_ref_idx ON tickets (book_ref);

-- Several columns, order matters
CREATE INDEX flights_dep_time_idx ON flights (departure_airport, scheduled_departure);

-- Unique: also rejects duplicates
CREATE UNIQUE INDEX bookings_book_ref_key ON bookings (book_ref);

-- PostgreSQL: build without blocking writes on a live table
CREATE INDEX CONCURRENTLY bookings_book_date_idx ON bookings (book_date);

-- Remove
DROP INDEX tickets_book_ref_idx;

The syntax is nearly identical in PostgreSQL, MySQL, SQL Server and DuckDB. Differences: CONCURRENTLY is PostgreSQL-only (SQL Server Enterprise has WITH (ONLINE = ON)), and DROP INDEX in MySQL and SQL Server needs the table name (DROP INDEX tickets_book_ref_idx ON tickets). As an analyst, you will usually suggest an index to the team that owns the database rather than create it on production yourself: every index takes disk space and has to be updated on every insert, update and delete.

How to read EXPLAIN

EXPLAIN shows the plan the database intends to run; EXPLAIN ANALYZE runs the query and adds what actually happened. Read the plan from the most indented line outwards: inner nodes produce rows for the nodes above them. What to look for:

MySQL has EXPLAIN and, from 8.0.18, EXPLAIN ANALYZE; SQL Server shows plans with SET SHOWPLAN_TEXT ON or graphically in Management Studio.

Columnar engines: zone maps instead of B-trees

DuckDB (the engine of the Nomto SQL room), BigQuery, Snowflake, ClickHouse and Redshift store data by column, in large blocks, and are built for scans and aggregations. Instead of B-trees, they keep min/max statistics for every column in every block and skip blocks whose range cannot match the filter. DuckDB calls these zone maps and keeps them for each row group of 122,880 rows; Parquet files store the same statistics per row group. Here is the EXPLAIN of a one-day filter in DuckDB on the course bookings table:

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│        count_star()       │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│          SEQ_SCAN         │
│   Type: Sequential Scan   │
│          Filters:         │
│ book_date>='2026-08-01 00 │
│   :00:00'::TIMESTAMP AND  │
│  book_date<'2026-08-02 00 │
│     :00:00'::TIMESTAMP    │
│        ~52,557 rows       │
└───────────────────────────┘

(Shortened.) It is a sequential scan, but with the filter pushed into the scan: row groups whose min/max range excludes 1 August can be skipped without reading them. Whether anything is actually skipped depends on how the data is sorted. The Parquet file behind bookings is stored in book_ref order, and its statistics show it:

SELECT row_group_id, row_group_num_rows, stats_min, stats_max
FROM parquet_metadata('bookings.parquet')
WHERE path_in_schema = 'book_date';

Every row group spans almost the whole date range, so a date filter cannot skip any of them. Sorted by book_date, each group would cover a few days and a one-day filter would read one group. That is why in columnar warehouses the equivalent of “add an index” is sort or cluster the table by the columns you filter on: partitioning and clustering in BigQuery, clustering keys in Snowflake, the sort order (ORDER BY) of a ClickHouse table. DuckDB also supports CREATE INDEX (an ART index), but it is meant for primary keys, uniqueness and very selective point lookups, not for speeding up analytical filters.

FAQ

What is an index in SQL?

A separate, sorted data structure, usually a B-tree, that maps a column’s values to the rows that contain them. It lets the database find rows by value without reading the whole table, at the cost of extra storage and slower writes.

When should I create an index?

When frequent queries filter, join or sort on a column and pick a small share of the rows: lookups by id, joins from a few rows into a large table, “latest N” queries. Columns with few distinct values, filters that keep most rows and small tables rarely benefit.

Does a primary key create an index?

Yes. Primary keys and unique constraints are enforced with a unique index that the database creates automatically. Foreign keys are not indexed automatically in PostgreSQL or SQL Server; MySQL’s InnoDB adds one if none exists.

Why is my query not using the index?

The most common reasons: the filter matches a large share of the table, so a full scan is cheaper; the column is wrapped in a function or cast (CAST(book_date AS date) = ...); a composite index is used without its first column; or the table statistics are outdated. EXPLAIN shows which plan was chosen.

Do BigQuery and DuckDB use indexes?

Not in the B-tree sense for ordinary filters. They store data in columns and skip blocks using min/max statistics, so the order of the data matters more than indexes. BigQuery uses partitioning and clustering for this; DuckDB uses zone maps automatically and has CREATE INDEX mainly for keys and point lookups.