A query filters orders by customer and date, so you create an index containing both columns. The query remains slow. The columns are present, but their order may organise the index in a way that does little to narrow the search.

A composite index contains more than one key column. For a B-tree index, those columns establish a sorted structure that the database can navigate. Understanding that structure helps you choose indexes for real queries instead of adding columns and hoping the optimiser will find a shortcut.

Introduction

An index is another physical organisation of data. Creating it asks the database to maintain that organisation whenever relevant rows change, in exchange for making certain reads faster. The order of its columns determines which values sit together and which searches can jump directly to a small useful range.

This article uses an orders table and PostgreSQL examples to explain that trade-off. The underlying reasoning is useful for other B-tree implementations, but optimiser features, included columns, null handling and online index operations vary by engine and version. Check the actual platform before applying the syntax or assuming identical behaviour.

The objective is not to find one ideal index for the entire table. A customer history page, a site-wide fulfilment queue and a monthly financial report ask different questions. An index can serve several related queries, but incompatible access patterns may need separate designs or a different read model.

Start with a complete query: its filters, sorting, selected columns and expected result size. Then inspect the distribution of the data. A customer with five orders and a customer with five million orders can produce very different plans even when the SQL text is identical.

Throughout the examples, sample identifiers and dates are illustrative. No execution timings are claimed without measurements. The useful skill is predicting the work an index could avoid, then checking whether the optimiser and real workload confirm that prediction.

Column order becomes much less mysterious when viewed as ordered groups. From there, equality predicates, ranges, sorting, covering and pagination are all questions about how far the database must travel through those groups to answer the query.

Think in Ordered Groups

Consider an index on (customer_id, created_at). Entries are grouped first by customer. Within each customer's group, they are ordered by creation time.

That layout suits a query asking for one customer's orders after a particular date. The database can find the customer's group, then navigate to the relevant part of its timeline.

An index on (created_at, customer_id) organises the same values differently: first by time, then by customer within equal timestamps. It may be useful for finding all recent orders, but it does not offer the same contiguous customer-specific range. Indexes containing identical columns are therefore not necessarily interchangeable.

Visualise a Small Set of Keys

Ignore the other order columns for a moment and consider these index entries:

customer_id | created_at          | order_id
------------+---------------------+---------
41          | 2026-09-01 09:00:00 | 801
41          | 2026-09-03 10:00:00 | 813
42          | 2026-08-29 12:00:00 | 792
42          | 2026-09-01 11:00:00 | 805
42          | 2026-09-04 15:00:00 | 822
43          | 2026-09-02 08:00:00 | 809

With keys ordered by customer and then time, customer 42's history is one contiguous group. A condition on that customer identifies the group, and a date boundary identifies a smaller interval inside it. The database does not need to inspect customer 41's history to answer the question.

Reverse the first two columns and the physical ordering becomes date-first. Customer 42's records are now separated by other customers' orders at neighbouring times. The date range remains useful, but locating only customer 42 may require examining a much larger section of recent activity.

This is a lexicographic order: compare the first key, then use the second to order equal first keys, then the third to break further ties. The index is not three independent sorted lists that the database can rearrange freely for each query.

Keep the term "contiguous" in mind. Many index-design rules are attempts to make the requested rows occupy a small contiguous interval, already in a useful output order. When a query spans many separated groups, the engine may need repeated searches, a broader scan, a bitmap operation or another plan altogether.

Separate Index Keys from Table Layout

The example concerns the order of a particular B-tree index. It does not imply that the table's rows are permanently stored in the same order, nor that other indexes on the table share this arrangement. Each index has its own keys and maintenance cost.

In PostgreSQL, finding an index entry can still require a heap visit to obtain columns or verify visibility. Other engines organise clustered storage differently. This is one reason that identical-looking composite indexes can have different practical costs across products.

Also distinguish logical order from current memory residency. A good index can perform poorly during cold reads if it needs many random page accesses, while a broader scan may look fast when everything is cached. Compare representative conditions instead of treating one warm execution as a permanent property of the index.

Put the Actual Query Beside the Index

Here is a PostgreSQL example, using literal values to keep it readable:

SELECT order_id, created_at, total
FROM orders
WHERE customer_id = 42
  AND created_at >= TIMESTAMP '2026-09-01 00:00:00'
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

CREATE INDEX ix_orders_customer_recent
ON orders (customer_id, created_at DESC, order_id DESC);

The equality predicate selects a customer group. The time condition narrows the range within that group. The remaining key order matches the requested output, including a stable identifier to break timestamp ties.

PostgreSQL's multicolumn index documentation explains how leading equalities and the first range condition constrain a B-tree scan. Later conditions can still filter index entries, even when they do not narrow the scanned range in the same way.

