Reading Query Plans with EXPLAIN

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS sales_order;DROP TABLE IF EXISTS customer;CREATE TABLE customer (    customer_id INTEGER PRIMARY KEY,    email       TEXT NOT NULL UNIQUE,    region      TEXT NOT NULL CHECK (region IN ('north','south','east','west')),    joined_at   TEXT NOT NULL) STRICT;CREATE TABLE sales_order (    order_id    INTEGER PRIMARY KEY,    customer_id INTEGER NOT NULL REFERENCES customer(customer_id),    status      TEXT NOT NULL CHECK (status IN ('pending','processing','paid','cancelled')),    ordered_at  TEXT NOT NULL,    total_cents INTEGER NOT NULL CHECK (total_cents >= 0),    channel     TEXT NOT NULL CHECK (channel IN ('web','mobile','partner'))) STRICT;WITH RECURSIVE seq(n) AS (    VALUES (1)    UNION ALL    SELECT n + 1 FROM seq WHERE n < 1000)INSERT INTO customer (customer_id, email, region, joined_at)SELECT    n,    printf('customer%04d@example.com', n),    CASE n % 4        WHEN 0 THEN 'north'        WHEN 1 THEN 'south'        WHEN 2 THEN 'east'        ELSE 'west'    END,    date('2023-01-01', printf('+%d days', n % 730))FROM seq;WITH RECURSIVE seq(n) AS (    VALUES (1)    UNION ALL    SELECT n + 1 FROM seq WHERE n < 20000)INSERT INTO sales_order    (order_id, customer_id, status, ordered_at, total_cents, channel)SELECT    n,    ((n * 37) % 1000) + 1,    CASE n % 20        WHEN 0 THEN 'pending'        WHEN 1 THEN 'processing'        WHEN 2 THEN 'cancelled'        ELSE 'paid'    END,    datetime('2025-01-01', printf('+%d hours', n % 8760)),    1000 + ((n * 7919) % 90000),    CASE n % 3 WHEN 0 THEN 'web' WHEN 1 THEN 'mobile' ELSE 'partner' ENDFROM seq;

EXPLAIN QUERY PLANSELECT order_id, total_centsFROM sales_orderWHERE channel = 'mobile';

Operation: SCAN sales_orderMeaning: visit a broad portion of the table and test the predicateQuestion: is the match fraction large enough that scanning is reasonable?

CREATE INDEX IF NOT EXISTS idx_order_customer_dateON sales_order (customer_id, ordered_at);EXPLAIN QUERY PLANSELECT order_id, ordered_at, total_centsFROM sales_orderWHERE customer_id = 417  AND ordered_at >= '2025-06-01';

Operation: SEARCH sales_order USING INDEX idx_order_customer_dateConstraint: customer_id = ? AND ordered_at > ?Meaning: navigate to a narrow key range before fetching rows

CREATE INDEX idx_order_status_date_totalON sales_order (status, ordered_at, total_cents);EXPLAIN QUERY PLANSELECT ordered_at, total_centsFROM sales_orderWHERE status = 'pending'ORDER BY ordered_at;

Operation: SEARCH sales_order USING COVERING INDEX idx_order_status_date_totalTable lookup: avoided for requested columnsOrdering: produced by the same index key order

EXPLAIN QUERY PLANSELECT order_id, customer_id, total_centsFROM sales_orderWHERE status = 'pending'ORDER BY total_cents DESC, customer_id;

Plan signal: USE TEMP B-TREE FOR ORDER BYMeaning: qualifying rows must be materialized and sortedDecision: add an index only when this ordering is frequent and valuable

CREATE INDEX IF NOT EXISTS idx_order_customer_dateON sales_order (customer_id, ordered_at);EXPLAIN QUERY PLANSELECT    c.customer_id,    c.email,    o.order_id,    o.ordered_atFROM customer AS cJOIN sales_order AS o  ON o.customer_id = c.customer_idWHERE c.email = 'customer0417@example.com'  AND o.ordered_at >= '2025-06-01';

EXPLAIN QUERY PLANSELECT customer_id FROM sales_order WHERE status = 'pending'UNIONSELECT customer_id FROM sales_order WHERE status = 'processing';

EXPLAIN (COSTS, VERBOSE)SELECT order_id, ordered_at, total_centsFROM sales_orderWHERE customer_id = 417  AND ordered_at >= DATE '2025-06-01'ORDER BY ordered_at DESCLIMIT 20;

Node: LimitChild: Index Scan using idx_order_customer_dateCost: startup_cost .. total_costRows: estimated rows emitted by this nodeWidth: estimated average row width in bytesCondition: Index Cond or Filter

BEGIN;EXPLAIN (ANALYZE, BUFFERS, WAL, FORMAT TEXT)UPDATE sales_orderSET status = 'processing'WHERE order_id = 9001;ROLLBACK;