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

Publikation: Beitrag in FachzeitschriftForschungsartikelBeigetragen

Beitragende

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

OriginalspracheEnglisch
Seiten (von - bis)1-28
Seitenumfang28
FachzeitschriftProceedings of the ACM on Management of Data
Jahrgang3
Ausgabenummer1
PublikationsstatusVeröffentlicht - 10 Feb. 2025
Peer-Review-StatusNein

Externe IDs

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

Schlagworte

Forschungsprofillinien der TU Dresden

Fächergruppen, Lehr- und Forschungsbereiche, Fachgebiete nach Destatis