mufassall

Cheat sheet · Data & storage

Database indexes

13 min · Intermediate · mufassall.dtwin.uz/en/l/indexes/

← Open the lesson

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: a Seq Scan versus an Index 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
Final scene
40%35%

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_db

Mermaid: 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_scan

D2

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_db

PlantUML: 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
@enduml

PlantUML: 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
@enduml

Recap

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

MetricStartEnd
Page p952 400 ms2 ms
Rows read per query40 000 00020
Insert per row0.3 ms0.5 ms
Indexes on orders13

Steps

  1. Problem The order history page takes 2.4 seconds

    A customer opens "My orders".

  2. Problem EXPLAIN shows a full table scan

    EXPLAIN ANALYZE runs the query and prints the plan the database chose.

  3. Fix A B-tree index on customer_id

    An index is a separate sorted structure: a B-tree of customer_id values, each pointing to its rows in the table.

  4. Problem A wholesale customer with 250 000 orders

    Customer 7 is a reseller with 250 000 orders.

  5. 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.

  6. Problem The new index does not help yesterday's report

    A daily report sums all orders since yesterday, for every customer.

  7. Fix An index for the report's query

    We add an index on created_at alone.

  8. 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 status and total.

  9. Problem Seven indexes, and imports crawl

    Over a year, three more indexes were added "just in case": on status, total and the order note.

  10. Fix Drop the indexes nobody reads

    The database counts how many times each index was used.

  11. Recap Recap

    Start with the plan: EXPLAIN ANALYZE and the line Rows Removed by Filter tell 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.