Indexes in Depth

Indexes make reads faster by giving the optimizer another route to the same rows. They are not magic, and they are not commands — they are extra sorted structures PostgreSQL may choose when they make the plan cheaper.

This lesson gives you the mental model 16.3 will draw in EXPLAIN: a B-tree index is a sorted copy of selected values plus pointers back to table rows. Good indexes narrow the search; bad or unused indexes mostly add write cost.

B-tree indexes: sorted copy + pointers

The shop_perf dataset has one large table and one index already waiting:

SELECT count(*) AS total_orders,
       count(*) FILTER (WHERE customer_id = 42) AS customer_42,
       count(*) FILTER (
         WHERE customer_id = 42
           AND placed_on >= DATE '2025-06-01'
           AND placed_on < DATE '2025-07-01'
       ) AS customer_42_june,
       count(*) FILTER (WHERE amount >= 90) AS amount_90_plus,
       round(100.0 * count(*) FILTER (WHERE amount >= 90) / count(*), 1) AS amount_90_plus_pct
FROM big_orders;

 total_orders | customer_42 | customer_42_june | amount_90_plus | amount_90_plus_pct
--------------+-------------+------------------+----------------+--------------------
       100000 |         100 |                8 |           1110 |                1.1
(1 row)

big_orders_customer_idx is a B-tree on customer_id. Picture it as a separate sorted list:

customer_id  pointer to table row
-----------  --------------------
1            row location ...
1            row location ...
2            row location ...
...
42           row location ...
42           row location ...

The index does not contain the whole table. It contains the indexed value, ordered for fast search, and enough pointer information for PostgreSQL to fetch the real row when needed.

EXPLAIN (COSTS OFF)
SELECT id, customer_id, amount
FROM big_orders
WHERE customer_id = 42;

                     QUERY PLAN
----------------------------------------------------
 Bitmap Heap Scan on big_orders
   Recheck Cond: (customer_id = 42)
   ->  Bitmap Index Scan on big_orders_customer_idx
         Index Cond: (customer_id = 42)
(4 rows)

The exact node names belong to 16.3. For today, read the story: PostgreSQL can use the sorted copy to find the 100 matching customer entries, then follow pointers back to table rows.

Composite indexes: column order decides

A composite index sorts by the first column, then by the second column inside each first-column group, then by the third, and so on. This one is sorted by customer_id first, then placed_on within each customer:

CREATE INDEX big_orders_customer_date_idx
ON big_orders (customer_id, placed_on);
 
EXPLAIN (COSTS OFF)
SELECT id, customer_id, placed_on, amount
FROM big_orders
WHERE customer_id = 42
  AND placed_on >= DATE '2025-06-01'
  AND placed_on < DATE '2025-07-01';
 
EXPLAIN (COSTS OFF)
SELECT id, customer_id, placed_on, amount
FROM big_orders
WHERE placed_on >= DATE '2025-06-01'
  AND placed_on < DATE '2025-07-01';

CREATE INDEX
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on big_orders
   Recheck Cond: ((customer_id = 42) AND (placed_on >= '2025-06-01'::date) AND (placed_on < '2025-07-01'::date))
   ->  Bitmap Index Scan on big_orders_customer_date_idx
         Index Cond: ((customer_id = 42) AND (placed_on >= '2025-06-01'::date) AND (placed_on < '2025-07-01'::date))
(4 rows)
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on big_orders
   Filter: ((placed_on >= '2025-06-01'::date) AND (placed_on < '2025-07-01'::date))
(2 rows)

Same index, two different questions. With customer_id = 42, PostgreSQL can jump to one customer's section and then scan the June date range inside it. With date alone, the phone book is sorted the wrong way: June orders are scattered across every customer section, so this index is not the right tool.

This is why composite index design starts from real WHERE and ORDER BY patterns, not from a desire to index every column.

Covering indexes and index-only scans

Sometimes the index can answer the query without visiting the table at all. A covering index includes every column the query needs. In PostgreSQL, the extra non-key columns go in INCLUDE:

