Hybrid Mixed Integer Linear Programming for Large-Scale Join Order Optimisation

TL;DR

This work tackles the challenge of large-scale join order optimisation, where NP-hardness makes exhaustive search infeasible for queries with many relations. It proposes a novel hybrid approach that uses a bushy-tree-capable MILP encoding within a template-driven framework, complemented by an adaptive method to efficiently handle lower-level decisions. The key contributions include the MILP encoding for arbitrary tree templates, a hybrid algorithm that assigns MILP to the most beneficial parts and uses adaptive techniques elsewhere, and extensive empirical validation showing robust performance up to 100 relations. The results demonstrate that the hybrid MILP method yields near-optimal plans across diverse benchmarks, offering a scalable and practical solver-based alternative for complex query optimisation, with open-source code provided for replication.

Abstract

Finding optimal join orders is among the most crucial steps to be performed by query optimisers. Though extensively studied in data management research, the problem remains far from solved: While query optimisers rely on exhaustive search methods to determine ideal solutions for small problems, such methods reach their limits once queries grow in size. Yet, large queries become increasingly common in real-world scenarios, and require suitable methods to generate efficient execution plans. While a variety of heuristics have been proposed for large-scale query optimisation, they suffer from degrading solution quality as queries grow in size, or feature highly sub-optimal worst-case behavior, as we will show. We propose a novel method based on the paradigm of mixed integer linear programming (MILP): By deriving a novel MILP model capable of optimising arbitrary bushy tree structures, we address the limitations of existing MILP methods for join ordering, and can rely on highly optimised MILP solvers to derive efficient tree structures that elude competing methods. To ensure optimisation efficiency, we embed our MILP method into a hybrid framework, which applies MILP solvers precisely where they provide the greatest advantage over competitors, while relying on more efficient methods for less complex optimisation steps. Thereby, our approach gracefully scales to extremely large query sizes joining up to 100 relations, and consistently achieves the most robust plan quality among a large variety of competing join ordering methods.

Figures (4)

• Figure 1: Illustrating our approach based on a tree template (a) featuring joins $i$, $j$, $k$, $l$, to join the four relations A, B, C and D. The template encompasses both a left-deep join tree (b), and a balanced join tree (c).
• Figure 2: Illustration of the optimisation steps performed by our hybrid MILP method. Relying on highly optimised MILP solvers, we determine efficient bushy tree structures in the upper tree levels using complete tree templates, with joins a, b, c, d and f selected by the optimiser, while e and g remain inactive. Continuing from the anchor joins d and f (colored in green), we rely on the efficient adaptive algorithm by Neumann and Radke Neumann.2018 for lower tree optimisation, yielding joins h-k.
• Figure 3: Normalised solution costs (relative to overall best solutions) for conventional benchmark queries. Range lines visualise minimal, average and maximal normalised costs over individual queries. Crosses with value N/A represent individual solutions with prohibitively large costs $\geq$ 20.
• Figure 4: Normalised solution costs (relative to overall best solutions) for tree queries joining increasing numbers of relations. Range lines visualise minimal, average and maximal normalised costs for a set of 100 problem instances. Crosses with value N/A (slightly jittered horizontally and vertically to resolve outlier count) represent individual solutions with prohibitively large costs $\geq$ 20, or scenarios where an algorithm failed to obtain any result within our 60s time limit.

Theorems & Definitions (7)

• Example 3.1
• Example 3.2
• Example 3.3
• Example 3.4
• Example 3.5
• Example 3.6
• Example 3.7