Jovis: A Visualization Tool for PostgreSQL Query Optimizer
Yoojin Choi, Juhee Han, Kyoseung Koo, Bongki Moon
TL;DR
The query optimizer's internal decision-making is opaque, hindering understanding and tuning of plan generation, especially given the $NP$-hard join-order search. It proposes Jovis, an interactive visualization tool that patches PostgreSQL to log optimizer decisions and provides visualizations for both the DP-based standard optimizer and GEQO, along with user-guided optimization via hints and prior results. Its contributions include end-to-end visualizations of DP and GEQO processes, an extensible backend that outputs structured JSON, and interfaces for hints and guided GEQO parameterization (e.g., $geqo\_seed$, $selection\_bias$). This enables learners and professionals to diagnose suboptimal plans, compare alternatives, and iteratively improve query performance, with a path toward broader DBMS support.
Abstract
Query optimizers are essential components of relational database management systems that directly impact query performance as they transform input queries into efficient execution plans. While users can obtain the final execution plan using the EXPLAIN command and leverage existing visualization tools for intuitive understanding, the internal decision-making processes of query optimizers are hidden from users, making it difficult to understand how the plan is constructed. To address this challenge, we present Jovis, an interactive visualization tool designed to explore the query optimization process in PostgreSQL. Jovis provides a comprehensive view of the entire optimization workflow through tailored visualization for each optimization strategy. It also includes features that allow users to participate in optimization by providing hints, tuning parameters, and reusing prior optimization results. Jovis serves as both an educational tool for learners and a practical resource for database professionals, helping users understand and improve query optimization by guiding the optimizer to make better decisions or consider previously unexplored plans. The source code, data, and/or other artifacts have been made available at https://github.com/orgs/snu-jovis.