Sorting keys
Understand how physical row order helps queries skip data.
Sorting keys
A sorting key determines how a table part arranges rows. The associated primary index helps queries skip ranges that cannot match a filter. The key is a physical optimization, not a uniqueness constraint or a promise about result order.
RawTree can choose keys automatically for raw tables or use an explicit table key. Understanding parts and merges helps explain why one table can contain several physical arrangements.
Why key order matters
A compound key has an order. Filters on a useful prefix can eliminate more work than filters on unrelated fields or later key components alone. A table keyed by tenant and time favors a different pattern from one keyed by time alone.
The query plan determines whether a predicate can use the key. Merely naming a key field in a query does not guarantee pruning. Expressions, types, and the available key for each part all matter.
Automatic per-part keys
When a raw table has no explicit key and automatic selection is enabled, RawTree derives keys from inserted data. The choices can differ between parts. During background merges, filter statistics can influence the key for the merged data.
Adopting a new key requires re-sorting rows. Configured row and byte limits bound that work. The engine can retain a suitable input key when the cost of a different choice exceeds those limits.
Automatic selection adapts gradually. It does not assign one permanent global key to every part at the first insert. Disabling automatic selection leaves new eligible parts unsorted. See Adaptive optimization.
Explicit table keys
An explicit PRIMARY KEY or ORDER BY takes precedence over automatic
selection. This makes the layout an intentional choice for a known workload.
The tradeoff is that the engine no longer chooses another automatic key for
new parts and merges.
Changing the table key changes the target layout. It does not relabel or immediately rewrite every existing part. Later merges can adopt the new key through background work.
The Change a sorting key guide contains the command procedure. The MergeTree settings reference describes automatic selection and its row, byte, and column limits.
Storage order and query order
A useful sorting key can reduce the amount of data a query reads. It does not define the returned order. Parts can have different keys, and execution can combine their rows in another order.
An explicit query ORDER BY defines result order. A key that helps execute
that query is an optimization, not a substitute for the clause.