A columnar table is sorted by (event_date, country) — why does filtering only on country prune poorly?

middleshould knowfreq 56%

answer

sorting works left to right, like a dictionary
the second column is sorted only within ties
the leading column sets the block bounds
filter on both and the second one earns its place
predicate order in WHERE changes nothing

basics

~20 s

Composite sorting is lexicographic: country is ordered only within rows sharing the same event_date. Blocks generally span many dates, so each one contains countries from across the whole list and its country min/max covers nearly everything.

solid answer

~40 s

A multi-column sort key orders rows left to right, like a dictionary. Rows are grouped by event_date first; country is sorted only inside one date's run. Unless a block happens to sit entirely within a single date and a single country, its country bounds stretch across most of the alphabet, so a country = 'FR' filter eliminates almost nothing — while event_date filters prune superbly and event_date plus country prunes best of all. The practical rules: the leading column is the one that really prunes; put the column present in the most queries first; prefer a leading column coarse enough that many rows share a value, so blocks can reach tight or constant ranges. Reversing to (country, event_date) flips which query is cheap, it does not make both cheap.

A multi-column sort key orders rows left to right, like a dictionary. Rows are grouped by `event_date` first; `country` is sorted only *inside* one date's run. Unless a block happens to sit entirely within a single date and a single country, its `country` bounds stretch across most of the alphabet, so a `country = 'FR'` filter eliminates almost nothing — while `event_date` filters prune superbly and `event_date` **plus** `country` prunes best of all. The practical rules: the leading column is the one that really prunes; put the column present in the most queries first; prefer a leading column coarse enough that many rows share a value, so blocks can reach tight or constant ranges. Reversing to `(country, event_date)` flips which query is cheap, it does not make both cheap.

code

sql · 12 lines

sql

-- table physically ordered by (event_date, country)

-- excellent pruning: leading key column
SELECT sum(revenue) FROM sales
WHERE event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-07';

-- poor pruning: trailing column alone, blocks span many countries
SELECT sum(revenue) FROM sales WHERE country = 'FR';

-- best pruning: leading column narrows blocks, trailing one narrows further
SELECT sum(revenue) FROM sales
WHERE event_date = DATE '2026-08-01' AND country = 'FR';

go deeper

for a junior

Recall that a multi-column sort key orders rows left to right, so the first column is the one that lets the engine skip data and later columns matter far less on their own.

for a middle

Explain why block bounds on a trailing column stay wide, why filtering both columns together does prune well, and how leading-column granularity decides whether blocks can hold a constant value.

for a senior

Choose the order from measured traffic — bytes scanned times frequency per access pattern — and know the escapes when two filters both matter: partition one axis and sort the other, interleave the key, or keep a second ordered copy.

for a principal

Own that layout is a zero-sum allocation across competing consumers: state the rule for who the table is optimized for, and how a second pattern gets funded in storage and pipeline cost rather than by stacking key columns.

Lexicographic order, and what it implies for block bounds

A composite sort or cluster key (a, b) sorts rows by a, and by b only among rows sharing the same a — exactly how a dictionary orders words by first letter, then second. That single fact explains everything about which filters prune.

Block elimination works off per-block min/max. Consider a table sorted by (event_date, country) and cut into blocks of, say, half a million rows:

event_date bounds per block: narrow. Each block covers one date, or a fragment of one, so a date filter eliminates nearly every block.
country bounds per block: wide. A block covering one day contains every country that appeared that day, so its bounds run from roughly the first to the last country code. A filter country = 'FR' passes the interval test on nearly every block.

The consequence is the asymmetry candidates get wrong: the leading column prunes, trailing columns barely do, and the benefit decays sharply with each position.

The exception that makes it useful

Trailing columns are not worthless. They prune when the leading column is also constrained:

sql

WHERE event_date = DATE '2026-08-01'
  AND country = 'FR'

Here the date predicate narrows the candidate set to one day's blocks, and within that day rows are sorted by country, so the country bounds of those blocks are tight and the second predicate eliminates most of them. This is the reason to add a second key column at all: it accelerates the compound filter, not the standalone one.

A second exception: if a block happens to be entirely inside one date and one country — which occurs when the leading column is coarse and the data volume per (date, country) is large — the block's country range is a single constant, and pruning on country alone works fine for those blocks. This is why the leading column's granularity matters so much.

Choosing the column order

The decision is empirical, driven by query traffic, but a few rules hold widely:

Lead with the column that appears in the most (or the most expensive) queries. If nine in ten queries carry a date range and one in ten carries a country, event_date leads. Weight by bytes scanned, not by query count alone.
Prefer a leading column coarse enough that many rows share a value. A millisecond timestamp gives every row a distinct key, so blocks can never share a constant leading value and the trailing column never gets a chance to be ordered within a block. Truncating to hour or day is often strictly better.
Equality-filtered columns before range-filtered ones, when both are common. Once a range predicate is applied on the leading column, the trailing column's ordering is spread over the whole range and stops helping.
Do not stack five columns. Each extra position contributes almost nothing to standalone pruning while adding sort and maintenance cost. Two or three meaningful columns is usually the whole benefit.
Do not confuse this with predicate order in SQL. Where you write the conditions in the WHERE clause is irrelevant; only the physical key order matters.

What to do when both filters must be fast

One table has one physical order, so (event_date, country) and (country, event_date) cannot both be optimal. The realistic options:

Partition on one axis, sort on the other. Partitioning by date and sorting by country inside each partition gives independent elimination on both, at the cost of more files. This is the most common answer and worth leading with.
Multi-dimensional clustering. Some engines interleave the key columns using a space-filling curve (Z-order style), so no single column prunes as well as a leading column would, but both prune reasonably. Good when two columns are filtered with similar frequency; a loss when one clearly dominates.
A second physically ordered copy of the table, refreshed by the same pipeline, serving the minority access pattern.
A block-level skipping structure on the trailing column, which rescues selective equality lookups without touching the order.

