From harness-claude
Explains composite indexes for accelerating multi-column SQL queries with WHERE and ORDER BY clauses. Covers leftmost prefix rule and ESR (Equality, Sort, Range) ordering strategy.
npx claudepluginhub intense-visions/harness-engineering --plugin harness-claudeThis skill uses the workspace's default tool permissions.
> Multi-column indexes that accelerate queries filtering on column combinations, governed by the leftmost prefix rule and the ESR (Equality, Sort, Range) column ordering strategy.
Guides B-tree index mechanics, composite column ordering, partial/covering indexes, GIN for JSONB/full-text search, GiST for geometry/ranges, and maintenance. Use for sequential scans or slow multi-column filters in EXPLAIN.
Analyzes PostgreSQL and MySQL index usage to detect missing indexes causing sequential scans, unused indexes, and recommend optimal configurations for query performance.
Designs and implements database indexing strategies for PostgreSQL and MySQL. Covers index creation, types like B-tree, Hash, GiST, BRIN, composites, partials, maintenance, and query performance optimization.
Share bugs, ideas, or general feedback.
Multi-column indexes that accelerate queries filtering on column combinations, governed by the leftmost prefix rule and the ESR (Equality, Sort, Range) column ordering strategy.
A composite index stores entries sorted by multiple columns in declaration order. Think of it like a phone book sorted by (last_name, first_name) -- you can look up by last name alone, or by last name and first name together, but not by first name alone.
Syntax:
CREATE INDEX idx_orders_status_date ON orders (status, created_at);
The leftmost prefix rule. An index on (a, b, c) supports queries on:
But NOT:
The ESR rule for column ordering -- Equality, Sort, Range:
=<, >, BETWEENThis order maximizes the portion of the index the planner can use in a single scan.
-- ESR-optimized for: WHERE tenant_id = ? AND status = ? ORDER BY created_at
CREATE INDEX idx_orders_esr ON orders (tenant_id, status, created_at);
Multi-tenant SaaS orders table with 50M rows:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
tenant_id INT NOT NULL,
status TEXT NOT NULL,
total NUMERIC(10,2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_orders_composite ON orders (tenant_id, status, created_at);
Query 1 -- full index usage (all three columns):
EXPLAIN ANALYZE
SELECT id, total FROM orders
WHERE tenant_id = 5 AND status = 'active' AND created_at > '2024-01-01';
Index Scan using idx_orders_composite on orders
(cost=0.56..1245.32 rows=4521 width=14)
(actual time=0.028..3.412 rows=4380 loops=1)
Index Cond: (tenant_id = 5 AND status = 'active' AND created_at > '2024-01-01')
Execution Time: 3.891 ms
Query 2 -- partial index usage (first column only):
EXPLAIN ANALYZE
SELECT id, total FROM orders
WHERE tenant_id = 5 AND created_at > '2024-01-01';
Index Scan using idx_orders_composite on orders
(cost=0.56..8923.12 rows=45210 width=14)
(actual time=0.031..89.452 rows=43800 loops=1)
Index Cond: (tenant_id = 5)
Filter: (created_at > '2024-01-01')
Rows Removed by Filter: 6200
The index is used for tenant_id but created_at is applied as a filter after fetching rows, because status (the second column) was skipped.
Query 3 -- index not usable (skips leading column):
EXPLAIN ANALYZE
SELECT id, total FROM orders
WHERE status = 'active';
Seq Scan on orders
(cost=0.00..1234567.00 rows=5000000 width=14)
(actual time=0.021..4521.330 rows=4980000 loops=1)
Filter: (status = 'active')
The composite index cannot be used because the query skips tenant_id (the leftmost column).
Separate single-column indexes instead of one composite. The planner can combine single-column indexes via Bitmap AND, but this is significantly slower than a single composite index scan. One well-designed composite outperforms multiple singles.
Wrong column order -- range before equality.
BAD: CREATE INDEX ON orders (created_at, tenant_id) for WHERE tenant_id = ? AND created_at > ?
GOOD: CREATE INDEX ON orders (tenant_id, created_at) -- equality first, range second.
Too many columns. Each additional column increases index size and write overhead with diminishing returns. Most composites should have 2-4 columns.
Duplicating the leading column. If you have an index on (tenant_id, status), a separate index on (tenant_id) alone is redundant -- the composite already supports queries on just tenant_id.
PostgreSQL can use a composite index on (a, b) for WHERE a = 1 ORDER BY b without a separate sort step -- the index already produces results in the correct order.
Monitoring composite index usage:
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_orders_composite';
Index-only scans with composites. If the composite index contains all columns in the SELECT list, PostgreSQL performs an Index Only Scan. See db-covering-index for details.
Index skip scan (PostgreSQL 16+). In some cases, PostgreSQL can skip over distinct values of the leading column, allowing the index on (a, b) to be used for queries on just (b). This is not yet as mature as Oracle's skip scan but is improving with each release.
Mixed ASC/DESC ordering. For queries with ORDER BY a ASC, b DESC, create:
CREATE INDEX idx_mixed ON orders (a ASC, b DESC);
Without matching sort directions, PostgreSQL must perform a separate sort step.
Index size growth. Each additional column increases the index size roughly in proportion to the column's average width. Monitor with pg_relation_size().
MySQL InnoDB applies the same leftmost prefix rule for composite indexes. Key differences:
E-commerce dashboard with 100M orders. The dashboard query was WHERE merchant_id = ? AND status = ? AND created_at BETWEEN ? AND ?. The system had three separate single-column indexes on merchant_id, status, and created_at. The planner used Bitmap AND to combine them, taking 800ms per query.
Replacing all three with one composite (merchant_id, status, created_at) following ESR ordering: