SQL · Advanced

SQL composite indexes and covering indexes

24 min read

Why it matters

A SQL composite index covers several columns in one sorted structure, and getting its column order right is the difference between reading 6 pages and reading 1,100. Most slow queries at work filter on two or three columns at once (a customer and a date, a seller and a status), sort the result, and read a few more columns. One well-designed composite or covering index answers all of that from the index alone; three single-column indexes rarely do. SQL appears in 88% of Data Analyst and 80% of Data Engineer job descriptions we studied, and "which of these queries can use an index on (a, b)?" is a favourite backend and database interview question.

This lesson builds on SQL EXPLAIN: read query plans and use indexes. On the same 541,909 sales lines you will compare column orders, apply the leftmost prefix rule, build a covering index that never touches the table, shrink an index to 3% of its size with a partial index, rescue a non-sargable filter with an expression index, and measure what all these indexes cost on writes.

The concept

A composite index is sorted by its first column, then the next

An index on (stock_code, invoiced_at) is like a phone book sorted by surname, then first name. All entries for product 85123A sit together, and inside that group they are sorted by date. So the database can:

find one product (the surname) directly,
find one product in a date range (surname, then a range of first names) directly,
return one product's rows already sorted by date, with no sort step.

It cannot jump to a date range across all products, just as a phone book cannot find everyone called "Priya" without reading the whole book.

Column order: equality first, then range

Put the columns compared with = (or IN) first and the column compared with a range (>, <, BETWEEN) or used in ORDER BY last. With (stock_code, invoiced_at), the index jumps to 85123A and reads only its Q4 entries. With (invoiced_at, stock_code), it has to read every entry in Q4 (171,000 of them) and check the product on each.

The leftmost prefix rule

An index on (a, b, c) can be used for filters on:

Filter	Uses the index?
`a = 1`	Yes
`a = 1 AND b = 2`	Yes
`a = 1 AND b = 2 AND c > 3`	Yes, all three columns
`a = 1 AND c = 3`	Yes for `a`; `c` is checked on each entry with that `a`
`a = 1 AND b > 2 AND c = 3`	Yes for `a` and `b`; after a range, later columns are only filters
`b = 2` or `c = 3` alone	No (PostgreSQL 18 and Oracle can sometimes "skip scan" when `a` has few distinct values)
`ORDER BY a, b`	Yes, already sorted

A consequence: an index on (a) is redundant once you have (a, b). Drop it.

Covering indexes and INCLUDE

A plain index scan finds rows in the index, then visits the table for every other column the query reads. A covering index contains every column the query needs, so the database answers from the index alone (PostgreSQL: Index Only Scan). Columns needed only for output, not for searching, go in INCLUDE:

sql

CREATE INDEX ON sales (stock_code, invoiced_at) INCLUDE (quantity);

INCLUDE exists in PostgreSQL 11+ and SQL Server. In MySQL, add the column to the end of the key instead.

In PostgreSQL an index-only scan still checks the table for rows changed since the last VACUUM, because visibility lives in the table, not the index. The visibility map marks pages where every row is visible to everyone; on those pages the table visit is skipped. Heap Fetches in the plan counts the visits that were not skipped.

Partial indexes

A partial (in SQL Server, filtered) index stores only rows matching a WHERE. When queries always target a small hot subset, such as open orders, unpaid invoices or cancellations, the index is a fraction of the size and stays in memory. The query's WHERE must imply the index's WHERE for the planner to use it. PostgreSQL, SQLite and SQL Server support them; MySQL does not.

Expression indexes

If queries filter on an expression, index the expression: CREATE INDEX ON users (lower(email)), or ON sales ((invoiced_at::date)). The query must use the same expression. MySQL 8.0.13+ calls these functional key parts; SQL Server indexes a computed column instead.

What every index costs

Each index is updated on every insert, on every delete, and on updates of its columns. Each takes disk and memory, and each must be vacuumed and backed up. Indexes that are never used are pure cost. In PostgreSQL, pg_stat_user_indexes.idx_scan counts how often each index was used since statistics were last reset.

Clustered indexes vs heap tables

