Introduction

Wolfgang Lehner from the TU Dresden presented a lecture titled “An Elephant Under The Microscope” at the 8th session of the Database Research Lecture series at the Hasso-Plattner-Institute. He is a full professor and the current head of the Database Research Group at TU Dresden and has had long-term partnership and contributions to SAP’s HANA platform by contributing to to it’s architectural foundations.He also published seminal papers such as “SAP HANA Database: Data Management for Modern Business Applications”, “Normal Forms for Multidimensional Databases,” as well as the textbook “Web-Scale Data Management for the Cloud.”

An Elephant Under The Microscope (What does this abstract topic truly mean?)

The title “An Elephant Under The Microscope” narrows down to the idea of Reproducible Optimizer Research. The elephant represents the complex and large-scale nature of modern database systems, while the microscope symbolizes the detailed examination and analysis required to understand and optimize these systems effectively. The lecture focused the growing crisis in this field (that is reproducibility) and looking at the growing research into Cardinality Estimation and Machine Learning optimizers is going at an incredibly fast rate, the challenge to testing these ideas in real-world systems like PostgreSQL is becoming bigger for many researchers. He also emphasized that many research papers lack sufficient details to reproduce experiments/studies and that only a small fraction of papers provide enough information for others to reproduce their results, which limits progress in the field.

Steps in Query Processing:

When a query is executed in a database system, it goes through several steps to ensure efficient processing and retrieval of data.The steps involve parsing to logical query plan, access and integrity control, and now the step that matters most to us in this context i.e “Query Optimization” which involves two steps (Logical Plan Rewrite and Plan Transformation). Other steps after the optimization include plan parametrization, code generation, and execution.

Now let’s focus on the Query Optimization step:

Fig 1: Query_Optimizers

Logical Plan Rewrite:

The objective of this step is to transform the the initial logical query plan into an optimized logical query plan by applying a set of rewrite rules. A typical example of a this is filtering out unnecessary data early in the query execution process to reduce the amount of data that needs to be processed in subsequent steps.

Plan Transformation:

In this step, each logical operator is mapped to a physical operator to achieve the objective of converting the optimized logical query plan into a Query Execution Plan(QEP) that the database engine can actually run. This is usually cost-based and relies on 3 main components:

1. Plan Enumerator: This is responsible for generating different possible plan combinations.

2. Cost Model: Evaluates the plans to select the Best Query Plan by calculating the expected resource usage like (I/O, CPU, Memory) for each plan.

3. Cardinality Estimates: This then predicts how many rows will result from each operation in the query plan based on information from the Staistics Catalog.

Together these steps ensure the database chooses an efficient path to execute queries, optimizing performance and resource utilization.

The Implementation and Reproducibility Gap (From Idea To Implementation)

Fig 2: Idea to Implementation

As a researcher, implementing transitioning from research ideas to actual implementations can be quite a challenge especially navigating around the technical complexity such as developing a cardinality estimator for Postgres. There are currently 4 primary approaches to doing this however they have their downsides:

Fig 3: Components of a Query Optimizer

One of such approaches is implementing as a Postgres Extension. This makes use of Postgres’s powerful extension mechanism that allows code to be dynamically added to the server process. Researchers can use hooks like “planner_hook” for complete optimizatin or “join_search_hook” for join order optimization. However, as mentioned earlier, this approach has its downsides as there is currently no hook for the cardinality estimator or the cost model. This then forces researchers to have deep understanding of Postgres internals to find an alternative.

Another approach is implementation as a Postgres Patch. This involves the direct modification of the Postgres source code enable changes to the query optimizer and execution engine. This then allows for Adaptive Query Optimization to be achieved. This approach also has its downside as patches are specific to a minor release meaning a patch written for a 13.1 version of Postgres cannot be applied to a version 17.0 and this makes longterm reproducibility close to impossible.

One of the 4 approaches is using external tools like “pg_hint_plan” which allows embedding of optimizer decision as comments in SQL queries. A sample query might include a comment like this “/* + NestLoop(t mi) IndexScan(mi) */” to force the optimizer to use a specific join and scan path. However this method also its limitations as it is opaque and limited o available hints meaning it cannot provide cardinalities for base tables or manage parallel workers.

The last approach is the Artificial Lab setting which researcher can use to avoid the complexities of real databases by using a lab setting. In this setting, researchers usually measure “q-error”(the ratio between estimated and actual cardinality) instead of actual runtime. However there is no real world correlatation between the lab metrics and how well the query performs under load.

And this is where PostBOUND comes in:

The Solution (PostBOUND)

Fig 4: PostBOUND

What is PostBOUND? PostBOUND is a Python-based framework designed to specifically the said reproducibility gaps stated above. Unlike traditional methods that require deep C-level patching of Postgres kernel, PostBOUND allows researchers to prototype entirely in Python. Looking at the workflow, PostBOUND hooks into the database using a “hinting module” by generating a specific execution plan and forces the database to follow it using extensions like “pg_hint_plan” or the advanced “pg_lab”.

In the scenario that a researcher is studying only Join Ordering, he does not need to build a full optimizer as PostBOUND automatically provides “sensible defaults” for cardinality estimation and cost modelling to complete the pipeline.

Since PosBOUND is written in Python, it integrates seamlessly with modern ML ecosytems like Pandas or Scikit-learn making it more easier to test optimizers like MSCN or BAO without going encountering the version specific patch issues.

The end to end workflow of PostBOUND can be summarized in the following steps: ### Query Preparation -> Query Optimization(Fill the Gaps stage) -> Query Hinting -> Benchmarking -> Result Analysis.

PostBOUND does not only provide technical advantages but also has some non-funtional benefits such as;

1. Easy to use.
2. Provides a nice programming experience i.e (low boilerplate, high customizability and powerful abstractions).
3. Allows for fast onboarding as it provides many optimization algorithms out of the box.
4. Uses sensible defaults whenever possible therefore achieving low configuration overhead.
5. Good documentation.

While traditional methods usually focus on minimizing “q-error”, PostBOUND’s transparent evaluation showed a big disconnect that 36% of improvements in cardinality estimation actually resulted in worse execution runtimes. This allowed researchers to see that marginal improvements often trigger arbitrary and unstable plan jumps where the optimizer switches to a completely different plan that may not be optimal.

“Fill the Gaps” architecture of PostBOUND allowed researchers to leave out specific stages of the optimization pipeline leading to the first join (base join) is the most important decision in exactly 75% if queries.

PostBOUND’s advanced benchmarking allows for testing under real world “noise neighbor” conditions which revealed a plan thought to be perfect in isolation becomes higly inefficient under load.

Take aways and Conclusion

In conclusion, the core message from Professor Lehner is that while query optimization is a 50 year old black art, it still remains a frontier for modern research and the main target for the next generation of researchers is to move away from from isolated non-reproducible research towards a reproducible research artifact and tools like PostBOUND can help achieve this target.

References

Lehner, W. (2025). An Elephant Under the Microscope: On Reproducible Optimizer Research. HPI Lecture Series 2025/2026. Selinger, P., et al. (1979). Access Path Selection in a Relational Database Management System. (The foundational “System R” paper cited as the industry baseline) . Marcus, R., et al. (2021). BAO: Making Learned Query Optimization Practical. (Cited regarding learned models and plan selection) . Kipf, A., et al. (2019). Learned Cardinalities: Estimating Correlated Joins with Deep Learning (MSCN). Hertzschuch, A., et al. (2021). Simplicity Done Right for Join Ordering (UES/Pessimistic Core).