← Back
Home › Systems › Compilers and language tooling

query-optimizer

SQL query optimizer and codegen

PostgreSQL planner

SystemTechniques · 8Primitives · 30 query-optimizer External merge sortsearch-sort · medium: Sort data larger than RAM: write memory-sized sorted runs, then k-way merge them with large sequential buffers; terabytes in one merge pass.›Cardinality estimationstorage · medium: Row-count estimates from histograms, MCVs, sketches and sampling for the optimizerCascades query optimizationstorage · medium: Memo of groups and expressions, transformation and implementation rules, top-down cost-based searchColumnar storagestorage · medium: Store each column contiguously so analytic scans read only the columns they need, compress well and run as tight SIMD loops.›Join order dynamic programmingstorage · medium: DPccp and DPhyp enumeration of join orders with cost models; greedy fallback for many joinsQuery compilationstorage · medium: Compile query pipelines into tight machine-code loops (data-centric produce/consume, LLVM or template JIT) instead of interpreting operatorsSubquery decorrelationstorage · medium: Unnest correlated subqueries into joins via dependent join and magic setsHyperLogLogdata-structures · low: Counts distinct elements in kilobytes with about 1 % error by tracking the longest run of leading zero bits in hashed values per bucket.›Relational algebrastorage · low: Relational operators and equivalence rules: selection pushdown, join reordering, projectionsHash joinstorage · low: Build a hash table on the smaller input's join key and probe it with every row of the larger one; the linear-time workhorse for equi-joins.›Bloom filterdata-structures · low: Bit array answering "definitely not present" or "probably present" in about 10 bits per key for 1 % false positives; inserts are cheap, deletes impossible.›Top-k selectionsearch-sort · low: Keep the k best of n items without sorting all n - bounded min-heap for streams, quickselect for arrays, threshold pruning to skip most items.›B+ treestorage · low: Balanced high-fanout search tree with all values in linked leaves; the default on-disk index for point lookups and range scans.›Sort-merge joinstorage · low: Sort both inputs on the key, then advance two cursors in lockstep and emit matching runs; cheap on sorted inputs and good for range joins.›Buffer pools for zero-allocation I/Ohardware-os · low: Preallocate a fixed set of aligned I/O buffers and recycle them through a free list, so the hot path never mallocs, faults or unmaps. The bounded count doubles as back pressure.›K way mergesearch-sort · low: Merge k sorted streams with a heap or loser tree (merging iterators)Pattern-defeating quicksort (pdqsor…search-sort · low: Introsort successor: quicksort with adaptive pivots, linear on sorted and equal-heavy input, heapsort fallback; the unstable sort of Go and (until 1.81) Rust.›Quantile sketchesdata-structures · low: Mergeable quantile sketches: t-digest, DDSketch, HDR histogramsReservoir samplingdata-structures · low: Uniform k-sample from a stream of unknown length (Algorithm R/L)Hash consingdata-structures · low: Intern structurally equal immutable terms so equality is pointer comparison and sharing is maximalPredicate pushdownstorage · low: Move filters and projections into scans and remote sourcesBit packing and PFORcompression · low: Store blocks of integers with exactly b bits each and patch rare outliers separately (PFOR); the fastest integer codec family, billions of values per second.›Delta encodingcompression · low: Store differences between consecutive values instead of the values, turning sorted IDs, timestamps and counters into small numbers that pack into few bits.›+ 1 more techniques+ 14 more primitives

Similar systems

Share the most building blocks with this one.

query-engine 28 %columnar-warehouse 28 %variant-database 24 %batch-engine 19 %log-analytics 18 %