In SQL Server (a table's clustered index) and MySQL InnoDB (always the primary key), the table is a B-tree sorted by that key, so a range on the clustered key reads neighbouring rows. Their secondary indexes point to the primary key, which makes a short primary key important in InnoDB. PostgreSQL stores tables as an unordered heap, and every index points to a physical row location. CLUSTER re-sorts a PostgreSQL table once, but new rows do not keep the order.

Worked example: indexing the sales table

PostgreSQL 16 on the UCI Online Retail dataset (CC BY 4.0). Every output is from a real run; your times will differ.

Set up the data

Download online-retail-dataset.csv (link in Further reading), start psql in the same folder, and run:

sql

CREATE TABLE raw_retail (
  invoice_no   text,
  stock_code   text,
  description  text,
  quantity     int,
  invoice_date text,
  unit_price   numeric(10,3),
  customer_id  int,
  country      text
);
\copy raw_retail FROM 'online-retail-dataset.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE sales AS
SELECT invoice_no, stock_code, description, quantity, unit_price, customer_id, country,
       to_timestamp(invoice_date, 'MM/DD/YYYY HH24:MI')::timestamp AS invoiced_at
FROM raw_retail;
VACUUM ANALYZE sales;

Output

CREATE TABLE
COPY 541909
SELECT 541909
VACUUM

Step 1: two single-column indexes

The query from the end of the EXPLAIN lesson: one product's sales in the last quarter of 2011.

sql

-- two single-column indexes, as at the end of the EXPLAIN lesson
CREATE INDEX sales_stock_code_idx  ON sales (stock_code);
CREATE INDEX sales_invoiced_at_idx ON sales (invoiced_at);

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*), sum(quantity) FROM sales
WHERE stock_code = '85123A'
  AND invoiced_at >= '2011-10-01' AND invoiced_at < '2012-01-01';

Output

CREATE INDEX
CREATE INDEX
                                                                                              QUERY PLAN                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=4.895..4.898 rows=1 loops=1)
   Buffers: shared hit=421 read=194
   ->  Bitmap Heap Scan on sales (actual time=4.072..4.838 rows=500 loops=1)
         Recheck Cond: ((stock_code = '85123A'::text) AND (invoiced_at >= '2011-10-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2012-01-01 00:00:00'::timestamp without time zone))
         Heap Blocks: exact=449
         Buffers: shared hit=421 read=194
         ->  BitmapAnd (actual time=3.999..4.001 rows=0 loops=1)
               Buffers: shared hit=3 read=163
               ->  Bitmap Index Scan on sales_stock_code_idx (actual time=0.234..0.235 rows=2313 loops=1)
                     Index Cond: (stock_code = '85123A'::text)
                     Buffers: shared read=5
               ->  Bitmap Index Scan on sales_invoiced_at_idx (actual time=3.653..3.653 rows=170978 loops=1)
                     Index Cond: ((invoiced_at >= '2011-10-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2012-01-01 00:00:00'::timestamp without time zone))
                     Buffers: shared hit=3 read=158
 Planning:
   Buffers: shared hit=72 read=5
 Planning Time: 0.452 ms
 Execution Time: 4.951 ms
(18 rows)

PostgreSQL combined both indexes with BitmapAnd: 2,313 rows for the product, 170,978 for the quarter, intersected down to 500. Building the 171,000-entry bitmap is where the time went, and the whole plan read 615 pages.

Step 2: the same two columns, in both orders

sql

-- range column first
CREATE INDEX sales_date_code_idx ON sales (invoiced_at, stock_code);

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*), sum(quantity) FROM sales
WHERE stock_code = '85123A'
  AND invoiced_at >= '2011-10-01' AND invoiced_at < '2012-01-01';

DROP INDEX sales_date_code_idx;

-- equality column first
CREATE INDEX sales_code_date_idx ON sales (stock_code, invoiced_at);

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*), sum(quantity) FROM sales
WHERE stock_code = '85123A'
  AND invoiced_at >= '2011-10-01' AND invoiced_at < '2012-01-01';

Output

CREATE INDEX
                                                                                              QUERY PLAN                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=4.295..4.298 rows=1 loops=1)
   Buffers: shared hit=615
   ->  Bitmap Heap Scan on sales (actual time=3.522..4.244 rows=500 loops=1)
         Recheck Cond: ((stock_code = '85123A'::text) AND (invoiced_at >= '2011-10-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2012-01-01 00:00:00'::timestamp without time zone))
         Heap Blocks: exact=449
         Buffers: shared hit=615
         ->  BitmapAnd (actual time=3.459..3.461 rows=0 loops=1)
               Buffers: shared hit=166
               ->  Bitmap Index Scan on sales_stock_code_idx (actual time=0.203..0.203 rows=2313 loops=1)
                     Index Cond: (stock_code = '85123A'::text)
                     Buffers: shared hit=5
               ->  Bitmap Index Scan on sales_invoiced_at_idx (actual time=3.147..3.147 rows=170978 loops=1)
                     Index Cond: ((invoiced_at >= '2011-10-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2012-01-01 00:00:00'::timestamp without time zone))
                     Buffers: shared hit=161
 Planning:
   Buffers: shared hit=89 read=4
 Planning Time: 0.482 ms
 Execution Time: 4.437 ms
