Progressive Parametric Query Optimization

Question

1. What are the contributions in "Progressive parametric query optimization" ?

2. What is the recent technique to solve the PQO problem?

3. What are examples of parameters that can be used to model the parametric optimization problem?

4. Why do selectivity-based representations tend to cluster together?

Accepted Answer

In this paper, the authors propose instead to progressively explore the parameter space and build a parametric plan during several executions of the same query.. The authors introduce algorithms that, as parametric plans are populated, are able to frequently bypass the optimizer but still execute optimal or near-optimal plans.

Accepted Answer

AniPQO [9] is a recent technique that approximates the solution to PQO for nonlinear functions and for an arbitrary number of parameters.

Accepted Answer

Examples of parameter values are system parameters (e.g., available memory) and query-dependant parameters (e.g., constants in parametric predicates).

Accepted Answer

The reason is that selectivity-based representations are better aligned to the optimizer cost model and tend to be represented by monotonic cost functions, and therefore, the regions of optimality of plans tend to cluster together.

Accepted Answer

Due to the size of the parameter space, PPs should not be implemented as exact lookup caches of plans because there would be too many “cache misses.”

Accepted Answer

Ganguly [5] uses a geometric approach to solve the PQO problem for one and two parameters under the assumption that cost functions are linear and that regions of optimality of plans are convex.

Accepted Answer

The region of optimality for plan p, denoted rðpÞ, is defined asrðpÞ ¼ ðt1; . . . ; tnÞ j p is optimal at ðc1 ¼ t1; . . . ; cn ¼ tnÞf g:Finally, a parametric optimal set of plans (POSP) is a minimal subset of MPSP that includes at least one optimal plan for each point in the parameter space.

Accepted Answer

for queries 7, 8, 9, and 21, the most suboptimal plan returned by Bounded was, respectively, 155 percent, 499 percent, 172 percent, and 177 percent the cost of the corresponding optimal plan.

Accepted Answer

For technical reasons, the authors did not implement function ’ ourselves but instead used SQL Server’s cost model to transform value into cost parameters.

Accepted Answer

How to enumerate dynamic query plans was proposed only later [2] with the concept of incomparability of costs: in the presence of unbound parameters at optimization time, plan costs are represented as intervals, and if intervals of alternative plans overlap, none is pruned.

Accepted Answer

Note also that the lower the values of M and A, the less likely it is to find pairs of triples that fulfill the requirements of Theorem 2, and thus, more added triples are needed to obtain higher hit rates.

Accepted Answer

On the other hand, for Optimize-Once, query order had avery significant impact on OptRate, with final values ranging from 3 percent to 48 percent.

Accepted Answer

If that first plan is also the plan with less cost variation in the plan space, then there is a high chance that that plan will do well in many other points in the space.

Progressive Parametric Query Optimization

Chat with Paper

AI Agents for this Paper

Most frequently asked questions

1. What are the contributions in "Progressive parametric query optimization" ?

2. What is the recent technique to solve the PQO problem?

3. What are examples of parameters that can be used to model the parametric optimization problem?

4. Why do selectivity-based representations tend to cluster together?

5. Why should a PP be implemented as a lookup cache?

6. What is the way to solve the PQO problem?

7. What is the definition of a parametric optimal set of plans?

8. How many percent of the cost of the optimal plan was returned by Bounded?

9. Why did the authors not implement function ’ ourselves?

10. What is the concept of incomparability of costs?

11. What is the effect of adding more triples?

12. What is the effect of query order on the value of the corresponding optimal plan?

13. What is the probability that the first plan will do well in other points?

Figures

Citations

Variance aware optimization of parameterized queries

Multi-objective parametric query optimization

Plan bouquets: query processing without selectivity estimation

Rule-Based Extendable Query Optimizer

Database calculation engine

References

Efficient mid-query re-optimization of sub-optimal query execution plans

Optimization of dynamic query evaluation plans

Index selection in a database system

Parametric query optimization

Method and apparatus for exploiting statistics on query expressions for optimization

Related Papers (5)

Parametric query optimization

Efficient mid-query re-optimization of sub-optimal query execution plans

Towards a robust query optimizer: a principled and practical approach

Access path selection in a relational database management system

Parametric query optimization for linear and piecewise linear cost functions