Explain What Each Column Contributes

customer_id comes first because this query supplies one exact customer. created_at then narrows and orders that customer's recent history. order_id provides a deterministic order when timestamps tie, which matters for stable results and pagination.

The LIMIT 20 is part of the design, not an incidental presentation choice. If matching entries are encountered in the requested order, the database may stop after enough eligible rows are found. Without useful ordering, it might need to find and sort many candidates before knowing which 20 belong first.

Now add status = 'Paid'. An alternative key order, (customer_id, status, created_at DESC, order_id DESC), groups paid orders within a customer before arranging them by time. This can be attractive when almost every customer-page request includes a fixed status.

But consider the original query without status. That alternative index groups the customer's cancelled, paid and pending orders separately. It no longer presents one simple combined timeline across all statuses in the requested time order. Optimising one query variation can make another less convenient.

List those variations before changing the index. A filter that is optional in the interface is an important physical-design detail. The best design depends on how often users apply it, how selective it is and how much extra work the less common query can tolerate.

Understand the First Range Boundary

Suppose an index has keys (customer_id, created_at, total) and the query specifies one customer, a date range and a minimum total. The date range spans many distinct timestamps. Within each timestamp, totals are ordered, but all high-total orders across the full date interval do not form one simple final suffix of the index.

The later total condition can still be useful when checking index entries. It may reject rows before a table visit, depending on the plan. The distinction is that it does not automatically shrink the main scanned interval as effectively as another leading equality would.

Moving total before created_at changes which range is easiest to navigate. If both conditions are ranges, neither ordering can generally turn every combination into one perfect interval. Choose based on measured selectivity, required ordering and result size rather than expecting a column permutation to remove all work.

This also explains why "put the most selective column first" is incomplete. If both columns have exact equality conditions, either order may reach a narrow matching group. Shared prefix queries, sort order and the range that follows often matter more than a standalone distinct-value count.

Avoid Universal Leftmost Rules

An index on (customer_id, created_at) is usually less attractive for a query that filters only created_at. However, saying the database can never use it is too strong.

The optimiser may choose an index scan, and some databases support skip-scan strategies that perform repeated searches over missing leading values. PostgreSQL 18 can use this optimisation when the distribution makes it worthwhile.

The useful distinction is between "the index can be used" and "the index makes this query efficient". A plan that scans most of an index to return a handful of rows can still be poor. Check your database engine, version, data distribution, and actual execution plan before turning a rule of thumb into a guarantee.

Ask How Much Work the Chosen Plan Performs

Suppose the table contains only three status values and an index on (status, created_at). A query for a recent date without specifying status may be able to benefit from repeated searches over those few leading groups. The same strategy across millions of distinct customer identifiers may be unattractive.

This illustrates why the missing-leading-column question needs distribution information. An optimiser feature is not a promise that every trailing-column predicate becomes an efficient point lookup. Verify the chosen plan and compare actual work with an index whose leading key directly matches the query.

Avoid tests that force an index and then conclude it is optimal because the query runs. A forced plan can help investigate alternatives, but it suppresses part of the optimiser's choice. The final index should earn its place under normal planning with representative statistics and parameters.

The engine can also choose a sequential scan for good reasons. If a query returns most of the table, walking table pages once may be cheaper than following many index entries and fetching scattered rows. The absence of an index scan is not automatically a performance defect.

Consider Multiple Indexes Without Assuming Equivalence

Separate indexes on customer_id and created_at are another possible design. PostgreSQL can sometimes combine index results through bitmap operations, but that is not the same physical path as one composite index arranged for the complete query. In particular, combining matches does not necessarily preserve the requested output order.

PostgreSQL's guide to combining indexes explains why bitmap access loses the original index ordering and can require a separate sort.

A composite index can therefore help a bounded ordered query even when both filter columns already have individual indexes. Conversely, individual indexes may serve a broader set of independent queries. The workload decides whether their flexibility justifies the extra work for this particular request.

Do not create every possible combination. Three filter columns already allow several orders, and each additional index increases write and storage costs. Group similar queries into access patterns and test a small set of candidates that have a clear purpose.

Account for Sorting and Common Variations

The order query uses a fixed customer and requests the newest rows first. An index matching that order can let the database stop after finding enough qualifying rows, rather than sorting a much larger result.

Direction matters particularly with mixed ascending and descending columns. Some orderings can be satisfied by scanning backwards; others need a differently defined index. PostgreSQL documents these choices in its indexes and ordering guide.

Also list common query variations. An index beginning with customer_id may suit customer pages but do little for a site-wide fulfilment queue filtered by status. When two columns both have equality conditions, choose their order with these other queries in mind; "most selective first" is not a complete design method.

Match Direction and Tie-Breaking Deliberately