(18 rows)
 
DROP INDEX
CREATE INDEX
                                                                                                QUERY PLAN                                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=1.000..1.002 rows=1 loops=1)
   Buffers: shared hit=449 read=5
   ->  Bitmap Heap Scan on sales (actual time=0.198..0.840 rows=500 loops=1)
         Recheck Cond: ((stock_code = '85123A'::text) AND (invoiced_at >= '2011-10-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2012-01-01 00:00:00'::timestamp without time zone))
         Heap Blocks: exact=449
         Buffers: shared hit=449 read=5
         ->  Bitmap Index Scan on sales_code_date_idx (actual time=0.130..0.131 rows=500 loops=1)
               Index Cond: ((stock_code = '85123A'::text) AND (invoiced_at >= '2011-10-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2012-01-01 00:00:00'::timestamp without time zone))
               Buffers: shared read=5
 Planning:
   Buffers: shared hit=89 read=1
 Planning Time: 0.289 ms
 Execution Time: 1.036 ms
(13 rows)

The planner did not even use the date-first index: it judged the old BitmapAnd cheaper. The product-first index went straight to the 500 rows by reading 5 index pages, and the query took 1.0 ms instead of 4.4 ms.

To see how bad the date-first order really is, try it alone. DDL is transactional in PostgreSQL, so we drop the other indexes inside a transaction and roll back:

sql

BEGIN;
-- try the range-first index on its own (DDL is transactional, so this is undone below)
DROP INDEX sales_stock_code_idx, sales_invoiced_at_idx, sales_code_date_idx;
CREATE INDEX sales_date_code_idx ON sales (invoiced_at, stock_code);

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*), sum(quantity) FROM sales
WHERE stock_code = '85123A'
  AND invoiced_at >= '2011-10-01' AND invoiced_at < '2012-01-01';
ROLLBACK;

Output

BEGIN
DROP INDEX
CREATE INDEX
                                                                                             QUERY PLAN                                                                                             
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=9.285..9.287 rows=1 loops=1)
   Buffers: shared hit=451 read=647
   ->  Index Scan using sales_date_code_idx on sales (actual time=0.036..9.190 rows=500 loops=1)
         Index Cond: ((invoiced_at >= '2011-10-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2012-01-01 00:00:00'::timestamp without time zone) AND (stock_code = '85123A'::text))
         Buffers: shared hit=451 read=647
 Planning:
   Buffers: shared hit=48 read=4
 Planning Time: 0.360 ms
 Execution Time: 9.334 ms
(9 rows)
 
ROLLBACK

Same columns, same Index Cond, but 1,098 pages and 9.3 ms. With the date first, the index walks all of Q4 and checks the product on every entry. The Index Cond line looks identical in both plans; only Buffers and time reveal the difference. Equality first, range last.

Step 3: the leftmost prefix rule in practice

(stock_code, invoiced_at) now makes the single-column (stock_code) index redundant. Drop it and check which queries still get an index:

sql

-- (stock_code) is now a duplicate: it is the leftmost prefix of (stock_code, invoiced_at)
DROP INDEX sales_stock_code_idx;

EXPLAIN (COSTS OFF)
SELECT count(*) FROM sales WHERE stock_code = '85123A';

EXPLAIN (COSTS OFF)
SELECT invoice_no, invoiced_at FROM sales
WHERE stock_code = '85123A'
ORDER BY invoiced_at DESC
LIMIT 5;

EXPLAIN (COSTS OFF)
SELECT count(*) FROM sales WHERE stock_code LIKE '8512%';

BEGIN;
DROP INDEX sales_invoiced_at_idx;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM sales
WHERE invoiced_at >= '2011-12-09' AND invoiced_at < '2011-12-10';
ROLLBACK;

Output

DROP INDEX
                        QUERY PLAN                        
----------------------------------------------------------
 Aggregate
   ->  Index Only Scan using sales_code_date_idx on sales
         Index Cond: (stock_code = '85123A'::text)
(3 rows)
 
                          QUERY PLAN                          
--------------------------------------------------------------
 Limit
   ->  Index Scan Backward using sales_code_date_idx on sales
         Index Cond: (stock_code = '85123A'::text)
(3 rows)
 
                        QUERY PLAN                         
-----------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Seq Scan on sales
                     Filter: (stock_code ~~ '8512%'::text)
(6 rows)
 
