Covering, partial and expression indexes

Three kinds of index fit an index more closely to the queries it serves. A covering index holds every column a query needs, so the database answers from the index alone and never reads the table: PostgreSQL calls it an Index Only Scan, SQLite says USING COVERING INDEX and MySQL Using index. A partial index holds only the rows that match a WHERE clause written into the index, such as the active subscriptions, so it is smaller and cheaper to keep up to date. Expression indexes store a value computed from the columns, such as lower(email), so that a query comparing that expression can search instead of scanning. Each comes with a condition: the covering index must hold the columns, the query’s WHERE must imply the partial index’s predicate, and the query must use the indexed expression exactly.

Three ways to fit an index to a query

All three on 20,000 users and 60,000 subscriptions, in your browser:

-- 20,000 users whose emails were typed in three different cases, and 60,000 subscriptions, a tenth of them active.
CREATE TABLE users (
  id    INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  name  TEXT NOT NULL,
  city  TEXT NOT NULL
);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
INSERT INTO users
SELECT i,
       CASE i % 3 WHEN 0 THEN 'User' || i || '@Example.in' WHEN 1 THEN 'user' || i || '@example.in' ELSE 'USER' || i || '@EXAMPLE.IN' END,
       'User ' || i,
       CASE i % 5 WHEN 0 THEN 'Pune' WHEN 1 THEN 'Kochi' WHEN 2 THEN 'Jaipur' WHEN 3 THEN 'Gangtok' ELSE 'Indore' END
FROM n;
CREATE TABLE subscriptions (
  id      INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  status  TEXT    NOT NULL,
  plan    TEXT    NOT NULL
);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 60000)
INSERT INTO subscriptions
SELECT i, 1 + (i * 7919) % 20000, CASE WHEN i % 10 = 0 THEN 'active' ELSE 'ended' END,
       CASE i % 2 WHEN 0 THEN 'monthly' ELSE 'yearly' END
FROM n;

-- 1. Covering: the index holds every column the query needs, so the table is never read.
CREATE INDEX users_city_name ON users (city, name);
EXPLAIN QUERY PLAN SELECT name FROM users WHERE city = 'Pune';

-- 2. Partial: only the active subscriptions are in the index; the query must say status = 'active' too.
CREATE INDEX subscriptions_active ON subscriptions (user_id) WHERE status = 'active';
EXPLAIN QUERY PLAN SELECT id, plan FROM subscriptions WHERE user_id = 42 AND status = 'active';

-- 3. Expression: the index holds lower(email), and the query compares the same expression.
CREATE INDEX users_email_lower ON users (lower(email));
EXPLAIN QUERY PLAN SELECT id, email FROM users WHERE lower(email) = lower('USER42@example.IN');
SELECT id, email FROM users WHERE lower(email) = lower('USER42@example.IN');

┌────┬────────┬─────────┬────────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                           detail                           │
├────┼────────┼─────────┼────────────────────────────────────────────────────────────┤
│ 2  │ 0      │ 55      │ SEARCH users USING COVERING INDEX users_city_name (city=?) │
└────┴────────┴─────────┴────────────────────────────────────────────────────────────┘
┌────┬────────┬─────────┬───────────────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                              detail                               │
├────┼────────┼─────────┼───────────────────────────────────────────────────────────────────┤
│ 3  │ 0      │ 61      │ SEARCH subscriptions USING INDEX subscriptions_active (user_id=?) │
└────┴────────┴─────────┴───────────────────────────────────────────────────────────────────┘
┌────┬────────┬─────────┬───────────────────────────────────────────────────────┐
│ id │ parent │ notused │                        detail                         │
├────┼────────┼─────────┼───────────────────────────────────────────────────────┤
│ 3  │ 0      │ 61      │ SEARCH users USING INDEX users_email_lower (<expr>=?) │
└────┴────────┴─────────┴───────────────────────────────────────────────────────┘
┌────┬───────────────────┐
│ id │       email       │
├────┼───────────────────┤
│ 42 │ User42@Example.in │
└────┴───────────────────┘

The covering plan says USING COVERING INDEX: name and city are in the index, and so is the rowid, so the 4,000 users of Pune are listed without a single table page being read. The partial index holds only the 6,000 active subscriptions, and it serves the query because the query’s own WHERE says status = 'active'. The expression plan searches (<expr>=?), the stored lower-case emails, and the row comes back whatever case was typed. None of the three changes what a query returns: an index is only a faster way to the same rows. What they change is how many pages a query reads, how large the index grows and which queries can use it. Expression indexes and partial indexes both trade generality for size or speed, and the rest of this lesson measures five details that decide whether they pay:

• Index-only scans in PostgreSQL also need the visibility map that VACUUM keeps, or every row is checked in the table.
• INCLUDE adds columns to a PostgreSQL index that are stored but not searched, to cover a query.
• Partial unique indexes enforce a rule on some rows only, such as one active subscription per user.
• MySQL has no partial indexes, so a unique index on an expression stands in for one.
• Immutable expressions only: PostgreSQL refuses to index a value that can change, such as a day that depends on the time zone setting.

Covering indexes and index-only scans, with pages counted Pro: opens with a Pro, Premium or Ultimate pass