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

2025-01-27

AI Summary

To address suboptimal query execution plans caused by cardinality estimation errors during optimization, this paper proposes the first method to detect such plans *in real time during optimization*—without requiring query execution or ground-truth cardinalities. Our approach comprises two core contributions: (1) a real-time detection mechanism based on *subplan ranking consistency*, breaking from conventional post-execution analysis paradigms; and (2) an *incremental proxy cardinality refinement framework* that continuously improves detection accuracy as the query workload evolves. The method integrates third-party cardinality estimators, subplan enumeration analysis, ranking consistency metrics, and online incremental learning. Evaluated on JOB-LIGHT-SCALE and STATS-CEB-SCALE benchmarks, our method achieves 88.7% accuracy in predicting suboptimal plans—significantly outperforming traditional post-execution error-based metrics.

Technology Category

• Search and Optimization: Learning to Search
• Reasoning under Uncertainty: Stochastic Optimization
• Planning, Routing, and Scheduling: Planning under Uncertainty

Application Category

• Search and Retrieval-Augmented AI: Web learning to rank, online learning, and counterfactual learning for ranking
• Graph Algorithms and Modeling for the Web: Representation, reconstruction, and subgraph or motif discovery in Web-related graphs
• User Modeling, Personalization and Recommendation: Attacks and countermeasures in recommendation systems

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.

Problem

• Query Optimization
• Real-time Detection
• Data Volume Uncertainty

Innovation

• PLANSIEVE
• Real-time Inefficient Plan Elimination
• Dynamic Query Optimization