BEGIN
DROP INDEX
                                                                                QUERY PLAN                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Seq Scan on sales
                     Filter: ((invoiced_at >= '2011-12-09 00:00:00'::timestamp without time zone) AND (invoiced_at < '2011-12-10 00:00:00'::timestamp without time zone))
(6 rows)
 
ROLLBACK

Four results, four lessons:

Prefix only: stock_code = '85123A' uses the composite index (here as an index-only scan, since count(*) needs no other column).
Sorted for free: ORDER BY invoiced_at DESC LIMIT 5 reads the index backward and stops after 5 rows. No sort node.
LIKE 'prefix%' does not use it. A plain B-tree on text supports LIKE prefixes only when the column's collation is C. Create the index with text_pattern_ops (ON sales (stock_code text_pattern_ops)) if you need prefix searches.
Second column alone: without its own index, a date filter cannot use (stock_code, invoiced_at) and falls back to a full scan. Keep sales_invoiced_at_idx if date-only queries matter.

Step 4: a covering index with INCLUDE

The Q4 query reads quantity, which is not in the index, so it visits 449 table pages. Add it with INCLUDE:

sql

-- a covering index: the key columns plus the column the query reads
CREATE INDEX sales_code_date_incl_idx ON sales (stock_code, invoiced_at) INCLUDE (quantity);

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*), sum(quantity) FROM sales
WHERE stock_code = '85123A'
  AND invoiced_at >= '2011-10-01' AND invoiced_at < '2012-01-01';

Output

CREATE INDEX
                                                                                             QUERY PLAN                                                                                             
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=0.148..0.149 rows=1 loops=1)
   Buffers: shared hit=1 read=5
   ->  Index Only Scan using sales_code_date_incl_idx on sales (actual time=0.038..0.110 rows=500 loops=1)
         Index Cond: ((stock_code = '85123A'::text) AND (invoiced_at >= '2011-10-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2012-01-01 00:00:00'::timestamp without time zone))
         Heap Fetches: 0
         Buffers: shared hit=1 read=5
 Planning:
   Buffers: shared hit=99 read=1
 Planning Time: 0.451 ms
 Execution Time: 0.187 ms
(10 rows)

6 pages and 0.19 ms, down from 615 pages and 4.9 ms in step 1. Heap Fetches: 0: the table was never touched, because VACUUM had marked every page all-visible. Now watch what an update does:

sql

-- touch those 500 rows (a no-op update still writes new row versions)
UPDATE sales SET quantity = quantity
WHERE stock_code = '85123A'
  AND invoiced_at >= '2011-10-01' AND invoiced_at < '2012-01-01';

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*), sum(quantity) FROM sales
WHERE stock_code = '85123A'
  AND invoiced_at >= '2011-10-01' AND invoiced_at < '2012-01-01';

VACUUM sales;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*), sum(quantity) FROM sales
WHERE stock_code = '85123A'
  AND invoiced_at >= '2011-10-01' AND invoiced_at < '2012-01-01';

Output

UPDATE 500
                                                                                             QUERY PLAN                                                                                             
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=1.610..1.611 rows=1 loops=1)
   Buffers: shared hit=969
   ->  Index Only Scan using sales_code_date_incl_idx on sales (actual time=0.035..1.567 rows=500 loops=1)
         Index Cond: ((stock_code = '85123A'::text) AND (invoiced_at >= '2011-10-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2012-01-01 00:00:00'::timestamp without time zone))
         Heap Fetches: 1000
         Buffers: shared hit=969
 Planning:
   Buffers: shared hit=14
 Planning Time: 0.130 ms
 Execution Time: 1.631 ms
(10 rows)
 
VACUUM
                                                                                             QUERY PLAN                                                                                             
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=0.128..0.129 rows=1 loops=1)
   Buffers: shared hit=11
   ->  Index Only Scan using sales_code_date_incl_idx on sales (actual time=0.019..0.088 rows=500 loops=1)
         Index Cond: ((stock_code = '85123A'::text) AND (invoiced_at >= '2011-10-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2012-01-01 00:00:00'::timestamp without time zone))
         Heap Fetches: 0
         Buffers: shared hit=11
 Planning:
   Buffers: shared hit=23
 Planning Time: 0.227 ms
 Execution Time: 0.151 ms
(10 rows)

After the update the plan is still called "Index Only Scan", but it made 1,000 heap fetches (the index now holds both the old and new version of each row, and every one had to be checked in the table) and read 969 pages. VACUUM cleaned the old versions and reset the visibility map, and the scan is back to 11 pages and no heap fetches. On busy tables, index-only scans are only as good as autovacuum keeps up.

Step 5: a partial index for a hot subset

