Day 84: EXPLAIN `pretty=1` and `compact=1` in ClickHouse® 26.3

When a ClickHouse® query runs slowly, the root cause is not always insufficient hardware or an inefficient schema. In many cases, the issue lies in the execution plan itself. The challenge is that execution plans are often difficult to read, making it harder to understand what the optimizer is actually doing.

ClickHouse® 26.3 introduces two new formatting options for the EXPLAIN statement—pretty=1 and compact=1—that make query plans significantly easier to inspect without changing the execution itself. These options provide two different views of the same logical plan, allowing developers to choose between a detailed tree representation or a concise overview depending on the debugging scenario.

This article explores both options using five progressively complex SQL queries against the official Stack Overflow sample dataset to demonstrate where each format excels and how they can improve the query optimization workflow.

---

Understanding the New EXPLAIN Options

The EXPLAIN statement has always been an essential tool for understanding how ClickHouse plans to execute a query. It exposes the logical operations that occur before the query is actually run.

ClickHouse already supports several EXPLAIN modes:

• EXPLAIN or EXPLAIN PLAN displays the logical execution plan.
• EXPLAIN PIPELINE shows the physical execution pipeline including processors and parallelism.
• EXPLAIN ESTIMATE estimates how many rows, parts, and marks will be read.

ClickHouse 26.3 extends the standard logical plan with two formatting options:

EXPLAIN pretty=1
SELECT ...

EXPLAIN compact=1
SELECT ...

EXPLAIN pretty=1, compact=1
SELECT ...

These are not new SQL commands but optional settings passed to the existing EXPLAIN statement.

Both display exactly the same execution plan—the only difference is how that plan is presented.

---

Test Environment

The demonstrations were performed using:

Component	Version
ClickHouse®	26.3 LTS
Deployment	Docker Compose
Dataset	Stack Overflow Sample Dataset
Client	clickhouse-client

Hardware:

• AMD Ryzen 5 3400G
• 16 GB RAM
• NVMe SSD
• KDE Neon (Ubuntu-based)

Instead of using a synthetic dataset, the official Stack Overflow dataset was chosen because it contains realistic relationships between tables and supports joins, aggregations, sorting, filtering, and grouping.

The dataset contains approximately:

• 37 million posts
• 13 million users

This produces execution plans that closely resemble real production workloads.

---

Why Query Plans Matter

A SQL query only describes what result should be returned.

It does not describe how ClickHouse will retrieve that result.

The optimizer determines:

• which table to read first,
• whether filters are pushed down,
• where aggregations occur,
• which join algorithm is selected,
• whether lazy column reading can be used,
• how sorting is performed.

Without examining the execution plan, developers often end up guessing why a query performs poorly.

The new formatting options simply make those plans easier to interpret.

---

Test 1 — Simple WHERE Filter

The first query applies a straightforward filter:

SELECT *
FROM stackoverflow.posts
WHERE Score > 100;

This serves as the simplest possible execution plan.

What the plan reveals

The filter does not appear as a dedicated "Filter" node.

Instead, ClickHouse incorporates it directly into an Expression node labeled similarly to:

WHERE + Change column names

This demonstrates one of ClickHouse's optimizer behaviors: not every SQL clause becomes its own execution stage.

Difference between formats

With pretty=1, the plan displays several wrapper nodes that make the hierarchy explicit.

With compact=1, almost all wrapper nodes disappear, leaving only the essential operation:

ReadFromMergeTree

For such a simple query, the compact output communicates everything necessary while eliminating visual clutter.

---

Test 2 — GROUP BY Aggregation

The second query introduces aggregation:

SELECT
OwnerUserId,
count()
FROM stackoverflow.posts
GROUP BY OwnerUserId
ORDER BY count() DESC
LIMIT 20;

Now the execution plan becomes more interesting.

Execution order

Reading the plan from bottom to top reveals:

Read
↓

Aggregate
↓

Sort
↓

Limit

This corresponds directly to how ClickHouse executes the query.

Observations

The execution plan identifies an Aggregating stage but does not explicitly list:

• grouping keys
• aggregate functions

Instead, it simply labels the node:

Aggregating

The detailed format provides additional contextual information such as:

• Before GROUP BY
• Before ORDER BY
• Projection

These annotations make the overall flow much easier to understand.

The compact version removes these descriptions while preserving the major execution stages.

---

Test 3 — JOIN Operations

The third example introduces an INNER JOIN between posts and users.

This is where execution plans become especially valuable.

SELECT
u.DisplayName,
count(),
sum(p.Score)
FROM posts p
INNER JOIN users u
ON p.OwnerUserId=u.Id
...

