Intermediate Relation Size Bounds for Select-Project-Join-Union Query Plans

TL;DR

This paper introduces a new measure called intermediate degree for SPJU query plans, and provides an algorithm to optimize plans to minimize this measure, improving understanding of intermediate relation sizes during query execution.

Contribution

It presents an algorithm to compute semantically equivalent plans with minimal intermediate degree under unary key constraints, advancing query plan optimization techniques.

Findings

Algorithm effectively minimizes intermediate degree for given plans.

Complete understanding of intermediate relation size bounds achieved.

Optimization respects unary key constraints in plan transformations.

Abstract

We study the problem of statically optimizing select-project-join-union (SPJU) plans where unary key constraints are allowed. A natural measure of a plan, which we call the output degree and which has been studied previously, is the minimum degree of a polynomial bounding the plan's output relation, as a function of the input database's maximum relation size. This measure is, by definition, invariant under passing from a plan to another plan that is semantically equivalent to the first. In this article, we consider a plan measure which we call the intermediate degree; this measure is defined to be the minimum degree of a polynomial bounding the size of all intermediate relations computed during a plan's execution -- again, as a function of the input database's maximum relation size. We present an algorithm that, given an SPJU plan $p$ and a set $\Sigma$ of unary keys, computes an SPJU plan $p'$ that is semantically equivalent to $p$ (over databases satisfying $\Sigma$) and that has the minimum intermediate degree over all such semantically equivalent plans. For the types of plans considered, we thus obtain a complete and effective understanding of intermediate degree.