Customer service looks up cancellations by customer all day. Cancellations are a small slice of the table:

sql

SELECT count(*) AS cancellation_lines,
       round(100.0 * count(*) / (SELECT count(*) FROM sales), 1) AS pct
FROM sales WHERE invoice_no LIKE 'C%';

CREATE INDEX sales_customer_idx ON sales (customer_id);
CREATE INDEX sales_cancel_customer_idx ON sales (customer_id) WHERE invoice_no LIKE 'C%';

SELECT indexrelname AS index_name, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE indexrelname IN ('sales_customer_idx', 'sales_cancel_customer_idx')
ORDER BY indexrelname;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT invoice_no, stock_code, quantity FROM sales
WHERE invoice_no LIKE 'C%' AND customer_id = 14911;

Output

 cancellation_lines | pct 
--------------------+-----
               9288 | 1.7
(1 row)
 
CREATE INDEX
CREATE INDEX
        index_name         |  size   
---------------------------+---------
 sales_cancel_customer_idx | 112 kB
 sales_customer_idx        | 3792 kB
(2 rows)
 
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sales (actual time=0.032..0.233 rows=226 loops=1)
   Recheck Cond: ((customer_id = 14911) AND (invoice_no ~~ 'C%'::text))
   Heap Blocks: exact=45
   ->  Bitmap Index Scan on sales_cancel_customer_idx (actual time=0.020..0.020 rows=226 loops=1)
         Index Cond: (customer_id = 14911)
 Planning Time: 0.231 ms
 Execution Time: 0.251 ms
(7 rows)

The partial index holds only the 9,288 cancellation lines (1.7% of the table), so it is 112 kB instead of 3.7 MB, and the planner chose it because the query's WHERE invoice_no LIKE 'C%' matches the index's condition exactly. Customer 14911 has 226 cancelled lines, found without looking at any of their 5,677 normal lines.

Step 6: an expression index for a filter you cannot rewrite

The EXPLAIN lesson rewrote invoiced_at::date = ... as a range. When you cannot change the query (a BI tool generates it), index the expression instead:

sql

-- reports keep filtering on the calendar date
CREATE INDEX sales_invoice_day_idx ON sales ((invoiced_at::date));
ANALYZE sales;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM sales WHERE invoiced_at::date = '2011-11-15';

EXPLAIN (COSTS OFF)
SELECT count(*) FROM sales WHERE date(invoiced_at) = '2011-11-15';

Output

CREATE INDEX
ANALYZE
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Aggregate (actual time=0.568..0.569 rows=1 loops=1)
   ->  Index Scan using sales_invoice_day_idx on sales (actual time=0.028..0.397 rows=3445 loops=1)
         Index Cond: ((invoiced_at)::date = '2011-11-15'::date)
 Planning Time: 0.239 ms
 Execution Time: 0.591 ms
(5 rows)
 
                          QUERY PLAN                          
--------------------------------------------------------------
 Aggregate
   ->  Index Scan using sales_invoice_day_idx on sales
         Index Cond: (date(invoiced_at) = '2011-11-15'::date)
(3 rows)

The query that took 35 ms with a full scan in the EXPLAIN lesson now takes 0.6 ms. date(invoiced_at) also matches, because it is the same cast written differently; date_trunc('day', invoiced_at) would not. Running ANALYZE after creating an expression index matters: PostgreSQL then collects statistics on the expression's values, which fixes the guessed row estimates you saw before.

Step 7: which indexes earn their keep

Reset the usage counters, run a small "day" of typical queries, and look:

sql

SELECT pg_stat_reset();

sql

-- a day of typical queries
SELECT sum(quantity) FROM sales WHERE stock_code = '85123A' AND invoiced_at >= '2011-10-01' AND invoiced_at < '2012-01-01';
SELECT sum(quantity) FROM sales WHERE stock_code = '22423' AND invoiced_at >= '2011-11-01' AND invoiced_at < '2011-12-01';
SELECT invoice_no, invoiced_at FROM sales WHERE stock_code = '85123A' ORDER BY invoiced_at DESC LIMIT 5;
SELECT count(*) FROM sales WHERE invoice_no LIKE 'C%' AND customer_id = 14911;
SELECT count(*) FROM sales WHERE invoiced_at::date = '2011-11-15';
SELECT count(*) FROM sales WHERE customer_id = 17850;

sql

SELECT indexrelname AS index_name,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'sales'
ORDER BY idx_scan, indexrelname;

Output

        index_name         | idx_scan |  size   