What EXPLAIN reveals

The plan shows:

• both table scans,
• the chosen join strategy,
• runtime filter creation,
• which table becomes the hash-table build side.

ClickHouse selects the users table as the build side because it is significantly smaller than the posts table.

This decision is not visible from the SQL query itself.

It only becomes apparent through the execution plan.

Format comparison

The compact format still preserves the two separate branches of the join.

However, the pretty format includes labels such as:

• Left Pre Join Actions
• Right Pre Join Actions

These labels make it immediately obvious which branch belongs to each table.

For join analysis, the detailed tree is noticeably easier to follow.

---

Test 4 — ORDER BY with LIMIT

Sorting is another area where execution plans expose optimizations that SQL alone cannot reveal.

SELECT *
FROM posts
ORDER BY CreationDate DESC
LIMIT 100;

The plan shows something surprising.

Instead of reading every column from every row, ClickHouse performs a lazy read.

It first scans only the sorting column:

CreationDate

After identifying the top 100 rows, it retrieves the remaining columns only for those records.

The execution plan contains nodes such as:

• JoinLazyColumnsStep
• LazilyReadFromMergeTree

This optimization significantly reduces unnecessary disk I/O.

Without inspecting EXPLAIN, most users would never know this optimization was taking place.

Both formatting options preserve this information.

---

Test 5 — Complex Analytical Query

The final example combines nearly every major SQL operation:

• JOIN
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• LIMIT

This produces a much larger execution plan.

Key observations

Several optimizer decisions become visible.

Filter Pushdown

The WHERE clause is applied before the join occurs, reducing the amount of data processed.

Join Reordering

Unlike the previous join example, the optimizer chooses a different execution order because the WHERE clause dramatically reduces the number of rows.

This demonstrates that ClickHouse does not rely solely on table size.

It also considers filter selectivity.

HAVING Stage

The execution plan includes a dedicated HAVING filter stage rather than merging it into aggregation.

This confirms that HAVING is processed after aggregation, exactly as expected.

Why formatting matters

At roughly fifteen execution nodes, readability becomes critical.

The indented tree produced by pretty=1 makes it easy to follow the flow of data through every stage.

The compact version still contains all major operations, but tracing parent-child relationships requires considerably more effort.

This example clearly demonstrates where the detailed format becomes the better choice.

---

Comparing pretty=1 and compact=1

Both options display the same logical execution plan.

The difference lies entirely in presentation.

pretty=1

Advantages:

• Highly readable tree structure
• Rich contextual labels
• Easier debugging
• Better documentation
• Ideal for learning execution plans

Tradeoffs:

• Larger output
• More verbose

---

compact=1

Advantages:

• Shorter output
• Easier to scan quickly
• Better suited for terminals and CI logs
• Minimal visual overhead

Tradeoffs:

• Removes contextual labels
• Requires more familiarity with execution plans

---

Which One Should You Use?

Choose pretty=1 when:

• Learning how ClickHouse executes queries
• Debugging complex performance issues
• Understanding optimizer decisions
• Writing technical documentation
• Teaching execution plans

Choose compact=1 when:

• Quickly checking query structure
• Verifying optimizer behavior
• Working inside terminal sessions
• Keeping CI logs concise
• Inspecting queries you already understand

Many developers may even combine both:

EXPLAIN pretty=1, compact=1
SELECT ...

This produces an indented tree while collapsing unnecessary Expression nodes, often providing the best balance between readability and brevity.

---

Best Practices

To make the most of these new formatting options:

• Always inspect the execution plan before attempting query optimization.
• Compare execution plans before and after schema changes to verify that indexes, projections, or sorting keys are actually being used.
• Test using realistic datasets instead of tiny sample tables, as optimizer behavior changes significantly with scale.
• Combine EXPLAIN with system.query_log to correlate the planned execution with actual runtime statistics such as rows read, memory usage, and execution time.
• Use EXPLAIN PIPELINE alongside logical plans when investigating processor-level execution and parallelism.

---

Final Thoughts

The introduction of pretty=1 and compact=1 in ClickHouse® 26.3 does not improve query performance directly—but it greatly improves a developer's ability to understand why a query behaves the way it does.

For simple queries, the difference between the two formats is minimal. As execution plans grow more complex with joins, aggregations, filters, and sorting, the value of a readable execution tree becomes increasingly apparent.

pretty=1 is ideal for deep analysis and debugging, while compact=1 is perfect for quick inspections during day-to-day development.

Rather than replacing one another, these two formats complement different stages of the query optimization process, giving developers a more efficient way to inspect and reason about ClickHouse execution plans.