For a single varying key, scanning a B-tree backwards can often provide the opposite direction. With several varying keys, reversing the scan reverses their ordering together. A query requesting one ascending column and another descending column may therefore need a definition that explicitly matches that mixed order.

When customer_id is fixed by equality, its direction is irrelevant to ordering that one customer's rows. The varying suffix is what matters for the result. This makes it possible to reason about the query by first reducing it to the selected group and then examining the order inside that group.

Null positioning can also affect whether an ordering matches. If a nullable dispatch time is sorted with nulls first or last, include that requirement in the query and index analysis. Do not assume a default ordering is identical across engines or scan directions.

A tie-breaking identifier avoids unstable ordering when several orders share a timestamp. Without it, two executions can legitimately return equal-time rows in different orders. Adding the tie-breaker to the SQL but omitting it from the relevant index may introduce extra sorting or prevent the simplest bounded scan.

Connect the Index to Cursor Pagination

For the newest customer orders, a later page can use the previous page's final timestamp and identifier as a boundary:

SELECT order_id, created_at, total
FROM orders
WHERE customer_id = 42
  AND (created_at, order_id) <
      (TIMESTAMP '2026-09-05 12:30:00', 900)
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

This PostgreSQL example assumes non-null ordering keys and the same timestamp type as the column. The tuple boundary uses the same total order as the result, so the matching index can continue from a known position. Store the exact boundary values in a protected cursor rather than asking clients to reconstruct them from formatted display dates.

Offset pagination asks the database to skip earlier rows before returning the requested page. Even with a useful index, deeper offsets generally require more preceding entries to be processed. Cursor pagination changes the query's starting point, which often matters more than adding another covering column.

Concurrent writes still affect what later pages represent. A cursor over mutable data is not automatically a stable snapshot. The application must decide whether it promises a live history or a fixed view, especially if ordering values can change after publication.

Keep Predicates Compatible with the Indexed Values

A query that applies a function to an indexed column may not match the simple key in the way expected. For example, extracting the date from created_at is a different expression from comparing the timestamp directly. A half-open timestamp range is often a clearer starting point for one day's records.

Use created_at >= dayStart AND created_at < nextDayStart, with boundaries computed for the application's intended time zone. This avoids relying on an arbitrary final millisecond and gives the optimiser direct range predicates. If expression-based searches are central, investigate an appropriate expression index rather than assuming the ordinary column index covers them.

Parameter types also matter. Pass values using types that match the intended comparison instead of relying on implicit conversions. Inspect generated SQL from an ORM; the application expression may be translated into a function or cast that changes the available access path.

Distinguish Search Keys from Included Data

The example also returns total, which is not in the index. Depending on the database, the engine may need to visit the table to retrieve it.

PostgreSQL can store this value as an included column:

CREATE INDEX ix_orders_customer_recent_covering
ON orders (customer_id, created_at DESC, order_id DESC)
INCLUDE (total);

This is an alternative to the earlier index, not a recommendation to install both. total becomes available as payload without changing the key ordering. It is not an additional navigational search key.

A covering index does not guarantee that PostgreSQL avoids every table access: row visibility checks can still require visits. The index-only scan documentation explains this qualification. Wider indexes also consume more storage and increase maintenance work on writes.

Include Only Payload That Pays for Itself

Adding total as an included column can help this exact projection because the index now contains all selected values. It does not make total a navigational key, and it does not change which rows are grouped together. A later query filtering primarily by total may still need a different access path.

Avoid copying an entire wide entity into an index merely to label it covering. Large descriptions, addresses or JSON payloads increase index size, reduce the number of entries fitting into cache and add maintenance work. The benefit should be tied to frequently executed queries returning a deliberately small projection.

Included columns also matter on updates. Changing a stored payload value requires the database to maintain the relevant index representation. If order totals are corrected frequently, include that write cost in the measurement instead of assuming only key-column changes affect the index.

For PostgreSQL specifically, visibility information influences whether an index-only scan still visits the heap. A recently updated table can behave differently from a mostly stable historical table. Maintenance and data churn therefore affect the practical benefit of coverage, not just the CREATE INDEX statement.

Compare a narrower index plus a small number of table fetches against a wider covering index. Returning 20 orders may require little additional table work in a healthy cache, while maintaining a much larger index affects every write. The best trade-off can differ between a high-write operational table and a mostly read-only archive.

Consider Partial Indexes and Constraints

A fulfilment queue might repeatedly query a small pending subset rather than every historical order. In PostgreSQL, a partial index can store only rows satisfying a predicate:

CREATE INDEX ix_orders_pending_queue
ON orders (created_at, order_id)
WHERE status = 'Pending';