---------------------------+----------+---------
 sales_cancel_customer_idx |        1 | 112 kB
 sales_code_date_idx       |        1 | 16 MB
 sales_customer_idx        |        1 | 3792 kB
 sales_invoice_day_idx     |        1 | 3808 kB
 sales_invoiced_at_idx     |        1 | 4104 kB
 sales_code_date_incl_idx  |        2 | 21 MB
(6 rows)

Two things to read here:

sales_code_date_idx (16 MB) duplicates sales_code_date_incl_idx (21 MB): same key columns, and the second one also carries quantity. Its one use (the ORDER BY ... LIMIT 5 query) would be served just as well by the other. Drop it and save 16 MB and its write cost.
sales_invoiced_at_idx shows 1 scan, but no query here filters on the date alone. The planner itself peeked into it to estimate the date ranges, because the queries asked for dates beyond its statistics. A non-zero idx_scan does not prove an index serves queries; a zero over a month of production traffic is strong evidence it does not. Check over weeks, not one test, and look at the queries in pg_stat_statements before dropping.

Step 8: what the indexes cost on writes

sql

CREATE TABLE copy_no_indexes (LIKE sales);
CREATE TABLE copy_six_indexes (LIKE sales INCLUDING INDEXES);

\timing on
INSERT INTO copy_no_indexes  SELECT * FROM sales;
INSERT INTO copy_six_indexes SELECT * FROM sales;
\timing off

SELECT pg_size_pretty(pg_table_size('copy_six_indexes'))   AS table_size,
       pg_size_pretty(pg_indexes_size('copy_six_indexes')) AS indexes_size;

Output

CREATE TABLE
CREATE TABLE
Timing is on.
INSERT 0 541909
Time: 1105.799 ms (00:01.106)
INSERT 0 541909
Time: 8550.215 ms (00:08.550)
Timing is off.
 table_size | indexes_size 
------------+--------------
 58 MB      | 68 MB
(1 row)

Loading the same 541,909 rows took 1.1 seconds into a bare table and 8.6 seconds into one with the six indexes: almost 8 times slower. The indexes together (68 MB) are bigger than the table (58 MB). For big batch loads, a common trick is to drop secondary indexes, load, and recreate them, which builds each index once in sorted order instead of row by row.

Common mistakes

Range column first. (invoiced_at, stock_code) read 1,098 pages where (stock_code, invoiced_at) read 454. Equality columns first, then the range or sort column.
One index per column "to cover everything". The planner can combine them with BitmapAnd, but it is slower than one composite index, and each extra index costs writes.
Keeping indexes that a composite already covers. (a) is redundant once (a, b) exists. So is (a, b) once (a, b) INCLUDE (c) exists.
Expecting (a, b) to help a filter on b alone. It does not in PostgreSQL 16 and most databases. Index b separately if those queries matter.
Forgetting that index-only scans need a vacuumed table. Heap fetches rise after every batch of updates until autovacuum runs. On append-heavy tables, tune autovacuum or vacuum after loads.
Partial index conditions the query cannot prove. An index WHERE status = 'open' cannot serve a prepared statement's generic plan for WHERE status = $1`, because the planner cannot prove `$1 is 'open'. Write the literal in the query.
Expression index with a different expression. date_trunc('day', ts) does not match an index on ts::date. Use the same expression in queries and index.
Judging an index by one test run. idx_scan counts even the planner's own peeks. Watch it over weeks of real traffic.
Too many indexes on a write-heavy table. Eight times slower loads here. Every index must earn its write cost.

Interview angle

You have an index on (country, created_at). Which of these WHERE clauses can use it?

country = 'IN': yes, it is the leftmost column. 2) country = 'IN' AND created_at >= '2025-01-01': yes, both columns, and it is the ideal case (equality then range). 3) created_at >= '2025-01-01' alone: no, the second column is not searchable without the first (except skip scans in PostgreSQL 18 or Oracle when country has few values). 4) country IN ('IN', 'US') AND created_at > x: yes, one range per country value. 5) lower(country) = 'in' or country LIKE '%dia': no, the function or leading wildcard hides the column. Bonus: WHERE country = 'IN' ORDER BY created_at DESC LIMIT 10 needs no sort, because the index is already in that order.

What is a covering index and why does it help?

An index that contains every column a query needs, either as key columns or as INCLUDE columns, so the database can answer from the index without reading the table. That saves one random table read per row. In PostgreSQL it shows as an Index Only Scan, and it only skips the table for pages the visibility map marks as all-visible, so it depends on vacuum. The trade-off is a bigger index and more write cost, so include only the few columns a hot query actually reads.

When would you use a partial index?

