[Experiment, Analysis, and Benchmark] Systematic Evaluation of Plan-based Adaptive Query Processing

TL;DR

This paper systematically evaluates plan-based adaptive query processing, revealing environment-specific performance sources and guiding future DBMS optimization strategies.

Contribution

It provides the first comprehensive analysis of plan-based AQP across different storage architectures, highlighting the distinct sources of performance improvements.

Findings

In on-disk DBMS, plan reordering yields most speedups.

In in-memory DBMS, cardinality refinement significantly improves performance.

Plan-based AQP outperforms state-of-the-art related methods.

Abstract

Unreliable cardinality estimation remains a critical performance bottleneck in database management systems (DBMSs). Adaptive Query Processing (AQP) strategies address this limitation by providing a more robust query execution mechanism. Specifically, plan-based AQP achieves this by incrementally refining cardinality using feedback from the execution of sub-plans. However, the actual reason behind the improvements of plan-based AQP, especially across different storage architectures (on-disk vs. in-memory DBMSs), remains unexplored. This paper presents the first comprehensive analysis of state-of-the-art plan-based AQP. We implement and evaluate this strategy on both on-disk and in-memory DBMSs across two benchmarks. Our key findings reveal that while plan-based AQP provides overall speedups in both environments, the sources of improvement differ significantly. In the on-disk DBMS, PostgreSQL, performance gains primarily come from the query plan reorderings, but not the cardinality updating mechanism; in fact, updating cardinalities introduces measurable overhead. Conversely, in the in-memory DBMS, DuckDB, cardinality refinement drives significant performance improvements for most queries. We also observe significant performance benefits of the plan-based AQP compared to a state-of-the-art related-based AQP method. These observations provide crucial insights for researchers on when and why plan-based AQP is effective, and ultimately guide database system developers on the tradeoffs between the implementation effort and performance improvements.