Rapid bushy join-order optimization with Cartesian products

  • Authors:

  • Bennet Vance;David Maier

  • Affiliations:

  • Oregon Graduate Institute of Science & Technology;Oregon Graduate Institute of Science & Technology

  • Venue:
  • SIGMOD '96 Proceedings of the 1996 ACM SIGMOD international conference on Management of data
  • Year:
  • 1996

Quantified Score

Hi-index 0.00

Visualization

Abstract

Query optimizers often limit the search space for join orderings, for example by excluding Cartesian products in subplans or by restricting plan trees to left-deep vines. Such exclusions are widely assumed to reduce optimization effort while minimally affecting plan quality. However, we show that searching the complete space of plans is more affordable than has been previously recognized, and that the common exclusions may be of little benefit.We start by presenting a Cartesian product optimizer that requires at most a few seconds of workstation time to search the space of bushy plans for products of up to 15 relations. Building on this result, we present a join-order optimizer that achieves a similar level of performance, and retains the ability to include Cartesian products in subplans wherever appropriate. The main contribution of the paper is in fully separating join-order enumeration from predicate analysis, and in showing that the former problem in particular can be solved swiftly by novel implementation techniques. A secondary contribution is to initiate a systematic approach to the benchmarking of join-order optimization, which we apply to the evaluation of our method.