Diagnosing it

If someone reports "we sorted the table and it did not help", check three things: which column leads the key, whether the slow query filters on that column at all, and whether the leading column's granularity lets blocks reach constant values. The scan statistics settle it — compare blocks scanned for a filter on the leading column against the same filter on a trailing one, and the shape of the key becomes visible immediately.

## Lexicographic order, and what it implies for block bounds A composite sort or cluster key `(a, b)` sorts rows by `a`, and by `b` only among rows sharing the same `a` — exactly how a dictionary orders words by first letter, then second. That single fact explains everything about which filters prune. Block elimination works off per-block min/max. Consider a table sorted by `(event_date, country)` and cut into blocks of, say, half a million rows: - `event_date` bounds per block: narrow. Each block covers one date, or a fragment of one, so a date filter eliminates nearly every block. - `country` bounds per block: wide. A block covering one day contains every country that appeared that day, so its bounds run from roughly the first to the last country code. A filter `country = 'FR'` passes the interval test on nearly every block. The consequence is the asymmetry candidates get wrong: the *leading* column prunes, trailing columns barely do, and the benefit decays sharply with each position. ## The exception that makes it useful Trailing columns are not worthless. They prune when the leading column is *also* constrained: ```sql WHERE event_date = DATE '2026-08-01' AND country = 'FR' ``` Here the date predicate narrows the candidate set to one day's blocks, and within that day rows are sorted by country, so the `country` bounds of *those* blocks are tight and the second predicate eliminates most of them. This is the reason to add a second key column at all: it accelerates the compound filter, not the standalone one. A second exception: if a block happens to be entirely inside one date **and** one country — which occurs when the leading column is coarse and the data volume per (date, country) is large — the block's country range is a single constant, and pruning on country alone works fine for those blocks. This is why the leading column's granularity matters so much. ## Choosing the column order The decision is empirical, driven by query traffic, but a few rules hold widely: 1. **Lead with the column that appears in the most (or the most expensive) queries.** If nine in ten queries carry a date range and one in ten carries a country, `event_date` leads. Weight by bytes scanned, not by query count alone. 2. **Prefer a leading column coarse enough that many rows share a value.** A millisecond timestamp gives every row a distinct key, so blocks can never share a constant leading value and the trailing column never gets a chance to be ordered within a block. Truncating to hour or day is often strictly better. 3. **Equality-filtered columns before range-filtered ones**, when both are common. Once a range predicate is applied on the leading column, the trailing column's ordering is spread over the whole range and stops helping. 4. **Do not stack five columns.** Each extra position contributes almost nothing to standalone pruning while adding sort and maintenance cost. Two or three meaningful columns is usually the whole benefit. 5. **Do not confuse this with predicate order in SQL.** Where you write the conditions in the `WHERE` clause is irrelevant; only the *physical* key order matters. ## What to do when both filters must be fast One table has one physical order, so `(event_date, country)` and `(country, event_date)` cannot both be optimal. The realistic options: - **Partition on one axis, sort on the other.** Partitioning by date and sorting by country inside each partition gives independent elimination on both, at the cost of more files. This is the most common answer and worth leading with. - **Multi-dimensional clustering.** Some engines interleave the key columns using a space-filling curve (Z-order style), so no single column prunes as well as a leading column would, but *both* prune reasonably. Good when two columns are filtered with similar frequency; a loss when one clearly dominates. - **A second physically ordered copy** of the table, refreshed by the same pipeline, serving the minority access pattern. - **A block-level skipping structure** on the trailing column, which rescues selective equality lookups without touching the order. ## Diagnosing it If someone reports "we sorted the table and it did not help", check three things: which column leads the key, whether the slow query filters on that column at all, and whether the leading column's granularity lets blocks reach constant values. The scan statistics settle it — compare blocks scanned for a filter on the leading column against the same filter on a trailing one, and the shape of the key becomes visible immediately.

Would swapping to (country, event_date) be an improvement?
Only if country filters dominate the traffic. It flips the asymmetry: country then prunes hard, and date prunes only within a country. Weigh it by bytes scanned times query frequency for each pattern. If both patterns matter roughly equally, partition on one axis and sort on the other rather than choosing a winner.
Does the order of conditions in the WHERE clause affect pruning?
No. Predicate text order is irrelevant; the engine matches available predicates against the physical layout regardless of how you wrote them. Only the key's column order, the data's actual clustering and whether the predicate compares the raw column to a constant matter.
When does adding a third and fourth sort column stop paying?
Almost immediately for standalone filters — each position is only ordered within ties of everything to its left, so by the third column blocks rarely share a constant prefix. Extra columns still cost sort time and maintenance on every write. Two or three meaningful columns typically capture the entire benefit.

In a dictionary, finding all words starting with 'q' is instant; finding all words whose second letter is 'q' means reading the whole book.

saying these in an interview costs you the question

Thinks every column in a sort key prunes equally
Puts the highest-cardinality column first by reflex
Believes predicate order in the WHERE clause matters
Adds five key columns expecting five-way pruning
Confuses sort key order with SELECT column order

A columnar table is sorted by (event_date, country) — why does filtering only on country prune poorly?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Lexicographic order, and what it implies for block bounds

The exception that makes it useful

Choosing the column order

What to do when both filters must be fast

Diagnosing it

▾common follow-ups3 questions

▾analogy~10 s

▾red flags5 flags

§ context

▾related questions7 questions

▾related topics4 topics

Lexicographic order, and what it implies for block bounds

The exception that makes it useful

Choosing the column order

What to do when both filters must be fast

Diagnosing it

answer

go deeper

context