Towards a Unified Query Plan Representation

TL;DR

Serialized query plans vary across DBMSs, hindering plan-based testing. The authors perform a nine-DBMS exploratory study to identify a common basis—operations, properties, and formats—and introduce UPlan, a reusable library with converters to unify plans for DBMS-agnostic testing. They re-implement QPG and CERT on top of the unified representation, uncovering 17 new bugs and enabling cross-DBMS visualization and benchmarking. The work provides a practical path toward broader adoption of plan-based testing and may inform future standardization efforts and deeper cross-DBMS optimization insights.

Abstract

In database systems, a query plan is a series of concrete internal steps to execute a query. Multiple testing approaches utilize query plans for finding bugs. However, query plans are represented in a database-specific manner, so implementing these testing approaches requires a non-trivial effort, hindering their adoption. We envision that a unified query plan representation can facilitate the implementation of these approaches. In this paper, we present an exploratory case study to investigate query plan representations in nine widely-used database systems. Our study shows that query plan representations consist of three conceptual components: operations, properties, and formats, which enable us to design a unified query plan representation. Based on it, existing testing methods can be efficiently adopted, finding 17 previously unknown and unique bugs. Additionally, the unified query plan representation can facilitate other applications. Existing visualization tools can support multiple database systems based on the unified query plan representation with moderate implementation effort, and comparing unified query plans across database systems provides actionable insights to improve their performance. We expect that the unified query plan representation will enable the exploration of additional application scenarios.

Figures (4)

• Figure 1: An example query plan of the Neo4j operations of the Join category.
• Figure 2: The architecture of using UPlan for QPG and CERT. Grey font means the implementation without UPlan, and $\checkmark$ refers to the implementation with UPlan. QPG/CERT stops when observing an unexpected result or query plan.
• Figure 3: Visualized unified query plans of query 1 from TPC-H benchmark.
• Figure 4: Variance of the number of Producer operations for each query in TPC-H benchmark across five DBMSs.