Skip to content
Tutorials Center
My learning

Topics

Lesson 89 of 125 ยท Performance

Partial, Expression and Covering Indexes

Design indexes that fit your queries. Use partial and expression indexes, build covering indexes, choose column order, weigh write costs and run ANALYZE.

A plain index on one column is a blunt tool. It helps some queries, ignores others, and costs space and write time whether it's useful or not. Experienced developers design indexes the way a tailor cuts a suit: to fit the queries the application actually runs.

This lesson covers the techniques that make that possible. Partial indexes index only the rows you search for. Expression indexes index a computed value such as lower(email) or a field inside JSON. Covering indexes contain every column a query needs. You'll also learn the rules for column order in multi-column indexes, measure what indexes cost on writes, and see how ANALYZE helps the planner choose.

If query plans are new to you, read Reading Query Plans first. Every example here builds a table with tens of thousands of rows using a recursive CTE, so the plans reflect a realistic workload.

Partial indexes: index only the rows you need

Imagine an orders table where 95% of orders are shipped and only 5% are pending. The warehouse screen constantly asks for pending orders, while shipped orders are only read one at a time by id. An index on (status, ordered_on) would spend 95% of its entries on rows nobody searches that way.

A partial index has a WHERE clause. Only rows that satisfy it get an entry, so the index is small, cheap to maintain and fast to search:

SQL A partial index for pending orders

Press Run (or Ctrl + Enter) to see the result here.

Compare the page counts: the partial index adds only a few dozen pages, while the full index on (status, ordered_on) adds several hundred. The first plan uses idx_orders_pending (even as a covering index), and the second can't, because shipped rows were never put in it.

The planner uses a partial index only when it can prove from your query that every row you want is in the index. In practice, repeat the index's condition in your query, written the same way. SQLite is fairly strict: WHERE status = 'pending' matches the index condition status = 'pending', but a condition that is merely logically equivalent in a roundabout way may not be recognized.

Typical uses:

  • Queue-like tables: index only unprocessed jobs, open tickets, unpaid invoices.
  • Soft deletes: WHERE deleted_at IS NULL, because live rows are what you query.
  • Sparse columns: WHERE coupon_code IS NOT NULL when most rows have no coupon.

Partial unique indexes: uniqueness with conditions

A partial index can also be UNIQUE, which enforces rules that a plain UNIQUE constraint can't express. "Each customer can have many subscriptions over time, but only one active subscription" is a perfect example. The last insert fails on purpose:

SQL Only one active subscription per customer

Press Run (or Ctrl + Enter) to see the result here.

The run stops with Error: UNIQUE constraint failed: subscriptions.customer_id. To switch plans, the application cancels the old subscription and inserts the new one in the same transaction. The same pattern gives you "one default address per user", "one primary email", or "unique usernames among non-deleted accounts".

Expression indexes: index a computed value

Writing Fast Queries showed that wrapping a column in a function, like lower(email), hides it from an ordinary index. When you really need that function, index the expression itself:

SQL An index on lower(email)

Press Run (or Ctrl + Enter) to see the result here.

The first plan shows SEARCH users USING INDEX users_email_lower (<expr>=?); <expr> is how SQLite labels an expression column. The plain email = ... query can't use it and scans. Because the index is UNIQUE, it also stops [email protected] from registering a second account: Error: UNIQUE constraint failed: index 'users_email_lower'.

For case-insensitive text specifically, SQLite has a second option: a collation. CREATE INDEX ... ON users (email COLLATE NOCASE) serves queries written as WHERE email = ? COLLATE NOCASE and also lets LIKE 'abc%' prefix searches use the index. Choose one style and use it consistently.

Indexing JSON fields

Expression indexes shine with JSON. If you store flexible attributes in a JSON column but filter by one field often, index that field:

SQL Indexing a field inside JSON

Press Run (or Ctrl + Enter) to see the result here.

