“Bad programmers worry about the code. Good programmers worry about data structures and their relationships.”Linus Torvalds · Mesaj pe lista de discuții git · 2006 · Mesaj pe lista de discuții git, 27 iunie 2006
Do not optimise the query; lay the data out so the query has nothing to read.
The B-tree index is the king of transactional databases: it finds a row in a few steps. Columnar warehouses work on a different principle: they do not look for a row, they skip blocks. Every column block carries a min and a max (zone map); if the filter asks for "March" and the block covers "June–July", the block is not read at all. Hence two levers. Partitioning — usually by date — eliminates whole directories before the query starts. Clustering (or the sort key) groups nearby values into the same blocks, so the zone maps have something to skip; without it, every block contains a bit of everything and none can be skipped. The design rules follow the filters, not the arrival of data: partition on what queries filter by, cluster on the second column in the WHERE. Too many partitions mean small files and metadata more expensive than the data; compact them. For equality on high-cardinality columns, Bloom filters; for repeated aggregates, materialised views; and fresh statistics remain the condition for any of this to be used. Torvalds was talking about code, but the rule holds: do not optimise the query; lay the data out so the query has nothing to read.
Why it matters The difference between a three-second query and a three-minute one on the same data is usually just the order in which it was written to disk.