SPQO: Learning to Safely Reuse Cached Plans for Dynamic Workloads

Learning-based Parametric Query Optimization (PQO) methods excel in static workloads with precise cached plan selection but struggle with dynamic workloads. When facing a parametric query outside query parameter distribution in its training workload, suboptimal plan could be selected and this plan would be unsafely reused. The root cause of this limitation is the plan selection model cannot adapt to distribution drifts in query parameters. In order to extend learning-based Parametric Query Optimization for safely reusing cached plans in dynamic workloads, we introduce a novel approach to predict and avoid reusing a suboptimal plan, referred to as SPQO. As each cached plan has specific reuse decision boundary, each cached plan is assigned to an independent binary classifier. In the offline phase, we employ an under-sampling algorithm integrated with Tomek Links technique to effectively train these classifiers under class imbalance setting. During the online phase, we implement hybrid adjustment strategies based on incremental learning, continuously training these classifiers with each prediction and query feedback. Our experiments show SPQO can reduce the 95th percentile relative query latency by \(10\times \) in static and \(10^3\times \) in dynamic workloads, and achieve better cache hit rates on various workloads.