Join Cardinality Estimation with OmniSketches

TL;DR

This paper extends OmniSketch, a probabilistic data structure, to improve multi-join cardinality estimation, leading to more accurate query plans and significant performance gains in certain scenarios.

Contribution

We introduce OmniSketch join estimator, enabling accurate multi-join cardinality estimation without assuming uniformity or independence, and demonstrate its integration into query optimization.

Findings

Intermediate result sizes reduced up to 1,077x

Execution time decreased up to 3.19x

Mixed results on JOB-light with some improvements and some losses

Abstract

Join ordering is a key factor in query performance, yet traditional cost-based optimizers often produce sub-optimal plans due to inaccurate cardinality estimates in multi-predicate, multi-join queries. Existing alternatives such as learning-based optimizers and adaptive query processing improve accuracy but can suffer from high training costs, poor generalization, or integration challenges. We present an extension of OmniSketch - a probabilistic data structure combining count-min sketches and K-minwise hashing - to enable multi-join cardinality estimation without assuming uniformity and independence. Our approach introduces the OmniSketch join estimator, ensures sketch interoperability across tables, and provides an algorithm to process alpha-acyclic join graphs. Our experiments on SSB-skew and JOB-light show that OmniSketch-enhanced cost-based optimization can improve estimation accuracy and plan quality compared to DuckDB. For SSB-skew, we show intermediate result decreases up to 1,077x and execution time decreases up to 3.19x. For JOB-light, OmniSketch join cardinality estimation shows occasional individual improvements but largely suffers from a loss of witnesses due to unfavorable join graph shapes and large numbers of unique values in foreign key columns.