Simpli-Squared: A Very Simple Yet Unexpectedly Powerful Join Ordering Algorithm Without Cardinality Estimates

TL;DR

Simpli-Squared is a simple join ordering algorithm that forgoes cardinality estimates, yet achieves performance close to complex methods, questioning the necessity of detailed statistics in query optimization.

Contribution

Introduces Simpli-Squared, a lightweight join ordering method that relies only on schema constraints and table sizes, avoiding complex statistics and correlations.

Findings

Achieves up to 16% performance increase over complex methods

Requires significantly less overhead to implement and maintain

Challenges the importance of cardinality estimates in join optimization

Abstract

The Join Order Benchmark (JOB) has become the de facto standard to assess the performance of relational database query optimizers due to its complexity and completeness. In order to compute the optimal execution plan -- join order -- existing solutions employ extensive data synopses and correlations -- functional dependencies -- between table attributes. These structures incur significant overhead to design, build, and maintain. In this paper, we present \textit{Simpli}city \textit{Simpli}fied (\textit{Simpli-Squared}), a very simple join ordering algorithm that achieves unexpectedly good results. Simpli-Squared computes the join order without using any statistics or cardinality estimates. It takes as input only the referential integrity constraints declared at schema definition and the number of tuples (size) in the base tables. The join order of a given query is computed by splitting the join graph along the many-to-many joins and sorting the tables based on their size. The tables involved in one-to-many joins are greedily included based on size and the query join graph. The resulting plan can be efficiently generated by a lightweight query rewriting procedure. Experiments on the JOB benchmark in PostgreSQL show that Simpli-Squared achieves runtimes having an increase of only up to 16\% -- and sometimes even a reduction -- compared to four state-of-the-art solutions that are considerably more intricate. Based on these results, we question whether JOB adequately tests query optimizers or if accurate cardinality estimation is such a fundamental requirement for performing well on the JOB benchmark.