Cheat sheet · Data & storage
Database indexes
The problem
An order history page gets slow as the table grows to 40 million rows. We read the query plan, add the right B-tree indexes, and see what every index costs on writes.
What you learn
- Read
EXPLAIN: aSeq Scanversus anIndex Scan - Why the column order of a composite index decides which queries it serves
- How a covering index answers a query without touching the table
- Why every extra index slows down every
INSERT
Diagram as code
The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.
Mermaid: flowchart
flowchart LR
n_app(["Shop app"])
n_api["Orders API"]
n_db[("Orders DB")]
n_report("Daily report")
n_import("Bulk import")
n_app --> n_api
n_api --> n_db
n_report --> n_db
n_import --> n_dbMermaid: request sequence
sequenceDiagram
participant n_api as Orders API
participant n_db as Orders DB
participant n_report as Daily report
participant n_import as Bulk import
Note over n_api,n_db: The order history page takes 2.4 seconds
n_api->>n_db: SELECT id, status, total FROM orders
n_db-->>n_api: (20 rows)
Note over n_api,n_db: EXPLAIN shows a full table scan
n_api->>n_db: EXPLAIN ANALYZE
n_db-->>n_api: Limit (actual rows=20)
Note over n_api,n_db: A B-tree index on customer_id
n_api->>n_db: CREATE INDEX CONCURRENTLY idx_orders_customer
n_db-->>n_api: CREATE INDEX
Note over n_api,n_db: A wholesale customer with 250 000 orders
n_api->>n_db: EXPLAIN ANALYZE
n_db-->>n_api: Limit (actual rows=20)
Note over n_api,n_db: One index on two columns
n_api->>n_db: EXPLAIN ANALYZE
n_db-->>n_api: Limit (actual rows=20)
Note over n_report,n_db: The new index does not help yesterday's report
n_report->>n_db: EXPLAIN ANALYZE SELECT count(*), sum(total) FROM orders
n_db-->>n_report: Aggregate
Note over n_report,n_db: An index for the report's query
n_report->>n_db: EXPLAIN ANALYZE SELECT count(*), sum(total) FROM orders
n_db-->>n_report: Aggregate
Note over n_api,n_db: A covering index never touches the table
n_api->>n_db: EXPLAIN ANALYZE
n_db-->>n_api: Limit (actual rows=20)
Note over n_import,n_db: Seven indexes, and imports crawl
n_import->>n_db: INSERT INTO orders (customer_id, created_at, status, total)
n_db-->>n_import: INSERT 0 500
Note over n_api,n_db: Drop the indexes nobody reads
n_api->>n_db: SELECT indexrelname, idx_scan
n_db-->>n_api: indexrelname #124; idx_scanD2
direction: right
n_app: "Shop app" {
shape: rectangle
}
n_api: "Orders API" {
shape: rectangle
}
n_db: "Orders DB" {
shape: cylinder
}
n_report: "Daily report" {
shape: rectangle
style.multiple: true
}
n_import: "Bulk import" {
shape: rectangle
style.multiple: true
}
n_app -> n_api
n_api -> n_db
n_report -> n_db
n_import -> n_dbPlantUML: request sequence
@startuml
participant "Orders API" as n_api
database "Orders DB" as n_db
participant "Daily report" as n_report
participant "Bulk import" as n_import
note over n_api, n_db : The order history page takes 2.4 seconds
n_api -> n_db : SELECT id, status, total FROM orders
n_db --> n_api : (20 rows)
note over n_api, n_db : EXPLAIN shows a full table scan
n_api -> n_db : EXPLAIN ANALYZE
n_db --> n_api : Limit (actual rows=20)
note over n_api, n_db : A B-tree index on customer_id
n_api -> n_db : CREATE INDEX CONCURRENTLY idx_orders_customer
n_db --> n_api : CREATE INDEX
note over n_api, n_db : A wholesale customer with 250 000 orders
n_api -> n_db : EXPLAIN ANALYZE
n_db --> n_api : Limit (actual rows=20)
note over n_api, n_db : One index on two columns
n_api -> n_db : EXPLAIN ANALYZE
n_db --> n_api : Limit (actual rows=20)
note over n_report, n_db : The new index does not help yesterday's report
n_report -> n_db : EXPLAIN ANALYZE SELECT count(*), sum(total) FROM orders
n_db --> n_report : Aggregate
note over n_report, n_db : An index for the report's query
n_report -> n_db : EXPLAIN ANALYZE SELECT count(*), sum(total) FROM orders
n_db --> n_report : Aggregate
note over n_api, n_db : A covering index never touches the table
n_api -> n_db : EXPLAIN ANALYZE
n_db --> n_api : Limit (actual rows=20)
note over n_import, n_db : Seven indexes, and imports crawl
n_import -> n_db : INSERT INTO orders (customer_id, created_at, status, total)
n_db --> n_import : INSERT 0 500
note over n_api, n_db : Drop the indexes nobody reads
n_api -> n_db : SELECT indexrelname, idx_scan
n_db --> n_api : indexrelname | idx_scan
@endumlPlantUML: components
@startuml
left to right direction
agent "Shop app" as n_app
component "Orders API" as n_api
database "Orders DB" as n_db
collections "Daily report" as n_report
collections "Bulk import" as n_import
n_app --> n_api
n_api --> n_db
n_report --> n_db
n_import --> n_db
@endumlRecap
Start with the plan: EXPLAIN ANALYZE and the line Rows Removed by Filter tell you what the database is really doing.
1) A B-tree index turns a full scan into a tree walk. 2) In a composite index the column order matters: filters first, sort column next. 3) INCLUDE makes it covering, so the table is not read. 4) Every index costs on every write: check idx_scan and drop what nobody uses.
Numbers: at the start and at the end
| Metric | Start | End |
|---|---|---|
| Page p95 | 2 400 ms | 2 ms |
| Rows read per query | 40 000 000 | 20 |
| Insert per row | 0.3 ms | 0.5 ms |
| Indexes on orders | 1 | 3 |
Steps
- Problem The order history page takes 2.4 seconds
A customer opens "My orders".
- Problem EXPLAIN shows a full table scan
EXPLAIN ANALYZEruns the query and prints the plan the database chose. - Fix A B-tree index on customer_id
An index is a separate sorted structure: a B-tree of
customer_idvalues, each pointing to its rows in the table. - Problem A wholesale customer with 250 000 orders
Customer
7is a reseller with 250 000 orders. - Fix One index on two columns
A composite index
(customer_id, created_at DESC)is sorted by customer first, then by date inside each customer. - Problem The new index does not help yesterday's report
A daily report sums all orders since yesterday, for every customer.
- Fix An index for the report's query
We add an index on
created_atalone. - Fix A covering index never touches the table
Even with the composite index, each of the 20 rows is a jump into the table to read
statusandtotal. - Problem Seven indexes, and imports crawl
Over a year, three more indexes were added "just in case": on
status,totaland the order note. - Fix Drop the indexes nobody reads
The database counts how many times each index was used.
- Recap Recap
Start with the plan:
EXPLAIN ANALYZEand the lineRows Removed by Filtertell you what the database is really doing.
Terms
- Index
- A separate sorted structure, usually a B-tree, that lets the database find rows without scanning the whole table. In a composite index the column order matters, and every index slows down every write.