An Elephant Under the Microscope: Analyzing the Interaction of Optimizer Components in PostgreSQL

Research output: Contribution to journalResearch articleContributed

Contributors

Abstract

Despite an ever-growing corpus of novel query optimization strategies, the interaction of the core components of query optimizers is still not well understood. This situation can be problematic for two main reasons: On the one hand, this may cause surprising results when two components influence each other in an unexpected way. On the other hand, this can lead to wasted effort in regard to both engineering and research, e.g., when an improvement for one component is dwarfed or entirely canceled out by problems of another component. Therefore, we argue that making improvements to a single optimization component requires a thorough understanding of how these changes might affect the other components. To achieve this understanding, we present results of a comprehensive experimental analysis of the interplay in the traditional optimizer architecture using the widely-used PostgreSQL system as prime representative. Our evaluation and analysis revisit the core building blocks of such an optimizer, i.e. per-column statistics, cardinality estimation, cost model, and plan generation. In particular, we analyze how these building blocks influence each other and how they react when faced with faulty input, such as imprecise cardinality estimates. Based on our results, we draw novel conclusions and make recommendations on how these should be taken into account.

Details

Original languageEnglish
Pages (from-to)1-28
Number of pages28
JournalProceedings of the ACM on Management of Data
Volume3
Issue number1
Publication statusPublished - 10 Feb 2025
Peer-reviewedNo

External IDs

Mendeley 5ede3a5c-f6f0-333f-9231-4118bc24ffdd

Keywords

Research priority areas of TU Dresden

DFG Classification of Subject Areas according to Review Boards

Subject groups, research areas, subject areas according to Destatis