The first plan searches the index; the second scans the whole table even though json_extract(payload, '$.user_id') returns the same values. SQLite matches index expressions by their form, not their meaning, so pick one spelling and use it everywhere (a view or a generated column helps). In fact, a generated column is a tidy alternative: user_id INTEGER GENERATED ALWAYS AS (payload ->> 'user_id') VIRTUAL plus an ordinary index on user_id gives queries a normal column name to filter on. See Constraints.

Expression indexes must use deterministic functions: the same input must always give the same output. lower(), substr(), json_extract() and ->> qualify; random() and date('now') don't.

Covering indexes and column order

A covering index holds every column a query touches, so SQLite never visits the table. For a multi-column index, the order of columns decides which queries it can serve. Think of a phone book sorted by last name, then first name: easy to find all "Garcia" entries, easy to find "Garcia, Maya", but useless for finding everyone named "Maya".

The rules for an index on (a, b, c):

  1. Leftmost prefix. It helps conditions on a, on a and b, or on a, b and c. A condition on b alone can't use it normally.
  2. Equality first, range last. The index can use equality on leading columns plus one range: a = ? AND b > ? is great; with a > ? AND b = ? only the range on a narrows the search.
  3. Sorting comes free when the order matches. WHERE a = ? ORDER BY b reads the index in order with no extra sort.
  4. Extra trailing columns make it covering. Columns that are only selected (not filtered) go at the end.
SQL How column order changes the plan

Press Run (or Ctrl + Enter) to see the result here.

  1. SEARCH ... USING COVERING INDEX ... (customer_id=? AND ordered_on>?): both columns narrow the search, and total rides along.
  2. SEARCH ... (customer_id=?) with no temp B-tree: the matching entries are already ordered by date, so SQLite reads them backwards.
  3. Without customer_id in the condition, SQLite can't search the index. It scans it instead (SCAN orders USING COVERING INDEX), which is cheaper than scanning the table because the index is narrower, but it's still a full pass.
  4. SEARCH orders USING INDEX (not covering): status isn't in the index, so each match costs a trip to the table.

Other databases make covering explicit with INCLUDE, which stores extra columns in the index leaf entries without making them part of the sort key:

SQL
-- PostgreSQL 11+ and SQL Server
CREATE INDEX idx_orders_cust_date
ON orders (customer_id, ordered_on)
INCLUDE (total, status);

