The DuckDB Explain Visualizer is now available!

We are happy to announce the release of the DuckDB Explain Visualizer! It is a web-based tool that allows you to visualize the query plans of DuckDB. The DuckDB Explain Visualizer is available here: https://db.cs.uni-tuebingen.de/explain.

What is the DuckDB Explain Visualizer?

If you spend any significant amount of time looking at DuckDB query plans, sooner or later you will realize that the textual representation of the query plan has its limitations. It can be hard to read and understand, especially for complex plans. And worse, if the plan is too large, it can be cut off, making it impossible to understand the full plan. Also, there are no visual cues to help you understand the plan, find bottlenecks, or identify optimization opportunities.

Luckily, the DuckDB team has added an alternative way to represent query plans: As a JSON object, which can be visualized in a more user-friendly way. This is similar to the EXPLAIN (FORMAT JSON) feature in PostgreSQL. We leverage the JSON representation to render the query plan in a much more visually appealing fashion, interactive, and with additional information (e.g. slowest operator or the number of rows processed by each operator).

One of the main features of the DuckDB Explain Visualizer is the ability to share query plans with others. Once you have uploaded a query plan, you can share the link, and others can view the query plan without having to upload it again. We believe this feature will be especially useful for teaching, debugging, and collaboration in general.

How to use the DuckDB Explain Visualizer?

To use the DuckDB Explain Visualizer, you need to have a DuckDB query plan in JSON format. You can obtain this by running a query prefixed with EXPLAIN (FORMAT JSON). Then, you can paste the JSON output into the DuckDB Explain Visualizer to visualize the query plan. You can also use the ANALYZE option to include runtime statistics in the query plan.

1EXPLAIN (ANALYZE, FORMAT JSON)
2WITH RECURSIVE t(x) AS (
3  SELECT 1
4    UNION ALL
5  SELECT x + 1 FROM t WHERE x < 100
6)
7SELECT * FROM t;

Alternatively, you can use PRAGMA commands to enable profiling and save the output to a file. This is useful for long-running queries or queries that you want to analyze later. The output file can then be uploaded to the DuckDB Explain Visualizer. The following commands enable profiling and save the output to a file:

1PRAGMA enable_profiling = 'json';
2PRAGMA profiling_output = '/path/to/file.json'; -- (optional)
3[your-query-here]

How did we build the DuckDB Explain Visualizer?

The basis for the DuckDB Explain Visualizer is the excellent PostgreSQL Explain Visualizer (PEV) by Alex Tatiyants. This tool has been re-implemented a few years ago as PostgreSQL Explain Visualizer 2 (PEV2). Both PEV and PEV2 are web-based tools that allows users to visualize query execution plans of PostgreSQL. Exactly what we want—and need—for DuckDB! The source code of the DuckDB Explain Visualizer is available on GitHub.

Credits

During Matthis Noël’s bachelor thesis, he adapted PEV2 to work with DuckDB’s execution plans. This involved understanding the structure of DuckDB’s execution plans and adapting the visualization tool to work with them.