Comparison
Sort keyvsZ-ordering
Sort key
the rows are stored in date order, so a range query reads a contiguous run of blocks instead of touching all of them.
The column or columns a table's rows are physically ordered by. Sorting improves both skipping and compression, since adjacent similar values compress far better, so it often reduces bytes scanned twice over. Choosing more than two or three sort columns rarely helps, because ordering is lexicographic and the later columns only discriminate within already-narrow ranges.
Full entry →Z-ordering
queries filter on either region or product, never both, and one linear sort order can only help one of them.
Interleaving the bits of several columns so that data is co-located along more than one dimension at once. It is a compromise rather than a win: no single column is as well ordered as it would be under a plain sort, but every included column gets useful skipping. It is worth reaching for only when the query mix genuinely filters on different columns, and it is one of the more expensive maintenance operations to run.
Full entry →