PLANSIEVE: Real-time Suboptimal Query Plan Detection Through Incremental Refinements

TL;DR

PLANSIEVE is a real-time framework that detects sub-optimal query plans during optimization by analyzing and incrementally refining cardinality estimates, significantly improving plan quality prediction accuracy.

Contribution

It introduces a novel real-time detection method that refines cardinality estimates during query optimization, unlike previous post-execution analysis approaches.

Findings

Achieves up to 88.7% accuracy in predicting sub-optimal plans.

Operates with surrogate cardinalities from third-party estimators.

Effectively refines estimates during query processing.

Abstract

Cardinality estimation remains a fundamental challenge in query optimization, often resulting in sub-optimal execution plans and degraded performance. While errors in cardinality estimation are inevitable, existing methods for identifying sub-optimal plans -- such as metrics like Q-error, P-error, or L1-error -- are limited to post-execution analysis, requiring complete knowledge of true cardinalities and failing to prevent the execution of sub-optimal plans in real-time. This paper introduces PLANSIEVE, a novel framework that identifies sub-optimal plans during query optimization. PLANSIEVE operates by analyzing the relative order of sub-plans generated by the optimizer based on estimated and true cardinalities. It begins with surrogate cardinalities from any third-party estimator and incrementally refines these surrogates as the system processes more queries. Experimental results on the augmented JOB-LIGHT-SCALE and STATS-CEB-SCALE workloads demonstrate that PLANSIEVE achieves an accuracy of up to 88.7\% in predicting sub-optimal plans.