Composite keys: match the ordered prefix to the question

For an ascending index on tenant_id, event_time, and status, rows are grouped first by tenant, then time within a tenant, then status for equal times. Equality on tenant and a time interval usually defines one straightforward contiguous scan interval. A status condition to the right of that range can reject entries without necessarily shortening the whole interval examined. Distinguish navigation that skips entries from a filter evaluated on entries already reached. Both can help, but they save different kinds of work.

Changing the order to tenant_id, status, event_time can make tenant equality plus status equality plus a time range describe a tighter interval. However, a query omitting status now has a different navigation problem. It may require broader scanning or repeated searches across status groups, and its requested time ordering may need additional work. PostgreSQL 18 can apply B-tree skip scan when repeated searches over missing leading values are cost-effective. Therefore a missing equality prefix does not make an index categorically unusable; it changes the available paths and their costs.

Selectivity is the fraction of a defined population satisfying a predicate. Tenant and status may be correlated: one tenant can contain almost all failed events while the global failed fraction is small. Multiplying global selectivities as though predicates were independent can badly misestimate the result. The exact chain rule uses the probability of the second condition given the first. Estimates based on statistics must state which conditional distributions they approximate and whether the workload's parameter values resemble the sampled population.

Returned-row selectivity and scan selectivity differ. An index may examine a thousand entries in a time interval and return only fifty after checking status. A different key order might navigate directly to fifty entries yet be worse for another common query or require an extra sort. Also account for row fetching, LIMIT behavior, grouping, key width, and writes. The slogan put the most selective column first ignores equality availability and order requirements. Start with actual query templates and compare estimated and observed plans for representative and skewed parameters.

One composite index may serve a frequent query family better than several independent indexes, while separate indexes can be combined for more varied predicates. Every added structure consumes storage and maintenance. Test common parameter combinations, including heavy tenants and rare statuses, rather than one conveniently selective example. Inspect how many entries or rows are filtered, whether sorting occurs, and which order a LIMIT can exploit. An index that returns the first twenty useful rows cheaply may be valuable even if its complete unbounded scan would be expensive.

The probability lab isolates a hypothetical filter fraction. Reset, set p=0.05 and n=20, predict one matching item on average, then Play or Step to inspect the exact binomial bars. Separately Reset, set p=0.50 and n=20, predict ten matches on average, then Play or Step. These independent common-probability trials do not traverse composite keys or represent correlated tenant data. Use the contrast to explain why a global five-percent rate is inadequate if the selected tenant's conditional rate is fifty percent.