A composite B-tree index orders tuples by its first key, then by the next key within each first-key group. This structure can make a tenant-specific time range cheap to locate. The useful rule is about how much index space a predicate narrows, not a prohibition against mentioning later columns or writing WHERE conditions in a different textual order.

Order the keys, not the SQL clauses

For an index on (tenant, happened), equality on tenant and a range on happened describe a compact portion of the tree. PostgreSQL can search that range rather than inspecting all tenants. Writing the time condition before the tenant condition in SQL does not change the logical conjunction or the index’s key order. The schema’s column order and the query’s presentation order are different things.

Leading equality conditions and the first non-equality key generally determine the range that must be scanned. Conditions on later keys can still be checked within the index, saving heap visits even when they do not further shrink a simple contiguous range. Reversing key columns changes these opportunities; it does not make an index universally unusable.

Later keys can still help

PostgreSQL 18 can use B-tree skip scans when repeated searches over missing leading-key values are cheaper than reading most of the index. With only a few tenants, a predicate on happened alone can search within each tenant group. With many distinct leading values, that strategy may be too expensive, and the planner can prefer a sequential scan or another index.

Even without a profitable skip scan, a composite index can sometimes be scanned for a condition involving a later key. The question is cost, coverage, and selectivity, not whether such SQL is legal or whether the index must be ignored. Do not use the shorthand “leftmost prefix” as an absolute claim that contradicts the target engine’s actual access methods.

Include ordering and the workload

Fixing the tenant key can make the following happened key useful for ordered results. Across several tenants, however, the index groups by tenant first; it does not provide one globally time-sorted stream. A bitmap scan also loses index ordering and may need a separate sort. Look at the complete plan instead of inferring final order from an index declaration.

Equality-first and range-next is a useful starting point for one query shape, but there is no universal “most selective column first” recipe. Shared prefixes, joins, sort direction, LIMIT, parameter distributions, and write costs all affect the choice. Compare actual recurring queries, then keep the smallest useful set of indexes. Redundant wide indexes consume cache and maintenance work even when nobody benefits from them.

When it breaks

A team may build (tenant, happened) for per-tenant queries, then add a global timeline and assume it is equally efficient. The timeline’s ordering and selectivity differ. Another regression comes from testing one tiny tenant while another owns most rows: the same parameterized SQL can have very different costs. Statistics and representative values matter more than a screenshot of one favorable plan.

Incorrect rules also cause unnecessary index churn. Adding one index per suffix because “the existing index cannot be used” can duplicate useful storage. Conversely, assuming skip scan always rescues a missing prefix can hide a costly path. Measure the chosen plan, actual rows, buffers, and sort work before changing the schema.

Check it quickly

Save this as composite.sql and run psql -X -v ON_ERROR_STOP=1 -f composite.sql on a disposable PostgreSQL 18 database. The temporary fixture contains four tenant values and twenty thousand ordered event numbers.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE events (tenant integer, happened integer, body text);
INSERT INTO events SELECT g % 4, g, repeat('x',40) FROM generate_series(1,20000) AS g;
CREATE INDEX events_tenant_time ON events (tenant, happened);
ANALYZE events;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT happened FROM events WHERE tenant=2 AND happened BETWEEN 100 AND 120
ORDER BY happened;
SELECT array_agg(happened ORDER BY happened) AS matching_times
FROM events WHERE happened BETWEEN 100 AND 120 AND tenant=2;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT tenant FROM events WHERE happened=12346;
DO $$ BEGIN
  IF (SELECT array_agg(happened ORDER BY happened) FROM events
      WHERE happened BETWEEN 100 AND 120 AND tenant=2)
     IS DISTINCT FROM ARRAY[102,106,110,114,118] THEN
    RAISE EXCEPTION 'wrong composite range';
  END IF;
  IF (SELECT tenant FROM events WHERE happened=12346) IS DISTINCT FROM 2 THEN
    RAISE EXCEPTION 'suffix predicate lost the row';
  END IF;
END $$;
ROLLBACK;

The tenant-range query returns exactly 102,106,110,114,118, including when the WHERE conditions are written in the opposite order. On PostgreSQL 18.6 it used a bitmap index scan followed by a sort. The suffix-only lookup returned tenant two through an index-only scan with repeated index searches, demonstrating a usable path without tenant equality.

Plan names, buffer counts, and search counts are observations for this dataset, not guaranteed outputs across configurations. The assertions verify result semantics, and rollback removes all objects. Repeat the measurement with more leading-key values to assess whether the suffix path remains worthwhile for a real workload.

Sources