For a query that explicitly requests pending orders in that order, the index can be smaller than an equivalent full-table index. It also stops containing a row when the row no longer satisfies the predicate. That maintenance work is part of the write path when statuses change.

The planner needs to establish that the query's condition implies the index predicate. Parameterisation can complicate that proof, so test the application-generated statement rather than only a hand-written literal example. PostgreSQL's partial-index documentation explains the predicate-matching requirement.

This partial index serves a different access pattern from the customer-history index. Keeping both may be justified if both queries are important. Installing two almost identical customer-history indexes, one covering and one not, is less obviously useful and should require evidence.

Unique indexes also enforce rules, which changes how they should be reviewed. An index on (tenant_id, external_reference) may prevent duplicate imported orders. Removing it because no query appears to use it can remove the business guarantee even if another index improves reads.

Adding extra key columns to a unique index changes the uniqueness rule. Uniqueness on (tenant_id, external_reference, status) would allow the same reference with different statuses. Treat constraint definitions as domain decisions rather than performance decorations.

Verify the Benefit Before Keeping It

Compare plans with realistic row counts and values, including customers with unusually large histories. Look at rows read versus returned, explicit sorts, table fetches, and elapsed time. Update statistics where necessary so the optimiser has a reasonable picture of the data.

In PostgreSQL, EXPLAIN ANALYZE executes the statement. Use a safe environment when investigating mutations, and remember that even a large read can create load.

Measure insert and update performance as well. Deploy large index builds using the engine's appropriate online or concurrent options, and monitor resource use. Remove redundant indexes only after checking their role in other queries and constraints.

Read a Plan as a Description of Work

For a safe read query in a representative environment, a useful starting command is:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, created_at, total
FROM orders
WHERE customer_id = 42
  AND created_at >= TIMESTAMP '2026-09-01 00:00:00'
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

Look for where rows are located, filtered, fetched and sorted. Compare estimated rows with actual rows. A large mismatch can cause the optimiser to choose a plan based on an inaccurate picture of the data, especially for unusually large customers or correlated filters.

Check how many rows are examined to return 20. A plan can contain the desired index name while scanning a broad range and discarding most entries. A top-level LIMIT does not prove that little work happened underneath it.

An explicit sort is not automatically bad. Sorting a few dozen rows can be inexpensive, whereas scanning many unrelated index entries merely to avoid a sort can be worse. Compare the total work and latency rather than treating one plan node as forbidden.

Buffer information helps distinguish cached work from physical reads, but one execution remains only one sample. PostgreSQL's EXPLAIN guide explains how to interpret plan estimates and execution information. Apply it with representative parameters and an understanding of the measurement overhead.

Use Data That Can Disprove the Design

Test ordinary customers, unusually large histories, empty results and broad date ranges. Include status distributions that match reality. A dataset where every status occurs equally often may hide the cost of a production table containing mostly completed orders and a small pending queue.

Consider correlations. Large enterprise customers might generate both more orders and different status proportions than small customers. Independent per-column assumptions can misestimate their combined predicates. Where relevant, investigate the engine's statistics facilities instead of repeatedly adding indexes to compensate for misunderstood distributions.

Compare application execution with the hand-written test. Prepared statements, ORM parameter types and connection settings can affect planning. Capture the actual SQL and relevant parameter shapes so the tested improvement applies to the requests users make.

Measure writes under concurrency as well as individual read latency. Additional indexes consume storage and add insert, update, logging and replication work. A read improvement that causes unacceptable checkout write latency may need a narrower index, a prepared view or a different query.

Deploy and Review the Index Safely

Large index builds consume resources even when the database supports a mode that permits concurrent writes. PostgreSQL's CREATE INDEX documentation describes the different behaviour and restrictions of concurrent builds. Choose the supported procedure for the actual environment and monitor its progress and completion state.

Do not leave a failed build unexamined. Confirm that the intended index is valid and usable before treating the deployment as complete. Also confirm the migration tool supports the chosen operation's transaction requirements rather than assuming every CREATE INDEX can run inside its normal migration transaction.

Review existing indexes after observing the new one over a representative period. An index with the same leading keys may appear redundant but differ in uniqueness, included data, ordering or a partial predicate. Check constraints and infrequent reporting queries before removing it.

Keep a short explanation of each retained index's purpose, such as "customer recent orders with cursor pagination". This makes future changes easier to evaluate than names that simply enumerate columns. Revisit the decision when query shapes, data distribution or database versions change.

Summary

Composite index order controls how data is grouped, narrowed, and returned. Start with the exact filters, sort order, and result size, then consider other queries that could share the index.

Use equality and range rules as a starting point, account for engine-specific behaviour, and separate included payload from search keys. Keep an index because measured query improvements justify its storage and write cost, not simply because its columns appear in a WHERE clause.