Partial indexes in SQL

If the query always filters the same way, the index does not need the other rows.

CREATE INDEX orders_pending
    ON orders (placed_at)
    WHERE status = 'pending';

EXPLAIN QUERY PLAN
SELECT
    id,
    placed_at
FROM orders
WHERE status = 'pending'
ORDER BY placed_at;

How it works

1. A WHERE clause on CREATE INDEX indexes only the matching rows.
2. Smaller index, cheaper writes, and it stays useful for exactly one query shape.
3. The planner uses it only when the query's filter implies the index's.

Keywords and builtins used here

• BY
• CREATE
• EXPLAIN
• FROM
• INDEX
• ON
• ORDER
• SELECT
• WHERE

The run, in numbers

Lines

11

Characters to type

173

Tokens

30

Three-star pace

90 tpm

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

Type this snippet

Step 3 of 5 in Indexes, step 5 of 17 in Plans & performance.

← Previous Next →