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

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.