Author
Linus Torvalds
1 reading card from 1 book · 2006.
1 card
Mesaj pe lista de discuții git · 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.
“Bad programmers worry about the code. Good programmers worry about data structures and their relationships.”