When queries consistently target a small, well-defined subset of a large table: open orders, unprocessed jobs, soft-deleted rows excluded, cancellations. The index stores only those rows, so it is a fraction of the size, stays in memory, and costs nothing for writes to rows outside the subset. The query's WHERE must imply the index's condition with literal values, or the planner cannot use it.

How do you find indexes to drop?

Look for indexes with zero or near-zero idx_scan in pg_stat_user_indexes over a long window of production traffic (and on replicas, which keep their own counters), indexes that are a leftmost prefix of another index, and exact duplicates. Check they do not enforce a constraint (unique and primary keys) and are not needed for a monthly job. Then drop them, ideally after making them invisible or testing on a replica, and watch query latency.

Practice task

Use Olist (CC BY-NC-SA 4.0) for two queries a marketplace runs constantly.

Load olist_orders_dataset.csv into orders (primary key order_id) and olist_order_items_dataset.csv into order_items (primary key (order_id, order_item_id)), then VACUUM ANALYZE.
Seller dashboard: the shipping dates and prices of seller 6560211a19b47992c3666cc44a7e94c0 for the first quarter of 2018, newest first.
Ops queue: the 20 oldest orders still in progress (status shipped, invoiced, processing, created or approved).
Record each query's plan, then design one index per query so the first becomes an index-only scan with no sort and the second reads only a few pages. Compare index sizes.

Show a solution

sql

CREATE TABLE orders (
  order_id text PRIMARY KEY,
  customer_id text,
  order_status text,
  order_purchase_timestamp timestamp,
  order_approved_at timestamp,
  order_delivered_carrier_date timestamp,
  order_delivered_customer_date timestamp,
  order_estimated_delivery_date timestamp
);
\copy orders FROM 'olist_orders_dataset.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE order_items (
  order_id text,
  order_item_id int,
  product_id text,
  seller_id text,
  shipping_limit_date timestamp,
  price numeric(10,2),
  freight_value numeric(10,2),
  PRIMARY KEY (order_id, order_item_id)
);
\copy order_items FROM 'olist_order_items_dataset.csv' WITH (FORMAT csv, HEADER true)
VACUUM ANALYZE;

Output

CREATE TABLE
COPY 99441
CREATE TABLE
COPY 112650
VACUUM

The two queries before any new index:

sql

-- query 1: a seller's items for a quarter, newest first
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT shipping_limit_date, price
FROM order_items
WHERE seller_id = '6560211a19b47992c3666cc44a7e94c0'
  AND shipping_limit_date >= '2018-01-01' AND shipping_limit_date < '2018-04-01'
ORDER BY shipping_limit_date DESC;

-- query 2: orders still in progress, oldest first
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT order_id, order_status, order_purchase_timestamp
FROM orders
WHERE order_status IN ('shipped', 'invoiced', 'processing', 'created', 'approved')
ORDER BY order_purchase_timestamp
LIMIT 20;

Output

                                                                                                               QUERY PLAN                                                                                                                
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (actual time=13.547..13.569 rows=297 loops=1)
   Sort Key: shipping_limit_date DESC
   Sort Method: quicksort  Memory: 36kB
   Buffers: shared hit=2331
   ->  Seq Scan on order_items (actual time=0.019..13.198 rows=297 loops=1)
         Filter: ((shipping_limit_date >= '2018-01-01 00:00:00'::timestamp without time zone) AND (shipping_limit_date < '2018-04-01 00:00:00'::timestamp without time zone) AND (seller_id = '6560211a19b47992c3666cc44a7e94c0'::text))
         Rows Removed by Filter: 112353
         Buffers: shared hit=2328
 Planning:
   Buffers: shared hit=122
 Planning Time: 0.915 ms
 Execution Time: 13.733 ms
(12 rows)
 
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Limit (actual time=19.209..19.214 rows=20 loops=1)
   Buffers: shared hit=1840
   ->  Sort (actual time=19.206..19.209 rows=20 loops=1)
         Sort Key: order_purchase_timestamp
         Sort Method: top-N heapsort  Memory: 29kB
         Buffers: shared hit=1840
         ->  Seq Scan on orders (actual time=0.011..18.742 rows=1729 loops=1)
               Filter: (order_status = ANY ('{shipped,invoiced,processing,created,approved}'::text[]))
               Rows Removed by Filter: 97712
               Buffers: shared hit=1840
 Planning:
   Buffers: shared hit=78
 Planning Time: 0.396 ms
 Execution Time: 19.249 ms
(14 rows)

Both scan the whole table and sort. Query 1 needs equality on seller_id, a range and sort on shipping_limit_date, and reads price: a composite index with price in INCLUDE. Query 2 targets 1,729 of 99,441 orders and sorts by purchase time: a partial index on the purchase time for in-progress orders only.

