Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

PARQO: Penalty-Aware Robust Plan Selection in Query Optimization

Haibo Xiu, Pankaj K. Agarwal, Jun Yang

TL;DR

This work tackles plan selection under uncertain selectivity estimates by introducing PARQO, a penalty-aware robust optimization framework that minimizes the expected penalty relative to the true optimum. It constructs workload-informed error profiles via querylets, uses Sobol’s global sensitivity analysis to identify a small set of actionable, interpretable dimensions, and searches for robust plans within that subspace. The approach is designed to work with existing optimizers (e.g., PostgreSQL) and to be accelerated through parametric query optimization by caching robustness results across query templates. Empirical results on JOB, DSB, and STATS show that PARQO yields substantial execution-time improvements, robust performance across a range of error scenarios, and reduced optimization overhead when deployed in PQO settings. The release is open-source, and the framework offers a practical path toward broader adoption of robust plan selection in real systems.

Abstract

The effectiveness of a query optimizer relies on the accuracy of selectivity estimates. The execution plan generated by the optimizer can be extremely poor in reality due to uncertainty in these estimates. This paper presents PARQO (Penalty-Aware Robust Plan Selection in Query Optimization), a novel system where users can define powerful robustness metrics that assess the expected penalty of a plan with respect to true optimal plans under uncertain selectivity estimates. PARQO uses workload-informed profiling to build error models, and employs principled sensitivity analysis techniques to identify human-interpretable selectivity dimensions with the largest impact on penalty. Experiments on three benchmarks demonstrate that PARQO finds robust, performant plans, and enables efficient and effective parametric optimization.

PARQO: Penalty-Aware Robust Plan Selection in Query Optimization

TL;DR

This work tackles plan selection under uncertain selectivity estimates by introducing PARQO, a penalty-aware robust optimization framework that minimizes the expected penalty relative to the true optimum. It constructs workload-informed error profiles via querylets, uses Sobol’s global sensitivity analysis to identify a small set of actionable, interpretable dimensions, and searches for robust plans within that subspace. The approach is designed to work with existing optimizers (e.g., PostgreSQL) and to be accelerated through parametric query optimization by caching robustness results across query templates. Empirical results on JOB, DSB, and STATS show that PARQO yields substantial execution-time improvements, robust performance across a range of error scenarios, and reduced optimization overhead when deployed in PQO settings. The release is open-source, and the framework offers a practical path toward broader adoption of robust plan selection in real systems.

Abstract

The effectiveness of a query optimizer relies on the accuracy of selectivity estimates. The execution plan generated by the optimizer can be extremely poor in reality due to uncertainty in these estimates. This paper presents PARQO (Penalty-Aware Robust Plan Selection in Query Optimization), a novel system where users can define powerful robustness metrics that assess the expected penalty of a plan with respect to true optimal plans under uncertain selectivity estimates. PARQO uses workload-informed profiling to build error models, and employs principled sensitivity analysis techniques to identify human-interpretable selectivity dimensions with the largest impact on penalty. Experiments on three benchmarks demonstrate that PARQO finds robust, performant plans, and enables efficient and effective parametric optimization.
Paper Structure (33 sections, 4 equations, 12 figures, 1 table)

This paper contains 33 sections, 4 equations, 12 figures, 1 table.

Table of Contents

  1. Introduction
  2. PARQO Framework
  3. Preliminaries and Problem Statement
  4. Remarks
  5. System Overview and Paper Outline
  6. Error Profiling
  7. Querylets
  8. Relevant Dimensions and Error Distributions
  9. Discussion
  10. A Note on Recentering
  11. Sensitivity Analysis
  12. From Local to Global Analysis
  13. Sobol's Method
  14. Sensitive Dimensions as Tuning Hints
  15. Finding Robust Plans
  16. ...and 18 more sections

Figures (12)

  • Figure 1: Actual execution times for different plans selected by various methods on JOB. (a) and (b) separate queries for which PARQO-Sobol outperforms or underperforms PostgreSQL; queries for which they perform similarly (9 out of 33) are omitted.
  • Figure 2: Cumulative density of cost penalty incurred by plans for Q2, 17, 26 and 15 in JOB.
  • Figure 3: Actual execution times of JOB queries on multiple instances by time-slicing IMDB. DB5 is the base instance.
  • Figure 4: Comparison of error profiling methods.
  • Figure 5: Improvements by correcting estimates for sensitive dimensions.
  • ...and 7 more figures

Theorems & Definitions (1)

  • Example 1