SQLite has no INCLUDE, so you simply append the columns to the key, as in the example. (Covering works similarly in MySQL's InnoDB, where every secondary index also contains the primary key.)

What indexes cost

Every index is a second copy of some of your data, kept sorted. Each INSERT has to add an entry to every index, each DELETE removes one, and each UPDATE of an indexed column moves one. Indexes also take disk space and memory. Here's the write cost, measured:

SQL Measuring the write cost of indexes

Press Run (or Ctrl + Enter) to see the result here.

On most devices the table with five indexes takes many times longer to fill, because each row is written six times (once in the table, once per index), and indexes on random values like note and total scatter writes all over their B-trees. Numbers vary, but the shape doesn't.

So index deliberately:

  • Index for real queries. Start from the queries that run often or slowly, not from a list of columns.
  • Merge overlapping indexes. An index on (customer_id, ordered_on) makes a separate index on (customer_id) redundant, because its leftmost prefix does the same job.
  • Remove unused indexes. PostgreSQL's pg_stat_user_indexes and SQL Server's sys.dm_db_index_usage_stats show how often each index is used. In SQLite, check the plans of your important queries.
  • For big one-time loads, create indexes afterwards. Building an index once over sorted data is faster than maintaining it row by row.

ANALYZE: give the planner statistics

When several indexes could serve a query, the planner estimates how many rows each would return and picks the cheapest. Without statistics it has to guess. ANALYZE scans your tables and indexes and stores summaries in sqlite_stat1. With them, SQLite can also do a skip-scan: use an index even when its first column isn't constrained, if that column has only a few distinct values:

SQL ANALYZE and skip-scan

Press Run (or Ctrl + Enter) to see the result here.

Before ANALYZE, the plan is SCAN orders USING COVERING INDEX idx_orders_status_date: SQLite reads all 50,000 index entries, because the index is narrower than the table, but it can't jump to the right date. The stat value 50000 16667 33 for idx_orders_status_date then reads: 50,000 rows; about 16,667 rows per distinct status; about 33 per distinct (status, ordered_on) pair. Knowing there are only three statuses, SQLite now answers the date query with SEARCH orders USING COVERING INDEX idx_orders_status_date (ANY(status) AND ordered_on=?): it searches the index once for each of the three status values instead of reading every entry.

Run ANALYZE after loading data or after big changes in its distribution. SQLite's documentation recommends PRAGMA optimize; before closing long-lived connections, which runs ANALYZE only where it seems needed. PostgreSQL's autovacuum daemon analyzes tables automatically, and MySQL updates InnoDB statistics automatically too; you can run ANALYZE TABLE orders; by hand.

Inspecting indexes

SQLite's pragmas tell you what indexes exist and what's in them, which is handy when you inherit a database:

SQL
PRAGMA index_list('orders');                -- name, unique, origin, partial
PRAGMA index_xinfo('idx_orders_pending');   -- columns, sort order, collation
SELECT name, sql FROM sqlite_schema WHERE type = 'index';

In index_list, origin is c for indexes made with CREATE INDEX, u for those created by a UNIQUE constraint, and pk for a primary key; partial is 1 for partial indexes.

Index features across databases

Feature SQLite PostgreSQL MySQL 8 SQL Server
Partial index WHERE clause WHERE clause No Filtered index (WHERE)
Expression index Yes Yes Functional key parts ((lower(email))) (8.0.13+) Index a computed column
INCLUDE columns No (append to key) Yes (11+) No Yes
Descending keys Yes Yes Yes (8.0+) Yes
Other index types (R-tree, FTS modules) GIN, GiST, BRIN, hash, SP-GiST Full-text, spatial Columnstore, full-text, spatial
Collect statistics ANALYZE ANALYZE (autovacuum) ANALYZE TABLE (automatic) UPDATE STATISTICS (automatic)

Two PostgreSQL types deserve a mention: GIN indexes make jsonb containment, array and full-text searches fast, and BRIN indexes are tiny summaries for huge tables whose rows are naturally ordered, such as append-only logs. You'll meet them in PostgreSQL Essentials.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Only 2% of tickets rows have status = 'open', and the support screen always lists open tickets by created_at. Which index fits best?

    A partial index stores only the 2% of open tickets, already ordered by created_at, so it is small and serves both the filter and the sort.
  2. 2. There's an index on lower(email). Which query can use it?

    An expression index only matches queries that use the same expression. The plain column, a different function and a leading wildcard can't use it.
  3. 3. An index on (customer_id, ordered_on) exists. Which query can NOT search it efficiently without statistics?

    The leftmost column, customer_id, is missing from the condition, so the index can't narrow the search. (After ANALYZE, SQLite may skip-scan if customer_id has few distinct values.)
  4. 4. How do you allow many cancelled subscriptions but only one active subscription per customer?

    A partial unique index applies uniqueness only to active rows. UNIQUE (customer_id, status) would also forbid two cancelled subscriptions.
  5. 5. A table gets 5,000 inserts per second and has 9 indexes, 4 of which no query uses. What should you do first?

    Each index must be updated on every insert, so unused indexes cost write time and space while giving nothing back.

Summary

  • Partial indexes (CREATE INDEX ... WHERE ...) index only the rows you query; repeat the condition in your queries so the planner can use them.
  • Partial unique indexes enforce conditional rules such as "one active subscription per customer".
  • Expression indexes index computed values like lower(email) or payload ->> 'user_id'; queries must use the identical expression.
  • In a multi-column index, put equality columns first, then one range or sort column, then extra columns to make it covering.
  • Every index slows down writes and uses space: index for real queries, merge overlaps and drop unused ones.
  • ANALYZE gives the planner statistics, enabling better index choices and skip-scans; use PRAGMA optimize in long-running SQLite apps.

Next: Tuning the Database Server.