Experiments and implementations · Intermediate

Comparing column order in composite indexes

Keep query semantics fixed while comparing composite-index order for equality, range filtering and ordering.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Vary only index column order
  2. Compare plans while checking identical results
  3. Reason from lexicographic key order
  4. Test the other queries before choosing
  5. Ask Logeia about this articleCheck your understanding

Vary only index column order

Use Python’s sqlite3 module to create ten thousand events in an in-memory database. There are one hundred tenant values and an increasing integer creation field. The query selects tenant seven with created at least eight thousand and orders by created. Separate databases receive either a tenant-first or a created-first index. Creating both indexes together would test the optimizer’s choice between alternatives, so the controlled comparison instead changes only the available composite order in each database.

Compare plans while checking identical results

Run the code and verify that both designs return the same twenty rows. Read EXPLAIN QUERY PLAN for the access conditions and possible covering-index use rather than only the selected index name. In particular, examine which equality and range constraints narrow the search. ANALYZE supplies statistics for this toy dataset, but plan wording and choices can vary with SQLite versions. The lab asserts result correctness without treating a particular diagnostic sentence as the only valid optimizer behavior.

python
import sqlite3
for name, columns in [("tenant_time", "tenant, created"),
                      ("time_tenant", "created, tenant")]:
    db = sqlite3.connect(":memory:")
    db.execute("create table events(tenant integer, created integer)")
    db.executemany("insert into events values (?,?)",
                   [(i % 100, i) for i in range(10000)])
    db.execute(f"create index {name} on events({columns})")
    db.execute("analyze")
    sql = "select tenant, created from events where tenant=? and created>=? order by created"
    rows = db.execute(sql, (7, 8000)).fetchall()
    plan = db.execute("explain query plan " + sql, (7, 8000)).fetchall()
    print(name, "rows", len(rows), "plan", plan)
    assert rows == [(7, i) for i in range(8007, 10000, 100)]
    db.close()

Reason from lexicographic key order

With tenant first, equality on tenant seven identifies a contiguous key region, and the created range can narrow that region further. With created first, the suffix may include many tenants that must still be checked. This does not mean trailing columns are universally unusable: skip-scan and other optimizer choices can depend on statistics and version. The goal is to explain how lexicographic B-tree ordering changes candidate ranges, not to memorize a rule that ignores implementation features.

Test the other queries before choosing

A global recent-events query without a tenant predicate may benefit from the created-first design. Indexes also add write and storage costs, so adding every permutation is not a sensible default. SQLite’s plan is not PostgreSQL’s plan; repeat the reasoning on the actual engine and workload. If measuring runtime, control data distribution and cache conditions and record the plan separately from elapsed time. Timings from this small in-memory dataset do not establish a production speedup percentage.

The selected columns matter as well as the predicates. This query requests only tenant and created, allowing a covering index to supply the needed values in suitable plans. Adding a large payload column can introduce table lookups even when the WHERE clause stays unchanged. Record the complete SELECT list when comparing plans rather than assuming that every query with the same filters has the same access costs.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 12

Choose a learning path