index_review.sql in SQL

Asking the database what indexes it has, which ones it uses, and which ones it is only paying for.

-- An index audit: what exists, what it covers, what the planner does with it.

CREATE INDEX IF NOT EXISTS orders_by_customer_date
    ON orders (customer_id, placed_at DESC);

CREATE INDEX IF NOT EXISTS orders_by_status
    ON orders (status);

ANALYZE;

-- Every index in the schema, with the statement that created it.
SELECT
    tbl_name AS table_name,
    name AS index_name,
    CASE
        WHEN sql IS NULL THEN 'implicit (PRIMARY KEY or UNIQUE)'
        ELSE sql
    END AS definition
FROM sqlite_master
WHERE type = 'index'
ORDER BY tbl_name, name;

-- What the planner now believes about their selectivity.
SELECT
    tbl AS table_name,
    idx AS index_name,
    stat AS rows_and_buckets
FROM sqlite_stat1
ORDER BY tbl, idx;

-- The columns inside one index, in index order.
PRAGMA index_list('orders');

PRAGMA index_xinfo('orders_by_customer_date');

-- Three queries, three plans. Read each one against the list above.
EXPLAIN QUERY PLAN
SELECT id
FROM orders
WHERE customer_id = 7;

EXPLAIN QUERY PLAN
SELECT id
FROM orders
WHERE status = 'paid'
ORDER BY placed_at DESC;

EXPLAIN QUERY PLAN
SELECT
    c.name,
    SUM(o.total) AS revenue
FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.id
GROUP BY c.id
ORDER BY revenue DESC
LIMIT 10;

How it works

sqlite_master holds the DDL of every index you created.
index_list and index_xinfo report the columns, in order.
An index no plan mentions is a write cost with no reader.

Keywords and builtins used here

ANALYZE
AS
BY
CASE
CREATE
DESC
ELSE
END
EXISTS
EXPLAIN
FROM
GROUP
IF
INDEX
IS
JOIN
LIMIT
NOT
NULL
ON
ORDER
SELECT
SUM
THEN
WHEN
WHERE
c
sql
table_name
type

The run, in numbers

Lines: 57
Characters to type: 1200
Tokens: 171
Three-star pace: 100 tpm

At the three-star pace of 100 tokens a minute, this run takes about 103 seconds.

Type this snippet

Step 1 of 2 in Encore, step 16 of 17 in Plans & performance.

← Previous Next →