An Incremental Anytime Algorithm for Multi-Objective Query Optimization

13 Pages·2015·0.29 MB·English

An Incremental Anytime Algorithm
for Multi-Objective Query Optimization
∗
Immanuel Trummer and Christoph Koch
{firstname}.{lastname}@epfl.ch
ÉcolePolytechniqueFédéraledeLausanne
ABSTRACT Optimization (MOQO) judges query plans based on multi-
ple cost metrics such as monetary fees of execution (e.g., in
Query plans offer diverse tradeoffs between conflicting cost
cloudcomputing)andenergyconsumptioninadditiontoex-
metrics such as execution time, energy consumption, or ex-
ecutiontime[11,14,15]. Plansareassociatedwithcostvec-
ecution fees in a multi-objective scenario. It is convenient
torsinsteadofcostvaluesandthegoalistofindaplanwith
foruserstochoosethedesiredcosttradeoffinaninteractive
an optimal tradeoff between conflicting cost metrics. The
process,dynamicallyaddingconstraintsandfinallyselecting
optimal tradeoff is user-specific since different users might
the best plan based on a continuously refined visualization
havedifferentpriorities. PriorworkinMOQOassumesthat
of optimal cost tradeoffs. Multi-objective query optimiza-
usersselecttheoptimalcosttradeoffindirectlybyspecifying
tion (MOQO) algorithms must possess specific properties
weights and constraints prior to query optimization. User
to support such an interactive process: First, they must be
studies have however shown that users have generally trou-
anytime algorithms, generating multiple result plan sets of
bles to accurately express their preferences indirectly in a
increasing quality with low latency between consecutive re-
multi-objective scenario without having prior knowledge of
sults. Second,theymustbeincremental,meaningthatthey
theavailabletradeoffs[17]. Itismorenaturalforuserstose-
avoid regenerating query plans when being invoked several
lectthepreferredtradeoffoutofasetofalternativesandthis
timesforthesamequerybutwithslightlydifferentusercon-
procedure tends to lead users to better choices. We apply
straints. We present an incremental anytime algorithm for
those results from general multi-objective optimization to
MOQO, analyze its complexity and show that it offers an
MOQOandpostulatethatMOQOshouldbeaninteractive
attractive tradeoff between result update frequency, single
process (at least for queries with non-negligible execution
invocation time complexity, and amortized time over mul-
time)inwhichusersselectthequeryplanwithoptimalcost
tiple invocations. Those properties make it suitable to be
tradeoffoutofasetofalternatives. Thefollowingexamples
used within an interactive query optimization process. We
illustrate two out of many possible application scenarios.
evaluate the algorithm in comparison with prior work on
TPC-H queries; our implementation is based on the Post- Example 1. In cloud computing, there is a tradeoff be-
gres database management system. tween execution time and fees as buying more resources can
speed up execution. Users performing SQL processing in the
CategoriesandSubjectDescriptors cloudcanbenefitfromavisualizationofavailablecosttrade-
offs before they select a query plan for execution.
H.2.4 [Database Management]: Systems
Example 2. In approximate query processing, there is a
Keywords
tradeoff between execution time and result precision since
Query optimization; multi-objective; incremental; anytime sampling can be used to reduce execution time. Visualizing
available tradeoffs helps users to hand-tune the execution of
1. INTRODUCTION queriesthatprocesslargedatasetsorareexecutedfrequently.
Classicalqueryoptimizationconsidersonlyonecostmet- It is not necessary to make users aware of all alternative
ricforqueryplansandaimsatfindingaplanwithminimal query plans for their query. It is sufficient if users have
cost[12]. Thismodelisinsufficientforscenarioswheremul- an overview of the Pareto-Optimal plans; a plan is Pareto-
tiple cost metrics are of interest. Multi-Objective Query optimalifnoalternativeplanhasbettercostaccordingtoall
costmetricsatthesametime(thisdefinitionisslightlysim-
∗This work was supported by ERC grant 279804.
plified). For two or three cost metrics, the Pareto-optimal
plancosttradeoffscanbevisualizedasacurveorasasurface
in three-dimensional space. For higher number of cost met-
rics,userscouldsuccessivelyvisualizetheParetosurfacefor
different combinations of cost metrics or look at aggregates
(minimaandmaxima)forthedifferentcostmetrics. Having
anoverviewoftheavailablecosttradeoffs,userscandirectly
select the query plan which fits best to their priorities.
AnidealinteractiveMOQOoptimizerpresentsanoverview
of all Pareto-optimal cost tradeoffs quickly after receiving
Plan cost Anytime Incremental
e
m Cost bounds One-shot Memoryless
Ti
y
Monetary fees alit me
Qu Ti
(a) The optimizer quickly visualizes a first
approximation of Pareto-optimal query plan costs ult Run
s
e
R
Plan cost Time Invocation Number
e
m Cost bounds
(a) Anytime versus (b) Incremental versus
Ti one-shot algorithms memoryless algorithms
Monetary fees
Figure 2: Incremental anytime algorithms
(b) The approximation is continuously refined without
user interaction
plansetisgeneratedthatisguaranteedtoapproximatethe
Plan cost Pareto plan set with the target precision. An interactive
e
m Old bounds scenarioratherrequiresalgorithmsthatreturnseveralresult
Ti New bounds plansetsofincreasingapproximationprecisionwithhighfre-
quency (low waiting time between consecutive result sets).
Monetary fees Algorithmsthatcontinuouslyimproveresultqualityinstead
(c) The user can always adapt the optimization focus ofreturningonlyoneresultattheendofexecutionaregen-
by dragging bounds into a new position erally called anytime algorithms in contrast to one-shot al-
gorithms. Figure 2(a) illustrates the difference. A second
shortcoming of existing MOQO algorithms is that they are
Figure 1: Example interaction between user and in-
non-incremental: theycannotsystematicallyexploitresults
teractiveanytimeoptimizer: theuserselectsaquery
of prior invocations to speed up optimization for very simi-
plan by finally clicking on the desired cost tradeoff.
larinputproblems. Usersmightforinstanceadaptthecost
bounds several times when optimizing a single query which
changespartoftheinputfortheoptimizationalgorithm(the
theuserqueryasinput. Theproblemisthatthenumberof
bounds change while the query remains the same). Start-
Pareto plans might be extremely large already for medium-
ing optimization from scratch every time that this happens
sized queries and prior work has shown that calculating all
is inefficient since the same query plans might get regener-
Pareto-optimal plans is often not realistic within a reason-
ated several times. An interactive scenario rather requires
abletimeframe[14]. Thisleadstoapproximationalgorithms
an incremental algorithm that maintains state across sev-
for MOQO that quickly find a representative set of query
eral invocations for the same query to minimize redundant
plans whose cost vectors approximate the Pareto-optimal
computation. Figure 2(b) illustrates the difference between
costtradeoffswithagiventargetprecision[11,14]. Thereis
incremental and memoryless algorithms.
a tradeoff between optimization time and target precision;
The original scientific contribution of this paper is an in-
choosingafinertargetprecisionincreasesoptimizationtime.
crementalanytimealgorithmforMOQO.Thisalgorithmhas
Approximate MOQO can take several tens of seconds for
theanytimepropertysinceitgeneratesaroughapproxima-
TPC-H queries when choosing a rather fine-grained target
tionoftheParetoplansetquicklythatisrefinedinmultiple
precision which is inconvenient for an interactive interface.
steps, having low latency between consecutive refinements.
It is impossible to know which precision the user requires
Thealgorithmisincrementalsinceitmaintainsstateacross
to make his decision. It is also unclear how much time op-
consecutive invocations for the same query with different
timization will take for a given query and target precision
costbounds,therebyavoidingtoregeneratethesameplans.
sincethisdependsonthesizeoftheresultplansetwhichis
Hence our algorithm is suitable for interactive MOQO.
the output of optimization. The most natural approach is
We summarize the contributions of this paper:
thereforeaninterfacethatiterativelyrefinestheapproxima-
tion of the Pareto cost tradeoffs, while allowing continuous
user interaction; users may for instance interact with the 1. We present an incremental anytime algorithm for in-
MOQO optimizer by selecting a query plan for execution teractive MOQO; it continuously refines the approxi-
(therebyendingoptimization)orbysettingcostboundsfor mationofthePareto-optimalcosttradeoffsandavoids
different cost metrics (which can be exploited to speed up regenerating plans over multiple invocations.
optimization as bounds restrict the search space). Figure 1
illustratestheinteractionbetweenuserandoptimizer: query 2. We analyze the space complexity of that algorithm,
plans are evaluated according to the two cost metrics (ex- the time complexity of a single invocation, and the
ecution) time and monetary fees in the example, and plan amortized complexity of several invocations.
costs are represented as points in a two-dimensional space.
Existing approximation algorithms for MOQO [14] are 3. Weexperimentallyevaluateanimplementationofthat
however ill-suited to be used within such an interface for algorithm within the Postgres database management
several reasons. First, they require to specify a target pre- system comparing with non-incremental non-anytime
cision in advance and return results only once a full result MOQOalgorithms,usingTPC-Hqueriesastestcases.
The remainder of this paper is organized as follows. Sec- and others; we cover the same metrics as the generic ap-
tion 2 discusses related work. Section 3 introduces the for- proximation schemes that were discussed before [14].
mal model that is used throughout the remainder of the Anytime algorithms are algorithms whose result quality
paper. Section 4 discusses the incremental anytime algo- improvesgraduallyascomputationtimeprogresses[19];they
rithmforinteractiveMOQOindetail. Section5provescor- are often applied to computationally intensive problems in
rectnessofthealgorithm,analyzesitsspacecomplexity,the situationswherecomputationmightbeinterrupted. MOQO
time complexity of a single invocation, and the amortized is computationally intensive and user input may interrupt
time of several invocations. Section 6 contains experimen- the current optimization at any time in our interactive sce-
tal results for TPC-H queries; the presented algorithm was nario. Anytime algorithms have been proposed for query
implemented on top of the Postgres optimizer. processing [16, 8] while we use them for query optimiza-
tion. Chaudhuri motivated the development of anytime al-
2. RELATEDWORK gorithmsforclassicalqueryoptimization[6]. Wearguethat
anytimealgorithmsareevenmorebeneficialforMOQOdue
We discuss prior work solving similar problems as we do
to the higher computational cost and due to the additional
(MOQO) and prior work using similar algorithmic tech-
challenge of user interaction. Incremental algorithms avoid
niques as we do (work on anytime algorithms, incremental
redundant work when solving similar problem instances in
algorithms,andapproximationalgorithms). Classicalquery
consecutiveinvocations(e.g.,whencalculatingshortestpaths
optimization[12]judgesqueryplansbasedononlyonecost
forseveralgraphswithsimilarstructure[2]). Inourcasewe
metric. Single-objective query optimization algorithms are
solve many consecutive optimization problems for the same
not applicable to MOQO in the general case; a detailed ex-
querybutwithdifferentboundsanddifferentapproximation
planation can be found in prior work [7, 14]. MOQO algo-
precision. Bizarro et al. proposed an incremental algorithm
rithms were proposed in the context of the Mariposa sys-
for parametric query optimization [4]; plan cost depends
tem [13] where query plans are evaluated based on the two
on unknown parameters in their scenario and the optimizer
cost metrics execution time and execution fees: Papadim-
might have to optimize the same query for many different
itriou and Yannakakis propose a fully polynomial-time ap-
combinations of parameter values. Storing result plans to-
proximation scheme for MOQO [11]. Their algorithm com-
gether with the corresponding input parameters allows to
binespolynomialruntimewithformalapproximationguar-
bypass future optimizer invocations for similar parameter
antees but does not optimize join order (only the mapping
values. Parametric query optimization is related to MOQO
from query plan nodes to processing sites is optimized for
since both extend the problem model of classical query op-
a fixed join order) and is therefore not applicable to the
timization; parametric query optimization associates plans
query optimization problem addressed in this paper. It has
however with cost functions while MOQO associates plans
been shown that even single-objective query optimization
withcostvectors. MOQOalgorithmsareingeneralnotap-
cannot be approximated in polynomial time if join order is
plicableforparametricqueryoptimizationandviceversa,a
optimized [5]; those results apply to MOQO as well since
detailed discussion of the differences can be found in prior
MOQO is a generalization of single-objective query opti-
work [15]. Existing algorithms for multi-objective paramet-
mization. Ganguly et al. [7] described an algorithm for
ricqueryoptimization[15]areneitherincrementalnorany-
MOQObasedondynamicprogramming;thisalgorithmpro-
time algorithms. The algorithm presented in this paper is
duces the full set of Pareto-optimal cost tradeoffs but its
an approximation scheme [9]: it differs from an exhaustive
execution time can be excessive in practice [14]. In prior
algorithm since it does not guarantee to return the optimal
work [14], we proposed several approximation schemes for
result. It offers however formal worst-case guarantees on
MOQO. We assumed that users specify a preference func-
how far the quality of the produced result is from the opti-
tion in the form of weights and cost bounds prior to opti-
mum; thisdistinguishesouralgorithmfrompureheuristics.
mization;theoptimizersearchesforaplanmaximizingthat
ApproximationschemesforMOQOhavebeenproposedbe-
preference function. Specifying a preference function in ad-
fore [14] but they are neither anytime algorithms nor incre-
vanceishoweveroftendifficultforusers[17];thisiswhywe
mental. Ouralgorithmisiterativewhichconnectsittoother
assume now that users select their preferred query plan in
iterative query optimization algorithms [10, 14]. The algo-
an interactive process. Our prior algorithms are unsuitable
rithm proposed by Kossmann and Stocker [10] is however
to be used within an interactive process since they are not
onlyapplicabletosingle-objectivequeryoptimizationwhile
incremental, meaning that consecutive invocations for the
our prior iterative algorithm [14] is non-incremental, mean-
same query result in large amounts of redundant work, and
ingthatresultsgeneratedinprioriterationsarenotreused,
since they are not anytime algorithms, meaning that they
andthegoalwastominimizetotalqueryoptimizationtime
donotimproveresultprecisioninregularintervals. Wedis-
rather than the time between consecutive results.
cuss and justify the design constraints that an interactive
interfaceimposesontheoptimizationalgorithminmorede-
tailinSection4. Duringformalanalysis(seeSection5)and
3. MODEL
experimental evaluation (see Section 6), we use algorithms
as baseline that are very similar to the ones proposed in We model a Query as a set Q of tables that need to be
our prior work. Other MOQO algorithms are tailored to- joined. We use this simple query model to describe our
wards specific combinations of cost metrics [18, 1]; while algorithminSection4butweoutlineinSection4.3howthe
such special-purpose algorithms achieve good performance, algorithmcanbeextendedtosupportaricherquerymodel.
they break when taking into account additional cost met- AQueryPlan eitherscansasingletableoriscomposedout
rics[14]. Thealgorithmproposedinthispaperisapplicable of two Sub-Plans such that the result of those sub-plans is
for a broad range of plan cost metrics such as execution finally joined. We denote by p = p (cid:49) p the plan p that
1 2
time, energy consumption, monetary fees, result precision uses p and p as sub-plans and joins their results.
1 2
Queryplansareassociatedwithscalarcostvaluesinclas- The result is a α-approximate b-bounded Pareto plan set
sical query optimization [12]. As we consider multiple cost forqueryQ. DuringInteractive MOQO,manyapproximate
metricsinMOQO,eachplanisassociatedwithaCost Vec- boundedMOQOproblemsmayhavetobesolved,reflecting
tor instead of a cost value. We denote by c(p) ∈ Rl the gradually refined approximation precision and bounds that
+
costvectorassociatedwithplanp. Eachcomponentofthat may change due to user input.
vector represents the cost value according to one of the l Wefinallydiscusstheparametersusedinourformalanal-
metrics. Notethatcostvaluesarealwaysnon-negative. We ysis: n is the number of query tables, m the cardinality of
use boldface for vectors (e.g., c) to distinguish them from the biggest table in the data base. Parameter l is the num-
scalar values. The algorithm presented in Section 4 sup- ber of cost metrics. We treat n as variable while we treat
portsthesameclassofcostmetricsaspriorgenericapprox- m and l as constants during complexity analysis. Those
imation schemes [14]; this set includes for instance execu- assumptions are consistent with prior work on MOQO ap-
tion time, monetary execution fees, result precision, energy proximation algorithms [14].
consumption, or various measures of resource consumption
concerningsystemresourcessuchasbufferspace,numberof
cores,orIObandwidth. Theclassofsupportedcostmetrics
is characterized more thoroughly during formal analysis in 4. DESCRIPTIONOFALGORITHM
Section 5. The focus of this paper is on optimization and
WedescribetheIncrementalAnytimeMOQOAlgorithm,
not on costing; we do not provide our own cost formulas
shortIAMA,forinteractiveMOQOinthissection. Existing
but assume that cost models from prior work are used to
MOQO algorithms [14] assume that users specify a prefer-
estimate the cost of query plans.
ence function prior to optimization; the goal of optimiza-
Considering one cost metric, a query plan p is at least
1 tionistofindaplanthatoptimizesthispreferencefunction.
as good as another query plan p if the cost of p is lower
2 1 IAMA differs since users select the optimal query plan for
than or equivalent to the cost of p . With multiple cost
2 their query in an interactive process. IAMA consists of two
metrics,aplanp isatleastasgoodasp ifitscostislower
1 2 main parts: the main control loop and the incremental op-
than or equivalent to the cost of p according to each cost
2 timizer. Themaincontrolloophandlestheinteractionwith
metric; if this is the case then we say that p Dominates
1 the user and decides which part of the plan search space to
p and denote it by c(p ) (cid:22) c(p ). If p dominates p and
2 1 2 1 2 explore next. It uses the incremental optimizer as a sub-
p has lower cost than p according to at least one metric
1 2 function to generate fresh query plans. The incremental
then we say that p Strictly Dominates p and denote it by
1 2 optimizer generates query plans for the given query; it al-
c(p ) ≺ c(p ). Consider the set P of all possible plans for
1 2 lowstofocusplangenerationbyspecifyinganareaofinter-
a fixed query: we call each plan p∗ ∈ P Pareto-Optimal if
est within the plan cost space and to choose the resolution
there is no alternative plan p ∈ P such that c(p) ≺ c(p∗).
withwhichPareto-optimalcosttradeoffsareapproximated.
WecallthesetP∗ ⊆P aParetoPlanSet ifforeachpossible
Choosing a higher resolution yields more accurate results
plan p∈P there is a plan p∗ ∈P∗ with c(p∗)(cid:22)c(p). Note
whilechoosingalowerresolutionreducesoptimizationtime.
that several subsets of P can be Pareto plan sets.
Themaincontrolloopusestheoptimizertoincreaseapprox-
Full Pareto plan sets can be excessively large; this mo-
imation precision step by step for a given area in the cost
tivates to approximate the real Pareto set. Let α > 1 be
space which leads to the anytime behavior of IAMA.
the Precision Factor. Then each subset P∗ of P is an α-
α The incremental optimizer was designed with two perfor-
ApproximateParetoPlanSet ifforeachpossibleplanp∈P
mance constraints in mind. First, it must be incremental,
there is a plan p∗ ∈ P∗ such that c(p∗) (cid:22) αc(p). Each
α meaningthatitavoidsregeneratingthesamequeryplansin
Pareto plan set is an approximate Pareto plan set with fac-
consecutive invocations; this is important as the optimizer
tor α = 1. By multiplying the cost vector of plan p by a
is invoked many times for a given query while only the res-
factor greater than 1, we make its cost appear higher than
olution and the area of interest in the cost space change.
itactuallyis;thisreducestherequirementscomparedtothe
Second,thetimeofanysingleoptimizerinvocationmustbe
definitionoftheParetoplanset. Thehigherαischosen,the
proportional to the resolution and to the size of the cho-
lower the approximation precision and the smaller the cor-
sencostspacearea. Bothoptimizerpropertiesarecrucialto
responding approximate Pareto plan set can be. We derive
enableaninteractiveprocess: Iftheoptimizerwasnotincre-
size bounds in Section 5.
mental then each invocation would start from scratch and
Often, users care only about query plans whose cost is
generating a fine-grained approximation could easily take
upper-boundedforcertaincostmetrics. Usersmightforin-
several tens of seconds. Receiving user input during such
stancehaveadeadlinewhichimpliesanupperboundonex-
a long time is likely which only leaves the choice between
ecutiontime,oramonetarybudgetlimitingexecutioncost.
blockingtheinterfaceuntiloptimizationisfinished(leading
We model Cost Bounds by a cost vector b with the seman-
to poor user experience) or interrupting optimization with-
tics that users are only interested in plans p with c(p)(cid:22)b.
out being able to reuse any results (making it unlikely that
Ifc(p)(cid:22)bforsomeplanpthenwealsosaythatitRespects
high resolutions are ever reached). If invocation time was
the cost bounds while it otherwise Exceeds the bounds. If
not guaranteed to be at most proportional to the input pa-
a user specifies cost bounds b then he is interested in an
rameters then the interface might not be able to generate a
approximation of a subset of the Pareto plan set: an α-
first approximation of optimal cost tradeoffs quickly.
Approximate b-Bounded Pareto Plan Set is a subset P∗ of
The proposed optimizer algorithm satisfies both perfor-
thesetP ofpossibleplanssuchthatforeachplanp∈P with
manceconstraints. Itusesavariantoftheclassicaldynamic
αc(p)(cid:22)b there is a plan p∗ ∈P∗ such that c(p∗)(cid:22)αc(p).
programming approach to query optimization [12] and gen-
The input to the Approximate Bounded MOQO Problem is
erates optimal plans for joining table sets out of optimal
a query Q, an approximation factor α, and cost bounds b.
plansforjoiningsubsets. Asingle-objectiveoptimizerwould
store one cost-optimal plan per table set1 while the IAMA costofaresultplan. Twoplanswhosecostswereconsidered
optimizermighthavetostoremanyalternativequeryplans equivalent at resolution level 0 might not be equivalent any-
thatallrealizePareto-optimalcosttradeoffs. TheIAMAop- more at resolution level 1; such plans are inserted as result
timizer becomes incremental by maintaining two plan sets plans. Then the optimizer uses the freshly inserted result
acrossinvocations: theresultplansetandthecandidateplan scan plans to combine fresh plans for the entire query.
set. Both sets may contain completed query plans, joining Assumetheuserrelaxesthetightboundonmonetaryfees.
alltablesinthecurrentquery,aswellaspartialqueryplans, Now the resolution is reset to 0 in order to quickly generate
joining only a subset of tables. Result plans have already aroughapproximationofavailablecosttradeoffsforthenew
been verified to be crucial in order to approximate a spe- bounds. Theoptimizeronlyreconsiderscandidatescanplans
cific cost space area with a specific resolution. Candidate whose costs exceeded the previous bounds but no candidates
planshaveonlybeendeterminedtobepotentiallyusefulfor whose cost was considered equivalent to one of the result
a given cost space area and resolution. A future optimizer plans at resolution 0 or 1. Freshly inserted result plans are
invocation will decide whether they are relevant indeed. used to combine fresh plans for the entire query; the user
Bothplansetsareindexedbyplancostandbyresolution view is updated.
level. Using a data structure supporting multi-dimensional
Section4.1describesthemaincontrolloopandSection4.2
range queries allows to efficiently retrieve plans whose cost
discussesthepseudo-codeoftheincrementaloptimizer. Sec-
is within a certain range and which are registered for a cer-
tion 4.3 proposes finally several extensions.
tain range of resolution levels. Indexing plans by their cost
vectors enables the optimizer to focus on certain cost space 4.1 MainControlLoop
areas. Indexingcandidateplansbyresolutionallowstoavoid
Algorithm 1 is the main function of IAMA. Its input is a
checking relevance of the same candidate for the same res-
query and its output is the query plan that the user selects
olution twice. We will formally prove in Section 5 that the
forexecutioninaninteractiveprocess. Algorithm1contains
proposed algorithm indeed verifies relevance only once per
the main control loop from lines 12 to 25; each iteration of
resolutionandcandidateplan. Indexingresultplansbyres-
the main loop generates new query plans by invoking the
olutionisrequiredtoguaranteethatoptimizationtimeisal-
Optimizeprocedure(itsimplementationisdiscussedinthe
waysproportionaltothechosenresolution. Wheninserting
next subsection), visualizes their cost using the Visualize
newpartialcandidateplansduringanoptimizerinvocation,
procedure (we do not provide pseudo-code for this proce-
theyshouldforinstanceonlybecombinedwithresultplans
dure), and selects the focus for the next optimizer invoca-
that are registered for the current resolution level or lower.
tion, taking into account user input, if any.
Weillustratehowouralgorithmworksbymeansofahighly
Theoptimizationfocusisdescribedbythetwolocalvari-
simplified example before providing details.
ables b and r. Variable b is a vector of upper cost bounds
restricting the area of interest in the cost space. Variable
Example 3. We consider the two cost metrics execution
bisusedasparameterfortheoptimizerinvocationandthe
time and monetary fees (e.g., in a cloud scenario) and opti-
optimizer focuses on generating plans that respect the cost
mizethesimplequeryR(cid:49)S. Theuserselectsverytightcost
bounds (i.e., plans whose cost vector is dominated by b).
bounds on execution fees. The initial goal of the optimizer
The cost bounds are initialized to default values (this can
is to quickly produce a coarse-grained approximation of the
also be the value ∞, indicating that no bounds are set by
optimalcosttradeoffsforthequery. Therefore,optimization
default) and can be adapted by the user in each iteration
starts with the lowest possible resolution level 0.
of the main control loop. Adapting the bounds gives users
Theoptimizerstartsbyconsideringalternativesscanplans
theopportunitytofocusplansearchontherelevantpartof
forthetwosinglerelationsRandS.Iftheoptimizerencoun-
thecostspace,therebyspeedingupoptimization. Variabler
ters a scan plan whose cost exceed the user bounds then this
representstheresolutionwithwhichthePareto-optimalcost
plan is stored as candidate for later optimizer invocations
tradeoffs are approximated. At a low resolution, the op-
as it might become useful once the user changes the bounds.
timizer does not distinguish query plans with similar cost
If the optimizer encounters several plans for the same ta-
vectors and generates a relatively small set of representa-
blewhosecostareroughlycomparablethenonlyoneofthem
tive query plans. At a high resolution, the optimizer gener-
is stored as result plan while the others are stored as can-
ates more query plans and the approximation of the set of
didates; the other plans might become useful once the reso-
Pareto-optimalcosttradeoffsisthereforemorefine-grained.
lution is refined. In a second step, the optimizer combines
Assuming a two-dimensional visualization of cost vectors, a
result scan plans to form join plans answering the entire
high resolution translates into pixels representing alterna-
query. The optimizer separates result plans from candidate
tive cost tradeoffs being closer together while a low resolu-
plansinthesamefashionasbeforeandshowsthecostofthe
tion means that those pixels are far apart from each other
result plans to the user.
(see Figure 1 from Section 1 for an example: the resolution
Without user intervention, the resolution is increased to
increases from Figure 1(a) to Figure 1(b)). This motivates
1. Now the optimizer reconsiders some of the scan plans for
the use of the term resolution. We assume that a predeter-
R and S that were stored as candidates. The optimizer does
minednumberofresolutionlevelsisused;thevaluedomain
not reconsider candidate plans whose cost exceed the bounds
ofvariableristhesetofresolutionlevels{0,...,r }. Vari-
M
sincetheuserdidnotchangethem. Theoptimizerreconsid-
ablerisinitializedwiththelowestpossibleresolutionandis
erscandidateplanswhosecostwasroughlycomparabletothe
increased by one in each iteration of the main control loop
if no user input is received. If the user changes the cost
1Single-objective optimizers might still store several cost-
optimal plans for a table set if they produce different tuple bounds then the resolution is set to zero again. Gradually
orderings that might speed up following operations; we ne- increasing resolution allows to keep each optimizer invoca-
glect tuple orderings here to simplify the explanations. tion short (note that this reasoning is only valid since the
1: // Generate Pareto plan set of increasing resolution 1: // Generate plans for query Q, insert them into result
2: // for query Q until user selects query plan 2: // set Res if they are relevant for current resolution r
3: function IncAnyMOQO(Q) 3: //andboundsborinsertthemintocandidatesetCand
4: // Initialize bounds and resolution 4: // if they might become relevant later.
5: b← default bounds 5: procedure Optimize(Q,Res,Cand,b,r)
6: r←0 6: // Check candidate plans
7: // Fill in scan plans for single tables 7: for q⊆Q do
8: for q∈Q,p∈ScanPlans(q) do 8: for p ∈Candq[0..b,0..r] do
C
9: Prune(Resq,Candq,b,r,p) 9: Candq ←Candq\{p }
C
10: end for 10: Prune(Resq,Candq,b,r,p )
C
11: // Main control loop 11: end for
12: repeat 12: end for
13: // Generate more plans 13: // Generate plans using fresh candidates
14: Optimize(Q,Res,Cand,b,r) 14: for k←2 to |Q| do
15: // Visualize cost of known plans 15: for q⊆Q:|q|=k do
16: Visualize(ResQ[0..b,0..r]) 16: for q ⊂Q:q (cid:54)=∅;q ←Q\q do
1 1 2 1
17: // Update bounds or refine resolution 17: for pF ∈Fresh(Resq1,Resq2,b,r) do
18: if User changed bounds then 18: Prune(Resq,Candq,b,r,p )
F
19: b← user-specified bounds 19: end for
20: r←0 20: end for
21: else 21: end for
22: // Refine resolution until r is reached 22: end for
M
23: r←min(r ,r+1) 23: end procedure
M
24: end if Algorithm2: Incrementaloptimizationalgorithmformulti-
25: until User selects plan p objective query optimization.
26: // Return result plan
27: return p
28: end function
ploitthefactthatthenumberofplaninsertionsisbounded
Algorithm 1: Main function for interactive query optimiza-
for a fixed query while the number of retrieval operations
tion: processes user input, visualizes plan cost, and invokes
is not (see Section 5.4). Prioritizing fast retrieval over fast
incremental optimization procedure.
insertiontimesandselectingacorrespondingdatastructure
seems therefore advantageous.
Both sets, result plans and candidate plans, are initially
empty in each invocation of Algorithm 1. They are initial-
optimizerisincremental). Underthereasonableassumption
ized before the main control loop starts, by inserting plans
thatthetimeforoneiterationofthemainloopismainlyde-
for scanning single query tables using procedure Prune.
terminedbyoptimizationtime,theshortoptimizationtimes
The pruning procedure decides whether to insert plans into
lead to high iteration frequencies. This guarantees that the
the result or candidate set and its implementation will be
plan cost visualization is updated frequently and that the
discussed in the next subsection. New plans can get gen-
interface remains responsive. Resetting the resolution after
erated and inserted into Res and Cand in each invocation
aboundschangemakessurethatfirstresultsbecomevisible
of the Optimize procedure. Note that we assume call-by-
quicklyaftertheuseradaptsthecostspaceareaofinterest.
reference parameter passing such that the optimizer sub-
Variable Res stores the set of result plans and variable
function can alter the state of Res and Cand. Procedure
Cand the set of candidate plans. Both sets contain partial
Visualizevisualizes only cost tradeoffs of completed query
plans that join subsets of Q in addition to completed plans
planswhichrespectthecurrentcostboundsandareappro-
that join all tables in Q; we use the superscript notation to
priate for the current resolution; the input set to procedure
refer to subsets of plans that join specific table sets (e.g.,
Visualize is therefore the subset of completed query plans
Resq for q ⊆Q denotes the subset of result plans that join
described by ResQ[0..b,0..r].
table set q). Plans in both sets are also indexed by their
cost vectors and by the resolution at which they were in-
4.2 IncrementalOptimizer
serted (result set) or at which they should be considered
for insertion (candidate set). We refer to subsets of plans Algorithm 2 is the incremental optimizer procedure that
thatareassociatedwithaspecificresolutionrangeandcost is invoked in each iteration of the main loop (lines 12 to 25
range using square brackets: Resq[0..b,0..r] selects for in- in Algorithm 1). It obtains as input the current query Q,
stance all result plans that join table set q, were inserted the set of result and candidate plans (which it may alter),
at a resolution between 0 and r (both limits inclusive), and as well as cost bounds b and resolution r. After the opti-
whose cost is dominated by b. The analogous notation ap- mizer invocation, the result set is guaranteed to contain a
plies for candidate plans. Those plan selections correspond b-boundedapproximationoftheParetoplansetforqueryQ
to range queries in the space that is spanned by all of the withresolutionr. Thismayormaynotrequiretheoptimizer
plan cost metrics and by the resolution level as additional to insert new plans into the result set. As the optimizer is
dimension. A classic survey on data structures supporting incremental, it will only insert new plans in addition to the
range queries was compiled by Bentley and Friedman [3]. ones already contained in Res if this is required to satisfy
Different data structures offer different tradeoffs between the previously mentioned guarantee. The optimizer may
insertion and retrieval time. We will later prove and ex- also insert plans into the candidate plan set Cand, discard
1: // Insert plan p for query q into result set Res if p is
plansfromthecandidateset,orre-indexcandidateplansfor
2: // relevant for current resolution r and bounds b.
adifferentresolution. Thepurposeofthecandidatesetisto
3: // Insert p into candidate set Cand if p could
avoidredundantworkoverdifferentoptimizerinvocations: a
4: // become relevant later.
non-incrementalMOQOalgorithm[14]discardsqueryplans
5: procedure Prune(Resq,Candq,b,r,p)
that are not useful for the current invocation. IAMA keeps
6: // Compare p with alternative plans and bounds
themascandidateplansinsteadanddoesnotneedtoregen-
7: if ∃p ∈Resq[0..b,0..r]:c(p )(cid:22)α ·c(p) then
eratetheminlaterinvocations. Re-indexinganddiscarding A A r
8: // p approximates p for resolution r
candidate plans also minimizes redundant work: if it has A
9: // → keep p as candidate for higher resolutions
been verified during the current optimizer invocation that
10: if r<r then
a certain candidate plan is irrelevant for a given resolution M
11: Insert(Candq[c(p),r+1],p)
then it is not necessary to recheck that candidate plan for
12: end if
thesameresolutionagaininfutureinvocations. Re-indexing
13: else if c(p)(cid:14)b then
that candidate plan for a higher resolution makes sure that
14: // Cost of p exceeds the bounds
the knowledge gained in the current invocation (about the
15: // → keep p as candidate for different bounds
irrelevanceofthatcandidate)isnotlost. Ifcandidateplans
16: Insert(Candq[c(p),r],p)
are irrelevant even for the highest resolution then they can
17: else
be safely discarded.
18: // p is immediately relevant
Algorithm 2 consists of two phases. In the first phase
19: // → add p to result plan set
(lines 6 to 12), the optimizer reconsiders candidate plans
20: Insert(Resq[c(p),r],p)
that were generated in previous invocations. It iterates over
21: end if
alltablesubsetsofQinarbitraryorderandretrievesforeach
22: end procedure
set all candidate plans that are indexed for the current res-
olution and the current cost bounds. All considered plans 23: // Given two sets of sub-plans Resq1 and Resq2, filter
are deleted from the candidate set and pruned; the prun- 24: // to relevant plans for resolution r and bounds b and
ing procedure might insert them again as candidates but 25: // generate all fresh combinations of sub-plans.
for a higher resolution than the current one. The pruning 26: function Fresh(Resq1,Resq2,b,r)
procedure(whosepseudo-codeisdiscussedlater)mightalso 27: // Filter to relevant sub-plans
insertthemintotheresultplanset. ThesecondphaseofAl- 28: P1 ←Resq1[0..b,0..r]
gorithm2(lines13to22)generatesnewplansbycombining 29: P2 ←Resq2[0..b,0..r]
plansintheresultsets. Duringthatphase,theoptimizerit- 30: // Generate relevant sub-plan pairs
eratesovertablesetsofincreasingcardinality;foreachtable 31: pairs←∆P1×(P2\∆P2)
set, it considers all possible splits into two non-empty sub- 32: pairs←pairs∪((P1\∆P1)×∆P2)
sets. Foreachsplitofasetq intotwosubsetsq1 andq2,the 33: pairs←pairs∪(∆P1×∆P2)
optimizer considers combining a plan joining the tables in 34: // Generate fresh plans
q with one joining the tables in q to obtain a plan joining 35: fresh←∅
1 2
all tables in q. In contrast to classical query optimization 36: for (cid:104)p1,p2(cid:105)∈pairs:IsFresh(p1,p2) do
algorithms [12], the incremental optimizer does not com- 37: fresh←fresh∪{p1 (cid:49)p2}
bineallplansintheresultplansetsbutonlyconsidersfresh 38: end for
combinations of sub-plans that were not generated in prior 39: return fresh
optimizerinvocations. FunctionFresh(whosepseudo-code 40: end function
is discussed next) returns only such plans. Algorithm 3: Sub-functions of the optimization procedure.
Algorithm3showspseudo-codeforthepruningprocedure
PruneandforfunctionFreshgeneratingfreshqueryplans.
ProcedurePruneinsertsanewqueryplanintotheresultset
tion factor and growing resolution; as the time complexity
ifitscostvectorcannotbeapproximatedbyanyalternative
growsinthesizeoftheresultset,thecomplexitygrowswith
resultplanatthecurrentresolution. Weusetheexpression
increasing resolution as well. We calculate precise bounds
Insert(S,p) for some set S and a plan p as shortcut for
in Section 5. We also show in Section 5 that an invocation
S ←S∪{p}. Resolution levels r translate into an approxi-
of the optimizer function with resolution r yields an αn-
mationfactorα bywhichthecostvectorofthenewplanis r
r approximate Pareto plan set, where n=|Q| designates the
multipliedbeforeitiscomparedwiththecostvectorsofthe
number of tables in the query. The underlying reason is in-
alternativeplans(line7). Theapproximationfactorsα are
r tuitivelythateachpruningoperationmayintheworstcase
chosen such that α > 1 and α > α for all resolution
r r r+1 introduceanapproximationerrorthataccumulatesoverdif-
levels r; we demonstrate the effects of different choices for
ferentpruningoperations;thenumberofpruningoperations
thenumberofresolutionlevelsandapproximationfactorsin
for a single query plan is proportional to n. Knowing the
Section6. Scalingthecostvectorofthenewplanbyafactor
relationship between α and the result precision allows to
greaterthanonemakesitmorelikelythatthescaledvector r
choose the factors α such that a desired target precision
isdominatedbythecostvectorofoneofthealternativere- r
is still reached for the maximal expected number of tables;
sult plans, meaning that the new plan is not inserted into
alternatively,thechoiceofα canbeadaptedtothecurrent
theresultset;thenewplancanonlybeinsertedifitscostis r
query. If the scaled cost vector of the new plan is domi-
for each cost metric lower than the cost of any other result
nated at the current resolution, it is inserted as candidate
planbyfactorα atleast. Thehigherthefactorα ,theless
r r plan for higher resolutions or discarded if the maximal res-
likely it is that the new plan is inserted. This means that
olutionhasbeenreached. Ifthecostvectorofthenewplan
theresultplansettendstogrowwithshrinkingapproxima-
exceedsthecurrentbounds,itmaybecomeusefulagainonce
the bounds change; in that case the new plan is therefore but not the choice of join operators. Supporting different
inserted as candidate for the current resolution again. join operators just requires to add an inner loop iterating
Asdiscussedbefore,ourgoalistomakethetimecomplex- over all applicable join operators and creating correspond-
ity of one optimizer invocation proportional to the current ing plans in function Fresh (see Algorithm 3). Second,
resolution and cost bounds, independently from how many alternative operators might produce the same set of result
candidate and result plans have accumulated from prior in- tuples while some of them generate them in an order that
vocations. This goal leads to two subtle design decisions canbeexploitedbyfutureoperations. Thisiswhydynamic-
concerning the pruning function that are nevertheless cru- programmingbasedqueryoptimizersdistinguishplansgen-
cial in order to obtain the complexity properties we were eratingdifferentinterestingtupleorders [12]duringpruning;
aimingfor: First,thenewplanisonlycomparedtoalterna- thecost-basedplancomparisonisrestrictedtoplansgener-
tive plans that have been inserted at the current resolution ating similar tuple orders and it is straight-forward to gen-
level or at a lower one. The disadvantage is that we might eralize this principle to the multi-objective case. Third, the
insert the new plan even if plans that are preferable over presentedcodeisbasedonasimplequerymodel,represent-
the new plan were already inserted at a higher resolution; ing queries as sets of tables that need to be joined. Predi-
the advantage is however that the number of plan compar- cates and projections can be handled by applying them as
isonsisproportionaltothesizeoftheresultplansetatthe earlyaspossibleinthejointreeandtherequiredcodeexten-
current resolution. The second decision is that we do not sions are standard [12]. The seminal paper by Selinger [12]
discard result plans that are dominated by the new plan in describes how complex SQL statements containing nested
case that the new plan is inserted into the result set. This queries can be decomposed into simple select-project-join
differs from prior MOQO approximation schemes which al- query blocks that can be optimized by our algorithm.
wayskeeptheresultplansetsassmallaspossible[14]. The
reasonthatwedonotdiscardresultplansisthattheymight 5. FORMALANALYSIS
havebeenusedalreadyassub-planstocombineotherquery
We analyze the algorithm presented in Section 4: More
plans in prior invocations of the optimizer; this might have
precisely,weanalyzetheoptimizersub-functionthatisrep-
happened at the current resolution or at a higher one. Dis-
resented in Algorithm 2. Section 5.1 proves formal worst-
carding a result plan would require to discard at the same
caseguaranteesonhowcloselytheresultplansets,produced
timeallplansthatuseitassub-plantokeeptheresultplan
bytheoptimizer,approximatetherealParetoplanset. Sec-
sets for different table sets consistent (we also assume that
tion5.2analyzesspacecomplexityandSection5.3analyzes
plans are represented by pointers to their sub-plans as dis-
thetimecomplexityofasingleoptimizerinvocation. InSec-
cussed in Section 5.2); the number of plans to discard is
tion5.4,weanalyzetheamortizedtimecomplexityofseveral
not necessarily proportional to the size of the result plan
consecutive optimizer invocations for the same query.
setatthecurrentresolution. Werenouncediscardingresult
plans to keep the time complexity of the current optimizer 5.1 ResultPrecision
invocation proportional to the current resolution.
The following analysis is based on the Principle of Near-
FunctionFreshusesresultplansfortwotablesubsetsq
1 Optimality [14] (PONO) for MOQO which states that re-
and q to combine new plans that are fresh, i.e., they have
2 placing optimal sub-plans within a complete query plan by
not been generated in prior optimizer invocations. The ex-
near-optimal sub-plans still yields a near-optimal complete
pression∆S designatesforsomeplansetS asubsetofplans
plan for a broad class of cost metrics. The class of cost
that potentially were not yet combined with all other plans
metrics to which the PONO applies is characterized by the
indexed for the current resolution and cost range. During
AggregationFunction,i.e.bytherecursivefunctionthatcal-
invocation series in which the bounds are tightened while
culates the cost of a plan according to that metric out of
resolution is refined, we can include all plans that were in-
thecostofthetwosub-plans: thePONOappliestoallcost
sertedinthecurrentinvocationin∆S (insuchcasesweare
metricswhoseaggregationfunctioncanberepresentedusing
sure that all previously inserted plans respecting the cur-
a combination of the operators sum, maximum, minimum,
rent bounds were already combined with each other) and
and multiplication by a constant. This applies for instance
set∆S =S otherwise. Wecanuseauxiliarydatastructures to metrics such as energy consumption or execution time2.
thatindexplansbasedontheinvocationatwhichtheywere
The PONO has also been shown to apply for several other
inserted in combination with the index on cost and resolu-
metrics whose aggregation formulas do not fit into the lat-
tion;thisallowstoevaluatetheexpressions∆Sand(S\∆S)
ter scheme, such as failure resilience or result precision. A
efficiently. For each cross product between plan sets, we
formal definition of the PONO follows.
check first if one of the two operand sets is empty before
evaluating the entire expression. Predicate IsFresh evalu- Definition 1 (PONO). Letpbeaqueryplanwithsub-
ates to true for plans that were not yet combined in prior plansp1 andp2 andpickanarbitraryα≥1. Derivep∗ from
invocations; we can use a hash table to perform this check p by replacing p1 by p∗1 and p2 by p∗2. Then c(p∗1)(cid:22)αc(p1)
efficiently. Fresh plans are returned and will be pruned. and c(p∗2)(cid:22)αc(p2) together imply c(p∗)(cid:22)αc(p).
ThefollowingtheoremsarebasedonthePONO.Wealso
4.3 Extensions
assume Monotone Cost Aggregation, meaning that the cost
Thealgorithmpresentedinthelastsubsectionsissimpli- ofaplanmustbehigherorequaltothecostofitssub-plans
fied and does not possess several features that are standard according to each cost metric.
in query optimization. The code can easily be extended to
2Theenergyconsumptionofaplanisthesumoftheenergy
incorporate the features discussed next and the implemen- consumption of the sub-plans. The plan execution time is
tation used for our experiments in Section 6 supports them the maximum of the execution times of the sub-plans for
as well. First, the presented code only optimizes join order parallel execution, and the sum for sequential execution.
Theorem 1. Afterinvoking Optimizewithboundsband usedinthei-thinvocationbyb . Resolutionr=min r des-
i i i
resolution r for query Q, Resq[0..b,0..r] contains an α - ignatestheminimalresolutionusedoverallinvocationsand
r
approximateb-boundedParetoplansetforeachtableq∈Q. vector b dominates all used cost bounds: ∀i : b (cid:22) b. Re-
i
sultandcandidateplansetsarethevariableswithdominant
Proof. For each table q, all applicable scan plans are space consumption in IAMA. We upper-bound the number
generatedandprunedbeforethemainloopstarts. Letpbe of plans stored in those sets after all optimizer invocations
anarbitraryscanplanforanarbitrarytableq. Onceproce- to obtain the accumulated space complexity.
dure Optimize is invoked later for resolution r and bounds
Lemma 1. Let q be a set containing k tables. Then Resq
b, there are two possibilities for p: either p was inserted
contains O(kllogl (m)) result plans.
into the result plan set in prior invocations or it is not in αr
the result plan set at the start of the current invocation. If Proof. Thecostofaqueryplanjoiningktablesisasymp-
pwasnotinsertedbeforethenwemustmakesurethatitis toticallyboundedbyO(m2k)forabroadrangeofcostmet-
either inserted in the current invocation or not required to rics[14]. Givenanapproximationfactorα >1,thenumber
r
form an αr-approximate b-bounded Pareto plan set. of cost values in the interval [1,m2k] such that there are no
If p was not inserted before then it must be included in two cost values c and c with c ≤ α c and c ≤ α c is
1 2 1 r 2 2 r 1
Candq[0..b,0..r] unless it exceeds the bounds b or can be bounded by O(klog (m)). Generalizing to l dimensions,
approximated by an alternative plan. In both cases, p is the number of costαvrectors taken from [1,m2k]l such that
not required for an α -approximate b-bounded Pareto plan
r there are no two vectors c and c with c (cid:22) α c and
set. If p is however in Candq[0..b,0..r] at the start of the c (cid:22)α c isboundedbyO(k1llogl (2m)). The1pruninrg2func-
current invocation then procedure Optimize will retrieve 2 r 1 αr
tion of IAMA only inserts query plans into the result set if
and prune p; plan p will be inserted if it is required for an
theircostvectorscannotbeapproximatedbyanyotherplan
α -approximate b-bounded Pareto plan set.
r intheresultset,usingapproximationfactorα forthecom-
r
Theorem 2. Afterinvoking Optimizewithboundsband parison. Therefore, the result set for q can never contain
resolution r for query Q, Resq[0..b,0..r] contains an αk- twoplanswhosecostvectorscanmutuallyapproximateeach
r
approximate b-bounded Pareto plan set for each table subset other. Sotheboundonthenumberofcostvectorstranslates
q⊆Q with cardinality k=|q|. into a bound on the number of plans.
Proof. Theproofisaninductionoverthenumberofta-
Theprecedinglemmaexploitsanupperboundontheplan
bles k. Theorem 1 proves the induction start for k = 1.
cost derived from the number of joined tables. The cost
Assume that the inductional assumption has been proven
bounds additionally restrict the maximal number of result
for all k < K. Let q ⊆ Q be a set of K tables and p
planssinceplansareonlyinsertedintotheresultsetifthey
an arbitrary plan that joins those tables with αKc(p) (cid:22) b.
r respect the current bounds. We denote by rpt(k,b,r) the
Plan p must have two sub-plans p and p that each join
1 2 asymptoticupperboundonthenumberofresultplansjoin-
at most K −1 tables. Let q and q be the set of tables
1 2 ing a set of k tables when using bounds b and resolution r.
joinedbyp andp respectively. Weassumemonotonecost
1 2 Thenextlemmaderivesaboundonthenumberofcandidate
aggregation which implies αKc(p ) (cid:22) b and αKc(p ) (cid:22)
r 1 r 2 plans from the bound on the number of result plans.
b. The inductional assumption applies to p and p such
1 2
that Resq1[0..b,0..r] will contain a plan p∗1 with c(p∗1) (cid:22) Lemma 2. Candq contains O(2krpt2(k,b,r)) candidate
αrK−1c(p1) and Resq2[0..b,0..r] will contain a plan p∗2 with plans for a table set q with k tables.
c(p∗)(cid:22)αK−1c(p )aftertheoptimizerinvocation. Plansp∗
2 r 2 1
and p∗ can be combined into a plan p∗ that joins the same Proof. Eachcandidateplanforqisconstructedbycom-
2
tables as p and has similar cost according to the PONO: bining two result plans that join subsets of q. There are
c(p∗) (cid:22) αK−1c(p). Plan p∗ is generated either in the cur- O(2k) possibilities of splitting a set with k tables into two
r
rentoptimizerinvocationwithresolutionrandboundsbor subsets. Assume that q is split into two subsets q and q .
1 2
inoneofthepriorinvocations. Ifp∗ isgeneratedinthecur- The cardinalities of Resq1 and Resq2 are both bounded by
rentinvocationthenitisinsertedunlessanalternativeplan rpt(k,b,r)sincerptgrowsmonotonicallyinkand|q |,|q |<
1 2
p∗∗ withc(p∗∗)(cid:22)α c(p∗)(cid:22)αKc(p)isalreadyintheresult k. The number of possible splits times the number of sub-
r r
set. In that case the theorem holds. If p∗ was generated in plan combinations bounds the number of candidates.
prior invocations then it was either inserted into the result
set,oritwasalreadyprunedatresolutionr anditscosttoo Werefertotheasymptoticupperboundonthenumberof
similartooneoftheresultplans,oritwillbeprunedinthe candidateplanspertablesetbycpt(k,b,r)inthefollowing.
current iteration. In all cases the theorem holds. ThetotalspacecomplexityofIAMAisobtainedbysumming
thenumberofresultandcandidateplansoveralltablesets.
Knowing the relationship between the precision factors
αr and the approximation quality of the result plan sets Theorem 3. Theaccumulatedspaceconsumptionofsev-
allows to choose the factor α for the maximal resolution
rM eral optimizer invocations for an n-table query is in
in function of the desired target precision. O(3nrpt2(n,b,r)).
5.2 SpaceConsumption Proof. EachplancanberepresentedinO(1)space: scan
The optimizer (meaning procedure Optimize, see Algo- plans are represented by the ID of the table being scanned;
rithm 2) is called once per iteration of the main loop. We other plans can be represented by the IDs of the two sub-
analyzetheaccumulatedspaceconsumptionofseveralopti- plans generating the operands for the final join. Plan cost
mizer invocations for the same query. We denote the reso- vectorshaveconstantspaceconsumptionsincelistreatedas
lutionusedinthei-thinvocationbyr andthecostbounds aconstant(seeSection3). WeassumeO(l)=O(1)indexing
i
space overhead per plan which is true for many data struc- using the newly inserted result plans and prunes the gener-
turessupportingrangequeries,includingthecelldatastruc- atednewplans. Foronetablesetwithktables,thisrequires
ture[3]. Thenumberofcandidateplansdominatesthenum- again O(2krpt3(k,b,r)) time, using the complexity results
ber of result plans. Summing over all table sets we obtain for pruning and plan generation. Summing over all table
a space complexity of O((cid:80)n (cid:0)n(cid:1)cpt(k,b,r)). Using the sets yields a time complexity of O((cid:80)n (cid:0)n(cid:1)2krpt3(k,b,r))
k=1 k k=1 k
definition of cpt, considering that cpt(k,b,r) ≤ cpt(n,b,r) which is in O(3nrpt3(n,b,r)).
for k ≤ n, and exploiting (cid:80)n (cid:0)n(cid:1)2k = 3n yields the final
k=0 k
complexity. Thetimecomplexityofoneoptimizerinvocationonlyde-
pendsontheparameters(resolutionandcostbounds)ofthe
5.3 TimeofSingleOptimizerInvocation current invocation but not on parameters used for previous
We analyze the time complexity of a single optimizer in- invocations. This means that plans stored from previous
vocation for a query with n tables, for bounds b, and for iterations do not cause any time overhead.
resolution r. The following analysis is valid independently 5.4 AmortizedOptimizationTime
from which invocations of Optimize precede the analyzed
overSeveralOptimizerInvocations
invocation. WesimplifyandassumethatretrievingF plans
by a range query takes O(F) time. We can for instance use We analyzed the time complexity of a single optimizer
adatastructuresimilartothecelldatastructure,described invocationintheprecedingsubsection. Nowweanalyzethe
by Bentley and Friedmann [3]: we partition the resolution amortized time complexity of a large series of invocations
andplancostspaceintocells3,associatealistofplanswith for the same query. After each invocation, the optimizer
everycell,andmakethoselistsaccessibleviadirectlookup. keeps plans that could be relevant for future invocations,
Assumingsuitablecellsizesandplancostdistributionssuch thereby avoiding redundant computation. The amortized
that the number of empty cells as well as the number of time complexity of a series of invocation is therefore lower
plans in partially included cells is negligible for most range than the time complexity of a single invocation. Resolution
queries, retrieval is in O(F) time and single plan insertion r and bounds b vary between invocations while query Q is
in O(1). fixed. Weassumeaninvocationserieswherethe∆operator
infunctionFreshfiltersplanstotheonesthatwereinserted
Lemma 3. Invoking Prune for a plan joining k tables is in the current invocation (e.g., if the user keeps tightening
in O(rpt(k,b,r)) time. theboundsandtheresolutionisrefined). Thenextlemmata
bound the amount of redundant computation.
Proof. The pruning procedure retrieves all result plans
joining the same tables as the new plan if they respect the Lemma 5. Each possible plan is generated at most once.
bounds b and are indexed for resolution r or smaller. The
Proof. Scan plans are only generated before the main
number of plans is in O(rpt(k,b,r)) and so is the retrieval
loop of Algorithm 1 is entered; this code is executed only
time. The cost vector of the new plan is compared against
once per query. Other plans are only generated in function
the cost vectors of all retrieved plans. One comparison re-
Freshandweexplicitlymakesuretogenerateplansonlyfor
quires to compare l cost values but l is a constant (see Sec-
fresh sub-plan combinations using predicate IsFresh.
tion 3). Adding the new plan takes constant time.
Lemma 6. Each sub-plan pair is generated at most once.
Lemma 4. Invoking Fresh for two table sets with maxi-
mally k tables is in O(rpt2(k,b,r)) time.
Proof. Each possible plan is inserted at most once into
Proof. FunctionFreshiteratesoverpairsofresultplans.
theresultplansetsinceitisgeneratedatmostonce(accord-
The plans from those sets are combined pair-wise forming
ing to the previous lemma) and since each plan is removed
O(rpt2(k,b,r)) pairs. Constructing a new plan and calcu-
from the candidate set once it is inserted into the result
lating its cost from the cached cost of the sub-plans using
set. Assume that optimizer invocations are numbered and
recursive cost formulas is in O(1).
denote for two arbitrary plans p and p by i and i the
1 2 1 2
invocations at which they become result plans. Then the
Weusethepreviousresultstocalculatethetimecomplex-
corresponding plan pair can only be considered at invoca-
ity of the Optimize procedure.
tionmax(i ,i ): itcannotbeconsideredbeforesinceatleast
1 2
one of the plans is not in the result set at this point and it
Theorem 4. Invoking Optimize for a query with n ta-
cannotbeconsideredafterwardssincenoneofthetwoplans
bles is in O(3nrpt3(n,b,r)) time.
was freshly inserted at that time.
Proof. ThefirstpartoftheOptimizeprocedurechecks
which candidate plans have become relevant. For one ta- Candidate plans are considered for insertion (into the re-
ble set with k tables, this requires to retrieve and prune all sultset)inthefirstphaseoftheOptimizeprocedure. Each
candidate plans that respect bounds b and are marked as possible plan is only considered a limited number of times
potentially relevant for resolution r or smaller which takes according to the following lemma.
O(cpt(k,b,r)rpt(k,b,r)) = O(2krpt3(k,b,r)) time. The
Lemma 7. Eachgeneratedplanisretrievedatmostr +1
secondpartoftheOptimizeproceduregeneratesfreshplans M
times from the candidate plan set.
3We can use logarithmic partitioning for the cost space Proof. Eachretrievedcandidateplanisdeletedfromthe
which should lead to a more uniform distribution of plans
over cells since the area in the cost space that a result plan candidates and pruned. During pruning, the plan can be
approximately dominates is defined by multiplying its cost inserted as candidate again (and considered in future invo-
vector by a constant factor. cations). All considered plans respect the current bounds.