B-tree, Partial and Expression Indexes

A B-tree is the default index, and it is the right one for most columns. It keeps values in sorted order, so it can answer equality, ranges and sorted output. The Intermediate course showed one single-column index. Real queries filter on two columns, read only a few columns, look at a small slice of the table, or compare a calculated value. Each of those has a B-tree form that costs less than the obvious index, and a wrong form that the planner will ignore. This lesson covers the multicolumn, covering, partial and expression forms.

Two tables are built from generate_series with plain arithmetic, so the data is the same on every run: 20,000 orders and 20,000 customers with mixed-case emails.

The tables

CREATE TABLE big_orders AS
SELECT g                                AS order_id,
       (g % 8) + 1                      AS customer_id,
       DATE '2026-01-01' + (g % 365)    AS order_date,
       CASE WHEN g % 100 = 0 THEN 'cancelled'
            WHEN g % 100 < 4 THEN 'new'
            WHEN g % 100 < 12 THEN 'paid'
            ELSE 'shipped' END          AS status,
       ((g % 97) + 0.5)::numeric(8,2)   AS total
FROM generate_series(1, 20000) AS g;
ALTER TABLE big_orders ADD PRIMARY KEY (order_id);
CREATE TABLE big_customers AS
SELECT g AS customer_id,
       'User' || g || '@Example.com' AS email,
       (ARRAY['Manila', 'Cebu', 'Davao', 'Iloilo'])[(g % 4) + 1] AS city
FROM generate_series(1, 20000) AS g;
ALTER TABLE big_customers ADD PRIMARY KEY (customer_id);
ANALYZE big_orders;
ANALYZE big_customers;
SET max_parallel_workers_per_gather = 0;

SELECT 20000
ALTER TABLE
SELECT 20000
ALTER TABLE
ANALYZE
ANALYZE
SET

A multicolumn index: the order of the columns matters

An index on (customer_id, order_date) is sorted by customer first, and by date inside each customer. It serves a query that filters on both columns, and one that filters on the first column alone. It is a different story for the second column alone.

CREATE INDEX big_orders_cust_date_idx ON big_orders (customer_id, order_date);
ANALYZE big_orders;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE customer_id = 3 AND order_date = DATE '2026-03-01';
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE order_date = DATE '2026-03-01';

CREATE INDEX
ANALYZE
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on big_orders (actual rows=7.00 loops=1)
   Recheck Cond: ((customer_id = 3) AND (order_date = '2026-03-01'::date))
   Heap Blocks: exact=7
   ->  Bitmap Index Scan on big_orders_cust_date_idx (actual rows=7.00 loops=1)
         Index Cond: ((customer_id = 3) AND (order_date = '2026-03-01'::date))
         Index Searches: 1
(6 rows)

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Bitmap Heap Scan on big_orders (actual rows=55.00 loops=1)
   Recheck Cond: (order_date = '2026-03-01'::date)
   Heap Blocks: exact=55
   ->  Bitmap Index Scan on big_orders_cust_date_idx (actual rows=55.00 loops=1)
         Index Cond: (order_date = '2026-03-01'::date)
         Index Searches: 9
(6 rows)

The first query is one search in the index: Index Searches: 1. The second filters on the date alone, and it still used the index, with Index Searches: 9. This is a skip scan, new in PostgreSQL 18. The planner searched the index once for each of the 8 customers, and once more to finish. It works because the first column has only 8 different values. With thousands of different first values, there would be thousands of searches, and the planner would not choose it. Swap the order and see:

DROP INDEX big_orders_cust_date_idx;
CREATE INDEX big_orders_date_cust_idx ON big_orders (order_date, customer_id);
ANALYZE big_orders;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM big_orders WHERE customer_id = 3;

DROP INDEX
CREATE INDEX
ANALYZE
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on big_orders (actual rows=2500.00 loops=1)
   Filter: (customer_id = 3)
   Rows Removed by Filter: 17500
(3 rows)

With 365 different dates first, filtering on the customer alone gets a sequential scan. The rule is simple: put the columns you filter with equality first, put the most useful range or sort column last, and do not count on a skip scan unless the leading column has few values. Skip scans are new in PostgreSQL 18, so an older version cannot use the first index for the date-only query in the same way.

A covering index: INCLUDE