sql

CREATE INDEX order_items_seller_ship_idx
  ON order_items (seller_id, shipping_limit_date) INCLUDE (price);

CREATE INDEX orders_open_idx
  ON orders (order_purchase_timestamp)
  WHERE order_status IN ('shipped', 'invoiced', 'processing', 'created', 'approved');

VACUUM ANALYZE order_items, orders;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT shipping_limit_date, price
FROM order_items
WHERE seller_id = '6560211a19b47992c3666cc44a7e94c0'
  AND shipping_limit_date >= '2018-01-01' AND shipping_limit_date < '2018-04-01'
ORDER BY shipping_limit_date DESC;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT order_id, order_status, order_purchase_timestamp
FROM orders
WHERE order_status IN ('shipped', 'invoiced', 'processing', 'created', 'approved')
ORDER BY order_purchase_timestamp
LIMIT 20;

SELECT indexrelname AS index_name, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE indexrelname IN ('order_items_seller_ship_idx', 'orders_open_idx', 'orders_pkey')
ORDER BY indexrelname;

Output

CREATE INDEX
CREATE INDEX
VACUUM
                                                                                                              QUERY PLAN                                                                                                               
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan Backward using order_items_seller_ship_idx on order_items (actual time=0.025..0.073 rows=297 loops=1)
   Index Cond: ((seller_id = '6560211a19b47992c3666cc44a7e94c0'::text) AND (shipping_limit_date >= '2018-01-01 00:00:00'::timestamp without time zone) AND (shipping_limit_date < '2018-04-01 00:00:00'::timestamp without time zone))
   Heap Fetches: 0
   Buffers: shared hit=1 read=6
 Planning:
   Buffers: shared hit=45
 Planning Time: 0.175 ms
 Execution Time: 0.105 ms
(8 rows)
 
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Limit (actual time=0.015..0.043 rows=20 loops=1)
   Buffers: shared hit=20 read=2
   ->  Index Scan using orders_open_idx on orders (actual time=0.014..0.040 rows=20 loops=1)
         Buffers: shared hit=20 read=2
 Planning:
   Buffers: shared hit=26
 Planning Time: 0.169 ms
 Execution Time: 0.050 ms
(8 rows)
 
         index_name          |  size   
-----------------------------+---------
 order_items_seller_ship_idx | 8536 kB
 orders_open_idx             | 56 kB
 orders_pkey                 | 7680 kB
(3 rows)

Query	Before	After	Index
Seller dashboard	Seq Scan + Sort, 2,331 pages, 13.7 ms	Index Only Scan Backward, 7 pages, 0.1 ms	`(seller_id, shipping_limit_date) INCLUDE (price)`, 8.3 MB
Ops queue	Seq Scan + top-N sort, 1,840 pages, 19.2 ms	Index Scan, 22 pages, 0.05 ms	partial on `order_purchase_timestamp`, 56 kB

The seller index has no sort node: it reads the index backward to get newest first. The partial index has no Index Cond at all: every entry in it is an in-progress order, already sorted by purchase time, so the planner reads the first 20 entries and stops. At 56 kB it is 0.7% of the size of the orders primary key.

#Why it matters

#The concept

#A composite index is sorted by its first column, then the next

#Column order: equality first, then range

#The leftmost prefix rule

#Covering indexes and INCLUDE

#Partial indexes

#Expression indexes

#What every index costs

#Clustered indexes vs heap tables

#Worked example: indexing the sales table

#Step 1: two single-column indexes

#Step 2: the same two columns, in both orders

#Step 3: the leftmost prefix rule in practice

#Step 4: a covering index with INCLUDE

#Step 5: a partial index for a hot subset

#Step 6: an expression index for a filter you cannot rewrite

#Step 7: which indexes earn their keep

#Step 8: what the indexes cost on writes

#Common mistakes

#Interview angle

#Practice task

#Further reading

Why it matters

The concept

A composite index is sorted by its first column, then the next

Column order: equality first, then range

The leftmost prefix rule

Covering indexes and INCLUDE

Partial indexes

Expression indexes

What every index costs

Clustered indexes vs heap tables

Worked example: indexing the sales table

Step 1: two single-column indexes

Step 2: the same two columns, in both orders

Step 3: the leftmost prefix rule in practice

Step 4: a covering index with INCLUDE

Step 5: a partial index for a hot subset

Step 6: an expression index for a filter you cannot rewrite

Step 7: which indexes earn their keep

Step 8: what the indexes cost on writes

Common mistakes

Interview angle

Practice task

Further reading