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

2025-11-20

AI Summary

Unreliable cardinality estimation remains a critical performance bottleneck in DBMSs. This paper presents the first systematic comparison of plan-based adaptive query processing (AQP) across disk-resident (PostgreSQL) and main-memory (DuckDB) database systems, analyzing their distinct optimization mechanisms and practical benefits. We design an execution-feedback-driven experimental framework that supports subplan-level cardinality updates and dynamic plan rewriting, evaluated across multiple benchmark workloads. Our results reveal that performance gains in PostgreSQL stem primarily from plan reordering rather than cardinality correction, whereas DuckDB achieves substantial speedups through precise cardinality propagation. The former is constrained by the overhead of cardinality updates, while the latter avoids this cost entirely and attains superior adaptivity. Consequently, this work challenges the prevailing assumption that cardinality refinement is the primary source of AQP benefit, and instead establishes architecture-dependent optimization pathways and their respective applicability boundaries.

Technology Category

• Data Mining & Knowledge Management: Intelligent Query Processing
• Search and Optimization: Sampling/Simulation-based Search
• Multiagent Systems: Multiagent Planning

Application Category

• Graph Algorithms and Modeling for the Web: Querying, indexing, and retrieval in Web-related graphs
• Search and Retrieval-Augmented AI: Web learning to rank, online learning, and counterfactual learning for ranking
• User Modeling, Personalization and Recommendation: Attacks and countermeasures in recommendation systems

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.

Problem

• Evaluates plan-based adaptive query processing for unreliable cardinality estimation
• Analyzes performance differences between on-disk and in-memory database systems
• Compares plan-based AQP effectiveness against related-based AQP methods

Innovation

• Plan-based AQP refines cardinality using sub-plan feedback
• Evaluates AQP across on-disk and in-memory DBMS architectures
• Identifies different performance improvement sources per storage type