CREATE INDEX big_orders_customer_cover_idx
ON big_orders (customer_id) INCLUDE (placed_on, amount);
VACUUM big_orders;
ANALYZE big_orders;
 
EXPLAIN (COSTS OFF)
SELECT customer_id, placed_on, amount
FROM big_orders
WHERE customer_id = 42;

CREATE INDEX
VACUUM
ANALYZE
                            QUERY PLAN
-------------------------------------------------------------------
 Index Only Scan using big_orders_customer_cover_idx on big_orders
   Index Cond: (customer_id = 42)
(2 rows)

That Index Only Scan is the payoff: the search column and selected columns are all in the index. The VACUUM line makes the browser lab's visibility information clean enough to show this shape; in real databases, index-only scans also depend on whether PostgreSQL can prove the table pages are visible without checking them.

Covering indexes are tempting, but every included column makes the index larger. Use them when a repeated read path genuinely benefits, not as a default.

Partial indexes: index the slice you actually ask for

A partial index stores only rows that satisfy a predicate. In this seed, amount >= 90 covers 1,110 orders — 1.1% of the table — so a partial index can be tiny compared with a full-table index:

CREATE INDEX big_orders_high_amount_customer_idx
ON big_orders (customer_id)
WHERE amount >= 90;

That index can help a query whose WHERE clause proves the same condition:

WHERE customer_id = 42
  AND amount >= 90

It cannot help a plain WHERE customer_id = 42 query, because the partial index does not contain ordinary customer-42 rows unless they also satisfy amount >= 90. The predicate is part of the promise. Try both in the playground below and compare the plan names.

Expression indexes: match the expression you search

Here is the classic index trap. You index email, then the app searches lower(email) for case-insensitive matching:

CREATE TABLE customer_contacts AS
SELECT i AS id,
       'customer' || i || '@example.com' AS email
FROM generate_series(1, 10000) AS i;
 
CREATE INDEX customer_contacts_email_idx
ON customer_contacts (email);
ANALYZE customer_contacts;
 
EXPLAIN (COSTS OFF)
SELECT id, email
FROM customer_contacts
WHERE lower(email) = 'customer42@example.com';
 
CREATE INDEX customer_contacts_lower_email_idx
ON customer_contacts (lower(email));
ANALYZE customer_contacts;
 
EXPLAIN (COSTS OFF)
SELECT id, email
FROM customer_contacts
WHERE lower(email) = 'customer42@example.com';

CREATE TABLE
CREATE INDEX
ANALYZE
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on customer_contacts
   Filter: (lower(email) = 'customer42@example.com'::text)
(2 rows)
CREATE INDEX
ANALYZE
                               QUERY PLAN
-------------------------------------------------------------------------
 Index Scan using customer_contacts_lower_email_idx on customer_contacts
   Index Cond: (lower(email) = 'customer42@example.com'::text)
(2 rows)

Indexes are not free

Every index is another structure PostgreSQL must maintain. INSERT adds an entry to every relevant index. UPDATE may delete and reinsert index entries when indexed values change. DELETE must mark index entries dead. Large indexes also consume memory and storage, and they give the planner more options to consider.

That cost is often worth it. It is not automatically worth it. Keep indexes that support real read paths, constraints, or ordering needs; remove unused indexes after measuring in the real system. 16.4 will turn this into tuning patterns: functions on indexed columns, leading-wildcard searches, deep OFFSET, and SELECT * I/O.

One boundary paragraph before you go: B-tree is the everyday default for equality, ranges, and ordering. GIN is the usual family for inverted lookups such as full-text search and JSON/array containment. GiST supports extensible shapes such as geometric and range-style searches. BRIN summarizes physical page ranges and can shine on huge naturally ordered tables. Specialist index families are not better B-trees; they solve different search problems.

Next, EXPLAIN becomes your instrument panel: the optimizer's choices made visible as a plan tree.