A query that needs only a few columns can be answered from the index without visiting the table, which is called an index-only scan. INCLUDE adds columns to the index that are not part of the search key.

DROP INDEX big_orders_date_cust_idx;
CREATE INDEX big_orders_cust_date_total_idx ON big_orders (customer_id, order_date) INCLUDE (total);
VACUUM big_orders;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT order_date, total FROM big_orders
WHERE customer_id = 3 AND order_date >= DATE '2026-12-01';

DROP INDEX
CREATE INDEX
VACUUM
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Index Only Scan using big_orders_cust_date_total_idx on big_orders (actual rows=209.00 loops=1)
   Index Cond: ((customer_id = 3) AND (order_date >= '2026-12-01'::date))
   Heap Fetches: 0
   Index Searches: 1
(4 rows)

Index Only Scan with Heap Fetches: 0 means PostgreSQL never touched the table. The VACUUM matters: an index-only scan can skip a table page only if the visibility map says every row on it is visible to everyone, and VACUUM is what sets that. After heavy updates, Heap Fetches goes up until the next vacuum. Put only small columns in INCLUDE. Every included column makes the index bigger and every write slower.

A partial index

Most orders are shipped, and nobody searches for them. If the queries are about new orders, index only those rows.

CREATE INDEX big_orders_new_idx ON big_orders (order_date) WHERE status = 'new';
CREATE INDEX big_orders_date_all_idx ON big_orders (order_date);
SELECT pg_size_pretty(pg_relation_size('big_orders_new_idx'))      AS partial_size,
       pg_size_pretty(pg_relation_size('big_orders_date_all_idx')) AS full_size;

CREATE INDEX
CREATE INDEX
 partial_size | full_size
--------------+-----------
 32 kB        | 160 kB
(1 row)

The partial index holds 3% of the rows, so it is a fifth of the size here and cheaper to write to, because a paid order never touches it. The planner uses it only when the query's WHERE implies the index's WHERE.

DROP INDEX big_orders_date_all_idx;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT order_id FROM big_orders WHERE status = 'new' AND order_date >= DATE '2026-12-01';

DROP INDEX
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Bitmap Heap Scan on big_orders (actual rows=51.00 loops=1)
   Recheck Cond: ((order_date >= '2026-12-01'::date) AND (status = 'new'::text))
   Heap Blocks: exact=17
   ->  Bitmap Index Scan on big_orders_new_idx (actual rows=51.00 loops=1)
         Index Cond: (order_date >= '2026-12-01'::date)
         Index Searches: 1
(6 rows)

The query repeats status = 'new', so the partial index qualifies. A query on status = 'paid' cannot use it. The planner needs to prove the match from the query text, so a parameter such as status = $1 does not qualify when the plan is built before the value is known.

An expression index

An index on email cannot help a search on lower(email), because the index holds the original text, not the lowercase form.

CREATE INDEX big_customers_email_idx ON big_customers (email);
ANALYZE big_customers;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT customer_id FROM big_customers WHERE lower(email) = 'user777@example.com';
CREATE INDEX big_customers_lower_email_idx ON big_customers (lower(email));
ANALYZE big_customers;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT customer_id FROM big_customers WHERE lower(email) = 'user777@example.com';

CREATE INDEX
ANALYZE
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on big_customers (actual rows=1.00 loops=1)
   Filter: (lower(email) = 'user777@example.com'::text)
   Rows Removed by Filter: 19999
(3 rows)

CREATE INDEX
ANALYZE
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using big_customers_lower_email_idx on big_customers (actual rows=1.00 loops=1)
   Index Cond: (lower(email) = 'user777@example.com'::text)
   Index Searches: 1
(3 rows)

The first plan reads all 20,000 rows to find one. The second index stores lower(email) itself, so the search is direct. The query must use the same expression as the index. lower(email) matches, and email ILIKE 'user777%' does not. The expression must also be immutable, which means the same input always gives the same output. That is why indexing something like now() is refused.

Common mistakes

• Putting the range column first in a multicolumn index.
• Adding a second index when one multicolumn index would do both queries.
• Using INCLUDE for wide columns, or expecting an index-only scan without ever running VACUUM.
• Writing a partial index and then querying with a parameter instead of the condition.
• Indexing email and searching on lower(email).

If you skip this, you will add one index per column and wonder why inserts got slow and queries did not get faster. Which of your indexes starts with a column that your queries never filter on?