SQL 74 🛢️ Composite, Unique, and Partial Indexes
A single-column index answers one question efficiently: which rows have this value? Real queries rarely ask only one question. They filter by customer and sort by date, look up a user by email and require that email to be unique, or query a queue for the oldest pending job while ignoring the millions of completed ones. These patterns call for index variants that a plain single-column B-tree cannot serve well. Composite indexes combine multiple columns into a single ordered structure. Unique indexes enforce correctness while accelerating lookup. Partial indexes index only the subset of rows that queries actually touch. Together, these three variants cover the majority of index design decisions a developer makes in practice, and getting them right is the difference between a schema that scales and one that stumbles under production load.
Key point: A composite index is not several indexes in one. It is a single structure whose usefulness depends entirely on the order of its columns, and the leftmost column rule determines which queries it can serve. A partial index is not a smaller version of a full index. It is a different index that exists only for the rows a specific query pattern cares about.
Why these index variants exist
The multi-column filter problem. A query that filters on two columns, such as WHERE customer_id = 42 AND status = 'pending', can use a single-column index on one of them, but then the other column’s filter is applied as a residual check on the fetched rows. If the first column is not selective enough, the database fetches many rows only to discard most of them. A composite index on both columns lets the database narrow to the exact matching rows in a single descent.
The filter-and-sort problem. A query that filters on one column and sorts by another, such as WHERE customer_id = 42 ORDER BY order_date DESC, is common in paginated applications. A single-column index on customer_id finds the matching rows but returns them in arbitrary order, forcing a sort. A composite index on (customer_id, order_date) stores the entries already sorted by date within each customer, eliminating the sort entirely.
The correctness problem. Some columns must be unique, and the database must enforce that uniqueness against concurrent inserts. This is not a query optimization; it is a correctness guarantee. A unique index is the mechanism that enforces it, and the same index accelerates lookups on the unique column. Uniqueness cannot be enforced by application code alone under concurrency, because two transactions can both check for a value’s absence and both proceed to insert it.
The wasted-index problem. A large table often has a hot subset that queries constantly touch and a cold majority that queries almost never touch. An orders table might have five million delivered orders and fifty thousand pending ones, and the application queries only the pending ones. A full index on that table indexes all five million and fifty thousand rows, wasting storage and slowing writes for rows that no query will ever look up by that column. A partial index on the pending subset is a fraction of the size and far more selective.
The write-cost problem. Every index taxes every write. A schema with a dozen full indexes imposes a dozen index updates per insert. Partial indexes reduce this tax by indexing only the rows that matter, so an insert of a cold row touches fewer structures. Composite indexes reduce the total index count by replacing several single-column indexes with one that serves multiple queries, again reducing write amplification.
The space problem. Indexes compete for the buffer pool. A schema whose indexes exceed available memory forces disk reads on every lookup. Partial indexes and well-designed composite indexes keep the working set smaller, so more of the index stays in memory, and lookups stay fast as the table grows.
a. Composite indexes and the leftmost prefix rule
A composite index is a single B-tree whose key is a tuple of columns. The entries are sorted by the first column, then by the second within equal first-column values, then by the third, and so on. This ordering is what makes the index useful for some queries and useless for others.
The structure of a composite key
Consider an index on (last_name, first_name, hire_date). Entries are sorted by last_name first. Within each last name, entries are sorted by first_name. Within each first name, entries are sorted by hire_date. This is exactly lexicographic ordering, the same way a phone book is sorted.
-- Conceptual composite index entries on (last_name, first_name, hire_date):
-- ('Adams', 'Alice', '2020-01-15')
-- ('Adams', 'Alice', '2023-07-01')
-- ('Adams', 'Bob', '2019-03-22')
-- ('Baker', 'Carol', '2021-11-30')
-- ('Baker', 'Carol', '2024-02-14')
-- ('Clark', 'Dave', '2018-06-05')The leftmost prefix rule follows directly from this ordering. The index can be used to answer a query if the query’s equality and range conditions cover a prefix of the index columns, starting from the left. Any gap breaks the usefulness of the rest.
Which queries use the index
A query that filters on last_name alone uses the index, because last_name is the leftmost column. A query that filters on last_name and first_name uses the index and descends further. A query that filters on all three columns uses the index fully. A query that filters on first_name alone cannot use the index, because the entries are not sorted by first name globally; they are only sorted by first name within each last name.
-- Uses the index (leftmost prefix = last_name):
SELECT * FROM employees WHERE last_name = 'Adams';
-- Uses the index (prefix = last_name, first_name):
SELECT * FROM employees
WHERE last_name = 'Adams' AND first_name = 'Alice';
-- Uses the index (full key):
SELECT * FROM employees
WHERE last_name = 'Adams' AND first_name = 'Alice'
AND hire_date = '2020-01-15';
-- Cannot use the index for a seek:
SELECT * FROM employees WHERE first_name = 'Alice';
-- The engine may do an index-only scan or a full scan of the index,
-- but it cannot descend directly to 'Alice' entries.Equality before range
Within a composite index, the order of equality and range conditions matters. All equality columns should come before any range column, because once the index is walked as a range, subsequent columns within that range are sorted only locally, not globally. A query that mixes them out of order loses the ability to use the later columns for seeking.
-- Index on (customer_id, order_date, status)
-- Good order for this query:
SELECT * FROM orders
WHERE customer_id = 42 -- equality
AND order_date >= '2026-01-01' -- range
AND status = 'pending'; -- equality after range
-- The engine uses customer_id to narrow, then walks order_date range,
-- then filters status within that range.
-- status is not used for the seek, only as a filter on the walked range.
-- Better index for this query would put status before order_date:
CREATE INDEX idx_better ON orders(customer_id, status, order_date);
-- Now customer_id and status are both equality seeks,
-- and order_date is the range. Maximum efficiency.Composite indexes replace multiple single-column indexes
When two single-column indexes exist on customer_id and order_date separately, the database can use at most one of them per query. A composite index on (customer_id, order_date) can serve both the customer-only query, the customer-and-date-range query, and the customer-and-date-sorted query. This reduces the total index count on the table, which reduces write amplification.
-- Two separate indexes: the database must choose one.
CREATE INDEX idx_a ON orders(customer_id);
CREATE INDEX idx_b ON orders(order_date);
-- One composite index: serves both query patterns.
CREATE INDEX idx_ab ON orders(customer_id, order_date);
-- Query on customer_id alone: uses idx_ab (leftmost prefix).
-- Query on customer_id + order_date: uses idx_ab fully.
-- Query on order_date alone: does NOT use idx_ab (missing leftmost).
-- The single-column index on order_date is still needed for that case.The general rule is: a composite index can replace a single-column index on its leftmost column, but not on its other columns. This is why a schema with composite indexes often still retains a few single-column indexes for queries that filter on non-leading columns.
Column order strategy
The optimal column order for a composite index is determined by the workload, not by the schema. The general heuristic is: put equality columns first, then the column used for range or sort, and consider adding covering columns last. When two columns are always used together in equality predicates, their relative order matters less; when one is equality and one is range, the equality column must come first.
-- Common query: WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC
-- Optimal composite index:
CREATE INDEX idx_tenant_status_date
ON orders(tenant_id, status, created_at DESC);
-- tenant_id and status are equality; created_at is range and sort.
-- The index serves the filter and eliminates the sort.Index-only scans with composite indexes
When a query needs only the columns in the composite index, the database can answer it without touching the heap. This is an index-only scan, and it is the fastest access path available. Composite indexes make it easy to build covering indexes by including all columns a hot query needs.
-- Query only needs three columns:
SELECT customer_id, order_date, total FROM orders WHERE customer_id = 42;
-- Composite index that covers the query:
CREATE INDEX idx_covering ON orders(customer_id, order_date, total);
-- PostgreSQL can answer entirely from the index:
-- Index Only Scan using idx_covering on orders
-- Index Cond: (customer_id = 42)
-- Heap Fetches: 0b. Unique indexes
A unique index enforces that no two rows share a value in the indexed columns. It is both a correctness constraint and a query accelerator. Every primary key in every relational database is backed by a unique index, and unique constraints on other columns are the standard way to enforce natural keys.
Uniqueness as a correctness guarantee
The database enforces uniqueness at write time, not at query time. When an INSERT or UPDATE would create a duplicate, the database raises an error and the transaction is rolled back or the statement fails. This guarantee holds under concurrency: two transactions inserting the same value concurrently will serialize on the index, and one will succeed while the other fails.
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Transaction A:
INSERT INTO users (email) VALUES ('alice@example.com');
-- Succeeds.
-- Transaction B (concurrent):
INSERT INTO users (email) VALUES ('alice@example.com');
-- ERROR: duplicate key value violates unique constraint
-- The index prevented the duplicate at the storage layer.The application cannot reliably enforce uniqueness on its own. A check-then-insert pattern has a race window: both transactions can check, both can see the value absent, and both can insert. Only the database’s unique index closes that window.
Unique indexes and multi-column uniqueness
A unique composite index enforces that the combination of values is unique, not each column individually. This is how composite natural keys are enforced, such as a membership table where a user can belong to a group at most once.
-- Each user can belong to each group at most once,
-- but a user can belong to many groups and a group can have many users.
CREATE UNIQUE INDEX idx_membership
ON memberships(user_id, group_id);
-- This insert succeeds:
INSERT INTO memberships (user_id, group_id) VALUES (42, 7);
-- This insert fails:
INSERT INTO memberships (user_id, group_id) VALUES (42, 7);
-- The pair must be unique, not each column.Unique indexes and NULL handling
Most databases treat NULL as distinct from every other value, including other NULLs, for uniqueness purposes. A unique index on a column with multiple NULL values typically allows all of them, because the database cannot determine that two unknown values are equal. This behavior varies by database and by standard, so developers relying on unique indexes over nullable columns must verify the specific behavior.
CREATE UNIQUE INDEX idx_users_phone ON users(phone);
INSERT INTO users (phone) VALUES (NULL); -- OK
INSERT INTO users (phone) VALUES (NULL); -- OK in most databases
-- Both NULLs coexist because NULL is not equal to NULL.
-- PostgreSQL 15 and later offer NULLS NOT DISTINCT:
CREATE UNIQUE INDEX idx_users_phone ON users(phone) NULLS NOT DISTINCT;
-- Now only one NULL is allowed.Unique indexes versus unique constraints
In PostgreSQL and most databases, CREATE UNIQUE INDEX and ALTER TABLE ... ADD CONSTRAINT ... UNIQUE are functionally equivalent for query planning. The difference is primarily semantic: a constraint is part of the schema’s declared integrity rules, while an index is a physical structure. Both create the same underlying index. Some databases, such as MySQL, require a unique constraint to be added via ALTER TABLE, which internally creates a unique index.
-- These two are equivalent in effect:
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
CREATE UNIQUE INDEX uq_email ON users(email);
-- The constraint version appears in information_schema.table_constraints.
-- The index version appears only in the index catalog.Partial unique indexes
A unique index can be partial, enforcing uniqueness only on rows matching a predicate. This is useful for soft-delete patterns, where deleted rows should not count against uniqueness, or for tables where only a subset of rows must have a unique value.
-- Enforce unique email only for active users.
CREATE UNIQUE INDEX idx_users_email_active
ON users(email)
WHERE deleted_at IS NULL;
-- Two users with the same email can exist if one is soft-deleted.
-- Active users still have unique emails.Unique indexes and performance
A unique index accelerates lookups the same way a non-unique B-tree does, because the structure is identical. The only difference is the additional check on insert that the key is not already present. This check is nearly free because the insert path already descends to the target leaf, where the duplicate would be found. A unique index is therefore both a correctness mechanism and a fast lookup structure with negligible extra cost.
c. Partial indexes
A partial index, also called a filtered index in SQL Server, indexes only the rows that satisfy a predicate. The predicate is fixed at index creation time and cannot be changed without dropping and recreating the index. The database uses the index only when a query’s WHERE clause logically implies the index’s predicate.
The predicate-matching rule
The optimizer will only use a partial index if it can prove that the query’s filter is a superset of, or exactly matches, the index’s predicate. In practice, the query must include the same predicate, often verbatim, for the index to be considered. This is not a limitation of the implementation; it is a logical requirement. If the query might return rows outside the index’s predicate, the index cannot possibly contain all the answers.
CREATE INDEX idx_pending_orders
ON orders(created_at)
WHERE status = 'pending';
-- The index is used because the query matches the predicate:
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2026-01-01';
-- Index Cond: (created_at > '2026-01-01')
-- Filter: (status = 'pending')
-- The index is NOT used because the predicate is missing:
SELECT * FROM orders WHERE created_at > '2026-01-01';
-- The query can return non-pending rows, which the index does not contain.
-- The index is NOT used because the query is broader:
SELECT * FROM orders WHERE status IN ('pending', 'shipped');
-- 'shipped' rows are not in the index.Size and maintenance advantages
A partial index is smaller than a full index on the same columns, often by orders of magnitude. A table with ten million rows where only one percent are pending produces a partial index of one hundred thousand entries, versus ten million entries for a full index. The smaller index fits in memory more easily, is faster to scan, and is cheaper to maintain on inserts that fall outside the predicate.
-- Full index: 10,000,000 entries.
CREATE INDEX idx_full ON orders(created_at);
-- Partial index: 100,000 entries (1% of rows).
CREATE INDEX idx_partial ON orders(created_at) WHERE status = 'pending';
-- Insert of a non-pending order:
-- The full index must be updated (write cost).
-- The partial index is not touched (no write cost).
-- The partial index is 100× smaller and 100× cheaper for these inserts.Partial indexes for queues and hot subsets
The canonical use case is a job queue or work queue where a small number of rows are actively being processed and the rest are historical. The queue worker queries only the active rows, and a partial index makes that query fast while ignoring the historical rows entirely.
-- The queue worker runs this query repeatedly:
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- A partial index on the pending subset makes this fast:
CREATE INDEX idx_jobs_pending
ON jobs(created_at)
WHERE status = 'pending';
-- The index contains only pending jobs, likely a few thousand
-- out of millions. The worker's query is a quick index scan
-- on a small, hot, memory-resident structure.Partial indexes for soft deletes
A soft-delete pattern uses a deleted_at timestamp column, where non-null means the row is deleted. Queries almost always filter WHERE deleted_at IS NULL. A partial index on the active rows accelerates those queries and excludes the deleted rows from the index entirely.
-- Application queries always filter active rows:
SELECT * FROM documents WHERE deleted_at IS NULL AND owner_id = 42;
-- Partial index on active rows only:
CREATE INDEX idx_documents_owner_active
ON documents(owner_id)
WHERE deleted_at IS NULL;Partial unique indexes for conditional uniqueness
A partial unique index enforces uniqueness only among rows matching the predicate. This is how conditional uniqueness rules are expressed, such as “only one primary email per user” or “only one active subscription per customer.”
-- Only one primary email per user.
CREATE UNIQUE INDEX idx_one_primary_email
ON emails(user_id)
WHERE is_primary = TRUE;
-- Only one active subscription per customer.
CREATE UNIQUE INDEX idx_one_active_sub
ON subscriptions(customer_id)
WHERE status = 'active';Indexes with expressions
A partial index can combine a predicate with expressions, producing an index that covers a filtered set of computed values. This is how case-insensitive unique constraints on a subset of rows are built.
-- Case-insensitive unique email for active users only.
CREATE UNIQUE INDEX idx_users_email_active_ci
ON users(LOWER(email))
WHERE deleted_at IS NULL;
-- Query must use the same expression and predicate:
SELECT * FROM users
WHERE LOWER(email) = 'alice@example.com'
AND deleted_at IS NULL;When partial indexes are not used
A partial index is invisible to the optimizer unless the query matches the predicate exactly. This means that applications with dynamic filters, where the predicate varies at runtime, cannot easily use a partial index. The predicate must be a fixed part of the query, either as a literal in the SQL or as a parameter the optimizer can match against the index definition. Applications that generate queries dynamically must ensure that the generated query includes the exact predicate the index was built for.
Complete Example Session
-- ============================================
-- PART 1: THE SCENARIO
-- ============================================
-- A multi-tenant SaaS application with an orders table.
CREATE TABLE orders (
order_id BIGSERIAL PRIMARY KEY,
tenant_id INT NOT NULL,
customer_id INT NOT NULL,
status VARCHAR(20) NOT NULL,
total DECIMAL(10,2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ
);
-- Common query patterns:
-- 1. Orders for a customer, newest first.
-- 2. Pending orders per tenant, oldest first (queue worker).
-- 3. Active orders only (soft delete filter).
-- 4. Lookup by order reference (external ID).
-- 5. Reports that sum total by tenant over a date range.
-- ============================================
-- PART 2: THE NAIVE APPROACH — SEPARATE INDEXES
-- ============================================
-- Start with single-column indexes on each column.
CREATE INDEX idx_orders_tenant ON orders(tenant_id);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at);
-- Problem 1: Query (1) filters customer and sorts by date.
-- The engine uses idx_orders_customer, finds matching rows,
-- then sorts them. The sort is expensive.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
-- Sort (cost=...) (actual time=...)
-- Sort Key: created_at DESC
-- -> Index Scan using idx_orders_customer on orders
-- The sort step processes every matching row before returning 20.
-- Problem 2: Query (2) filters tenant and status.
-- The engine picks one index; the other predicate is a filter.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE tenant_id = 7 AND status = 'pending'
ORDER BY created_at
LIMIT 10;
-- The engine uses idx_orders_tenant, fetches all tenant 7 rows,
-- then filters status = 'pending' and sorts by created_at.
-- If tenant 7 has 500,000 rows and only 100 are pending,
-- 499,900 rows are fetched and discarded.
-- ============================================
-- PART 3: COMPOSITE INDEX FOR CUSTOMER + SORT
-- ============================================
-- Replace the single-column index on customer with a composite.
DROP INDEX idx_orders_customer;
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, created_at DESC);
-- Same query (1) now uses the composite index.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
-- Index Scan using idx_orders_customer_date on orders
-- Index Cond: (customer_id = 42)
-- The sort is gone. The index returns rows already in date order.
-- The LIMIT 20 stops the scan after 20 rows.
-- Time dropped from milliseconds to microseconds for a narrow slice.
-- ============================================
-- PART 4: COMPOSITE INDEX FOR TENANT + STATUS + SORT
-- ============================================
-- Replace the tenant and status indexes with one composite.
DROP INDEX idx_orders_tenant;
DROP INDEX idx_orders_status;
CREATE INDEX idx_orders_tenant_status_date
ON orders(tenant_id, status, created_at);
-- Query (2) now uses the composite index fully.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE tenant_id = 7 AND status = 'pending'
ORDER BY created_at
LIMIT 10;
-- Index Scan using idx_orders_tenant_status_date on orders
-- Index Cond: ((tenant_id = 7) AND (status = 'pending'))
-- The engine descends to the tenant 7 / pending range,
-- returns rows in created_at order, stops after 10.
-- No filter, no sort. Direct seek to the exact rows needed.
-- ============================================
-- PART 5: UNIQUE INDEX FOR EXTERNAL REFERENCE
-- ============================================
-- Add a natural key column that must be unique.
ALTER TABLE orders ADD COLUMN order_ref VARCHAR(40);
-- Enforce uniqueness and accelerate lookup.
CREATE UNIQUE INDEX idx_orders_ref ON orders(order_ref);
-- Application lookup by reference:
SELECT * FROM orders WHERE order_ref = 'ORD-2026-00042';
-- Single-row equality lookup via the unique index.
-- Uniqueness is enforced by the database, not the application.
-- ============================================
-- PART 6: PARTIAL INDEX FOR THE QUEUE WORKER
-- ============================================
-- Query (2) from the application filters pending orders only.
-- But the composite index from Part 4 indexes all statuses.
-- Most orders are 'delivered' or 'cancelled' and never queried
-- by the worker. A partial index can exclude them.
DROP INDEX idx_orders_tenant_status_date;
CREATE INDEX idx_orders_pending
ON orders(tenant_id, created_at)
WHERE status = 'pending';
-- The index only contains pending orders, a small fraction.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE tenant_id = 7 AND status = 'pending'
ORDER BY created_at
LIMIT 10;
-- Index Scan using idx_orders_pending on orders
-- Index Cond: (tenant_id = 7)
-- Filter: (status = 'pending')
-- The index is small, fits in memory, and the query is fast.
-- Inserts of non-pending orders do not touch this index at all.
-- ============================================
-- PART 7: PARTIAL INDEX FOR SOFT DELETES
-- ============================================
-- Query (3) always filters out deleted rows.
-- A partial index on active rows serves all such queries.
CREATE INDEX idx_orders_active_tenant_date
ON orders(tenant_id, created_at DESC)
WHERE deleted_at IS NULL;
-- Active-orders query:
SELECT * FROM orders
WHERE tenant_id = 7 AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 20;
-- Index Scan using idx_orders_active_tenant_date on orders
-- Index Cond: (tenant_id = 7)
-- The index excludes deleted rows entirely.
-- Deletes do not need to update this index (only the row's deleted_at
-- change triggers a removal from the index, but the index shrinks).
-- The index stays small even if the table grows with deleted rows.
-- ============================================
-- PART 8: PARTIAL UNIQUE INDEX FOR CONDITIONAL UNIQUENESS
-- ============================================
-- Business rule: a customer can have at most one active order per tenant.
CREATE UNIQUE INDEX idx_one_active_order_per_customer
ON orders(tenant_id, customer_id)
WHERE status = 'pending';
-- Insert of a second pending order for the same customer fails:
INSERT INTO orders (tenant_id, customer_id, status, total)
VALUES (7, 42, 'pending', 99.99);
-- ERROR: duplicate key value violates unique constraint
-- But the same customer can have many delivered orders:
INSERT INTO orders (tenant_id, customer_id, status, total)
VALUES (7, 42, 'delivered', 199.99);
-- Succeeds.
-- ============================================
-- PART 9: COVERING COMPOSITE INDEX
-- ============================================
-- Reports sum totals per tenant over a date range.
SELECT tenant_id, SUM(total) FROM orders
WHERE tenant_id = 7
AND created_at >= '2026-01-01'
AND created_at < '2026-02-01'
GROUP BY tenant_id;
-- A covering composite index makes this an index-only scan.
CREATE INDEX idx_orders_tenant_date_total
ON orders(tenant_id, created_at, total);
EXPLAIN ANALYZE
SELECT tenant_id, SUM(total) FROM orders
WHERE tenant_id = 7
AND created_at >= '2026-01-01'
AND created_at < '2026-02-01'
GROUP BY tenant_id;
-- Index Only Scan using idx_orders_tenant_date_total on orders
-- Index Cond: ((tenant_id = 7) AND (created_at >= ...) AND (created_at < ...))
-- Heap Fetches: 0
-- The heap is never touched. The index contains all needed columns.
-- ============================================
-- PART 10: AUDITING INDEXES
-- ============================================
-- After all the changes, inspect what indexes exist and their usage.
SELECT
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS size,
idx_scan AS scans
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;
-- Sample output:
-- index_name | size | scans
-- ------------------------------------+--------+--------
-- orders_pkey | 220 MB | 500000
-- idx_orders_ref | 180 MB | 120000
-- idx_orders_tenant_date_total | 240 MB | 8000
-- idx_orders_customer_date | 195 MB | 90000
-- idx_orders_pending | 4 MB | 2400000
-- idx_orders_active_tenant_date | 200 MB | 45000
-- idx_one_active_order_per_customer | 3 MB | 2000
-- Observations:
-- The primary key and unique index are heavily used.
-- The partial pending index is tiny but has the most scans.
-- The covering index is the largest but least scanned.
-- Each index earns its write cost with a specific query pattern.The ten parts walked through every major composite, unique, and partial index decision on a realistic multi-tenant orders schema. The naive approach with separate single-column indexes was replaced step by step by composite indexes that eliminated sorts, a unique index that enforced a natural key, partial indexes that shrank the working set to the hot subset, a partial unique index that enforced a conditional business rule, and a covering composite index that answered aggregate queries without touching the heap.
Quick Reference
Composite Index Leftmost Prefix Rule
| Query Filter | Uses Index (a, b, c) |
|---|---|
| a = ? | Yes |
| a = ? AND b = ? | Yes |
| a = ? AND b = ? AND c = ? | Yes |
| a = ? AND c = ? | Partial (a seek, c filter) |
| b = ? | No |
| c = ? | No |
| b = ? AND c = ? | No |
Index Type Comparison
| Property | Single-Column | Composite | Unique | Partial |
|---|---|---|---|---|
| Columns indexed | One | Two or more | One or more | Filtered subset |
| Leftmost prefix | N/A | Required | Required | Required |
| Enforces uniqueness | No | Optional | Yes | Optional (filtered) |
| Size | Baseline | Larger per entry | Same as composite | Smaller than full |
| Write cost | Baseline | Same | Same + uniqueness check | Reduced for excluded rows |
| Best for | Simple filters | Multi-column filters | Natural keys | Hot subsets, queues |
Column Order Strategy
| Scenario | Recommended Order |
|---|---|
| Two equality columns | Either order (lower cardinality first helps slightly) |
| One equality, one range | Equality first, range second |
| Equality, range, sort | Equality, range/sort combined |
| Filter and sort | Filter column first, sort column second |
| Covering query | Filter columns, then sort column, then covering columns |
Partial Index Predicate Matching
| Query WHERE Clause | Matches Index WHERE status = ‘pending’ |
|---|---|
| status = ‘pending’ | Yes |
| status = ‘pending’ AND created_at > ? | Yes |
| status = ‘pending’ OR status = ‘shipped’ | No |
| status IN (‘pending’, ‘shipped’) | No |
| created_at > ? | No |
| deleted_at IS NULL AND status = ‘pending’ | Yes (if predicate matches) |
Best Practices
✅ Do This:
-- Put equality columns first, range and sort columns after.
CREATE INDEX idx ON orders(tenant_id, status, created_at); -- ✅
-- Use composite indexes to serve filter-and-sort queries.
CREATE INDEX idx_cust_date ON orders(customer_id, created_at DESC); -- ✅
-- Use unique indexes to enforce natural keys.
CREATE UNIQUE INDEX idx_users_email ON users(email); -- ✅
-- Use partial indexes for hot subsets and queues.
CREATE INDEX idx_pending ON jobs(created_at) WHERE status = 'pending'; -- ✅
-- Use partial unique indexes for conditional business rules.
CREATE UNIQUE INDEX idx_one_primary
ON emails(user_id) WHERE is_primary = TRUE; -- ✅
-- Match the query predicate to the partial index predicate exactly.
SELECT * FROM jobs WHERE status = 'pending' AND created_at < NOW(); -- ✅
-- Use covering composite indexes for index-only scans.
CREATE INDEX idx_cover ON orders(customer_id) INCLUDE (total); -- ✅
-- Audit index usage and drop unused composites.
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes; -- ✅❌ Don’t Do This:
-- Don't create a composite index and expect non-leading columns to be usable.
CREATE INDEX idx ON orders(a, b, c);
SELECT * FROM orders WHERE b = 1; -- ❌ Cannot seek on b
-- Don't put range columns before equality columns.
CREATE INDEX idx ON orders(created_at, tenant_id); -- ❌ Poor for tenant filter
-- Don't rely on application code for uniqueness.
-- check-then-insert without a unique index has a race. -- ❌
-- Don't create partial indexes without matching query predicates.
CREATE INDEX idx ON jobs(created_at) WHERE status = 'pending';
SELECT * FROM jobs WHERE status = 'running'; -- ❌ Index not used
-- Don't index every column combination just in case.
CREATE INDEX idx1 ON t(a, b);
CREATE INDEX idx2 ON t(a, c);
CREATE INDEX idx3 ON t(a, b, c); -- ❌ Redundant overlap
-- Don't forget that unique indexes affect INSERT error handling.
-- Applications must catch duplicate key violations. -- ❌
-- Don't use partial indexes for predicates that vary at runtime.
-- The predicate must be a fixed literal or parameter. -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Composite index not used | Query missing leftmost column | Add index on non-leading column or reorder |
| Sort still happens | Sort column not contiguous after filter | Reorder composite columns |
| Partial index ignored | Query predicate does not match index predicate | Match predicate exactly in query |
| Duplicate rows despite unique index | NULLs treated as distinct | Use NULLS NOT DISTINCT or partial index |
| Unique index causes insert errors | Application does not handle duplicates | Catch and handle unique violation |
| Redundant overlapping indexes | Multiple composites with shared prefix | Drop indexes whose prefix is covered by another |
| Partial unique index too broad | Predicate allows too many rows | Tighten predicate to the true constraint |
| Covering index too large | Included columns bloat every entry | Include only columns needed by hot queries |
Real-World Examples
1. Customer Orders, Newest First
CREATE INDEX idx_cust_date ON orders(customer_id, created_at DESC);
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;2. Multi-Tenant Queue Worker
CREATE INDEX idx_pending ON jobs(tenant_id, created_at)
WHERE status = 'pending';
SELECT * FROM jobs WHERE tenant_id = 7 AND status = 'pending'
ORDER BY created_at LIMIT 10;3. Unique Username with Case Insensitivity
CREATE UNIQUE INDEX idx_username_ci ON users(LOWER(username));
-- Enforces uniqueness on case-folded values.4. Soft-Delete Active Records
CREATE INDEX idx_active_owner ON documents(owner_id)
WHERE deleted_at IS NULL;
SELECT * FROM documents WHERE owner_id = 42 AND deleted_at IS NULL;5. One Primary Email per User
CREATE UNIQUE INDEX idx_one_primary
ON emails(user_id) WHERE is_primary = TRUE;6. Composite Natural Key
CREATE UNIQUE INDEX idx_membership
ON memberships(user_id, group_id);
-- A user can belong to each group at most once.7. Covering Aggregate Query
CREATE INDEX idx_tenant_date_total
ON orders(tenant_id, created_at, total);
SELECT tenant_id, SUM(total) FROM orders
WHERE tenant_id = 7 AND created_at >= '2026-01-01'
GROUP BY tenant_id;8. Soft-Delete Unique Email
CREATE UNIQUE INDEX idx_active_email
ON users(email) WHERE deleted_at IS NULL;
-- A soft-deleted user's email can be reused.9. Partial Index for Failed Jobs
CREATE INDEX idx_failed ON jobs(failed_at)
WHERE status = 'failed';
SELECT * FROM jobs WHERE status = 'failed'
ORDER BY failed_at DESC LIMIT 100;10. Auditing Unused Indexes
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';
-- Candidates for removal.Visual
Composite Index Leftmost Prefix
┌────────────────────────────────────────────────────────────┐
│ COMPOSITE INDEX ON (last_name, first_name, hire_date) │
│ │
│ Sorted entries: │
│ Adams, Alice, 2020-01-15 │
│ Adams, Alice, 2023-07-01 │
│ Adams, Bob, 2019-03-22 │
│ Baker, Carol, 2021-11-30 │
│ Baker, Carol, 2024-02-14 │
│ Clark, Dave, 2018-06-05 │
│ │
│ QUERY USES INDEX? │
│ last_name = ? YES (leftmost) │
│ last_name + first_name YES (prefix) │
│ all three columns YES (full key) │
│ first_name only NO (gap before first) │
│ first_name + hire_date NO (gap before first) │
│ last_name + hire_date PARTIAL (seek then filter) │
│ │
└────────────────────────────────────────────────────────────┘Equality Before Range in Column Order
┌────────────────────────────────────────────────────────────┐
│ COLUMN ORDER: EQUALITY FIRST, RANGE SECOND │
│ │
│ Query: WHERE tenant = 7 AND status = 'pending' │
│ AND created_at > '2026-01-01' │
│ │
│ GOOD ORDER: (tenant, status, created_at) │
│ ┌──────────────────────────────────────────┐ │
│ │ tenant = 7 (equality seek) │ │
│ │ ↓ │ │
│ │ status = 'pending' (equality seek) │ │
│ │ ↓ │ │
│ │ created_at > '2026-01-01' (range walk) │ │
│ └──────────────────────────────────────────┘ │
│ Result: direct seek to exact rows, range walk. │
│ │
│ BAD ORDER: (created_at, tenant, status) │
│ ┌──────────────────────────────────────────┐ │
│ │ created_at > '2026-01-01' (range walk) │ │
│ │ ↓ │ │
│ │ tenant and status become filters only │ │
│ └──────────────────────────────────────────┘ │
│ Result: walk large range, filter most rows. │
│ │
└────────────────────────────────────────────────────────────┘Partial Index: Full vs Filtered
┌────────────────────────────────────────────────────────────┐
│ PARTIAL INDEX ON A LARGE TABLE │
│ │
│ FULL INDEX (all statuses) │
│ ┌──────────────────────────────────────────┐ │
│ │ pending │ 50,000 rows │ │
│ │ shipped │ 200,000 rows │ │
│ │ delivered │ 4,500,000 rows │ │
│ │ cancelled │ 250,000 rows │ │
│ │ TOTAL: 5,000,000 entries │ │
│ └──────────────────────────────────────────┘ │
│ Size: 500 MB. Touched by every insert. │
│ │
│ PARTIAL INDEX WHERE status = 'pending' │
│ ┌──────────────────────────────────────────┐ │
│ │ pending │ 50,000 rows │ │
│ │ TOTAL: 50,000 entries │ │
│ └──────────────────────────────────────────┘ │
│ Size: 5 MB. Touched only by pending inserts. │
│ │
│ Query: WHERE status = 'pending' AND created_at > ? │
│ Full index: scans 50,000 entries, filters. │
│ Partial index: scans 50,000 entries, no filter. │
│ Partial is 100× smaller and stays in memory. │
│ │
└────────────────────────────────────────────────────────────┘Partial Unique Index for Conditional Rules
┌────────────────────────────────────────────────────────────┐
│ PARTIAL UNIQUE INDEX: ONE PRIMARY EMAIL PER USER │
│ │
│ Table: emails(user_id, address, is_primary) │
│ │
│ user_id | address | is_primary │
│ --------+-------------------+------------ │
│ 42 | alice@work.com | TRUE │
│ 42 | alice@home.com | FALSE │
│ 42 | alice@old.com | FALSE │
│ 99 | bob@work.com | TRUE │
│ 99 | bob@home.com | FALSE │
│ │
│ Index: CREATE UNIQUE INDEX idx_one_primary │
│ ON emails(user_id) WHERE is_primary = TRUE; │
│ │
│ The index contains only the TRUE rows: │
│ (42, TRUE) │
│ (99, TRUE) │
│ │
│ INSERT (42, ..., TRUE) → duplicate key, rejected. │
│ INSERT (42, ..., FALSE) → not in index, allowed. │
│ │
│ Uniqueness is enforced conditionally, on the subset │
│ the business rule actually cares about. │
│ │
└────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Composite index | Single B-tree over a tuple of columns |
| Leftmost prefix rule | Only prefixes of the index columns can be used for seeks |
| Equality before range | Equality columns must precede range columns |
| Covering composite | Composite index containing all columns a query needs |
| Unique index | B-tree that rejects duplicate key values |
| Multi-column unique | Uniqueness on the combination, not each column |
| NULL uniqueness | NULLs treated as distinct in most databases |
| Partial index | Index over a filtered subset of rows |
| Predicate matching | Query must imply the index predicate to use it |
| Partial unique | Conditional uniqueness on a row subset |
| Write cost | Composite and partial reduce total index maintenance |
| Index-only scan | Query answered entirely from the index |
Key takeaways:
- Composite indexes are ordered structures, not collections of single-column indexes. The leftmost prefix rule determines which queries can use them. A query that skips a leading column cannot seek into the index.
- Column order is the entire design decision. Put equality columns first, range and sort columns after, and covering columns last. Get the order wrong, and the index serves no query well.
- Composite indexes eliminate sorts. When the sort column follows the filter column in the index, the leaf chain is already in the requested order. This is one of the largest single-query wins available.
- Unique indexes enforce correctness under concurrency. Application-level checks have a race window. Only the database’s unique index closes it, and it accelerates lookups at the same time.
- Partial indexes shrink the working set to the hot subset. A table with millions of cold rows and thousands of hot ones gets a tiny index that fits in memory and is cheap to maintain.
- Predicate matching is strict. A partial index is invisible to the optimizer unless the query includes the predicate the index was built for. Applications must generate matching queries.
- Partial unique indexes express conditional business rules. “One primary email per user,” “one active subscription per customer,” and “one pending order per customer” are all enforced by the same mechanism.
- Audit and prune. Composite and partial indexes multiply quickly. Unused ones still tax every write. Monitor usage and drop anything that does not earn its keep.
Remember: Composite, unique, and partial indexes are the three workhorses of practical index design. A composite index turns a multi-column filter and a sort into a single descent down a sorted structure, but only if the column order matches the query. A unique index enforces a correctness guarantee the application cannot provide on its own, and accelerates the lookups that rely on that guarantee. A partial index makes the hot subset of a large table feel small by refusing to index the rows that queries never touch. Together, they let a schema serve complex query patterns without accumulating dozens of overlapping full indexes, keeping write amplification low and the working set small. Choose the column order from the queries, not the schema. Choose the predicate from the workload, not the column definition.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!