EXPLAIN PRETTY and COMPACT in ClickHouse® 26.3

Slow queries aren't always a hardware problem - often they're a query plan problem, and the plan is invisible until you ask for it. ClickHouse® 26.3 makes that easier with two new EXPLAIN options: pretty=1 and compact=1.

These aren't separate SQL statements - there's no literal EXPLAIN PRETTY keyword - they're settings you pass to EXPLAIN, and you can combine them:

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

This article walks through both, side by side, on five progressively more complex queries against a real dataset, so you can see exactly how each format renders a filter, a GROUP BY, a JOIN, and beyond.

Test Environment

Machine Specifications

• CPU: AMD Ryzen 5 3400G (4 cores / 8 threads, integrated Radeon Vega 11 Graphics)
• RAM: 16 GiB total (13.6 GiB usable)
• Storage: NVMe SSD, 465.8 GB (nvme0n1)
• Operating System: KDE neon (Ubuntu-based)

Docker Compose Configuration

services:
  clickhouse:
    image: clickhouse/clickhouse-server:26.3
    container_name: clickhouse-263
    ports:
      - "8123:8123"
      - "9000:9000"
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    volumes:
      - ch_data:/var/lib/clickhouse
 
volumes:
  ch_data:

docker compose up -d
docker exec -it clickhouse-263 clickhouse-client --query "SELECT version()"

Dataset: Stack Overflow

This article uses the Stack Overflow dataset rather than the UK Property Price Paid dataset from our previous benchmark, because it supports filters, JOINs, aggregations, sorting, and grouping across genuinely related tables (posts.OwnerUserId → users.Id) - exactly what's needed to make five structurally different EXPLAIN plans worth comparing.

Why this dataset:

• Official ClickHouse® sample dataset - publicly hosted, no auth needed, fully reproducible.
• ~37 million posts, plus related users data - big enough for realistic plans, small enough to load quickly.
• Genuinely relational, unlike the flat property-price dataset, so JOIN plans have something real to show.

CREATE DATABASE IF NOT EXISTS stackoverflow;
 
CREATE TABLE stackoverflow.posts
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate))
AS SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')
LIMIT 37000000;
 
CREATE TABLE stackoverflow.users
ENGINE = MergeTree
ORDER BY Id
AS SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/users.parquet')
LIMIT 13000000;
 
SELECT count() FROM stackoverflow.posts;
SELECT count() FROM stackoverflow.users;

What is EXPLAIN?

ClickHouse®'s EXPLAIN statement has several modes, each showing a different layer of what the engine plans to do with your query:

EXPLAIN SELECT ...          -- default: query plan, ClickHouse®'s logical steps
EXPLAIN PLAN SELECT ...     -- same as above, explicit form
EXPLAIN PIPELINE SELECT ... -- the physical execution pipeline (processors/threads)
EXPLAIN ESTIMATE SELECT ... -- estimated rows/parts/marks that will be read

This article focuses on two options layered on top of EXPLAIN, new in 26.3:

EXPLAIN pretty=1 SELECT ...    -- tree-style, indented, human-readable output
EXPLAIN compact=1 SELECT ...   -- collapses Expression steps, denser output

They aren't mutually exclusive - EXPLAIN pretty=1, compact=1 gives you an indented tree with the verbose Expression steps folded away, often the most readable combination for a quick look at a plan's shape.

Test 1 - Simple Filter

The simplest possible plan - one table, one filter. This is the baseline for comparing how much visual overhead pretty=1 adds and how much compact=1 strips away.

SELECT *
FROM stackoverflow.posts
WHERE Score > 100;

EXPLAIN pretty=1
SELECT * FROM stackoverflow.posts WHERE Score > 100;

EXPLAIN compact=1
SELECT * FROM stackoverflow.posts WHERE Score > 100;

Explain pretty

Explain compact

Discussion

• The WHERE Score > 100 filter isn't shown as a separate Filter or PREWHERE step - it's folded directly into an Expression node labeled (WHERE + Change column names to column identifiers).
• compact=1 doesn't just shrink this plan, it collapses it entirely - from 3 rows down to a single line, just ReadFromMergeTree. For a query this simple, there's no wrapper structure left worth showing.
• This is the clearest case for compact=1: at this level of simplicity, pretty=1's extra structure doesn't add anything a reader needs.

Test 2 - GROUP BY

Introduces an Aggregating step and a Sorting step, so this shows how each format handles a multi-stage plan rather than a single filter.

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

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

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

Explain pretty

Explain compact

Discussion

• Reading the plan bottom-to-top shows the expected execution order: Read → Aggregate → Sort → Limit, matching GROUP BY → ORDER BY → LIMIT in the SQL.
• Neither format prints the actual aggregate keys or functions - there's no Keys: OwnerUserId or Aggregates: count() label. The node is simply named Aggregating.
• pretty=1's real value here is the parenthetical context on each step - (Before GROUP BY...), (Before ORDER BY + Projection), (Project names) - which explain why each step exists. compact=1 strips those labels along with the wrapper nodes, but the four steps it keeps (Limit, Sorting, Aggregating, Read) are arguably sufficient to understand the plan's shape.

Test 3 - JOIN

This is where having two related tables earns its keep - a JOIN plan reveals the join algorithm ClickHouse® picked and which side it chose to build the hash table from.

SELECT
    u.DisplayName,
    count() AS post_count,
    sum(p.Score) AS total_score
FROM stackoverflow.posts AS p
INNER JOIN stackoverflow.users AS u ON p.OwnerUserId = u.Id
GROUP BY u.DisplayName
ORDER BY total_score DESC
LIMIT 20;

EXPLAIN pretty=1
SELECT u.DisplayName, count() AS post_count, sum(p.Score) AS total_score
FROM stackoverflow.posts AS p
INNER JOIN stackoverflow.users AS u ON p.OwnerUserId = u.Id
GROUP BY u.DisplayName ORDER BY total_score DESC LIMIT 20;

EXPLAIN compact=1
SELECT u.DisplayName, count() AS post_count, sum(p.Score) AS total_score
FROM stackoverflow.posts AS p
INNER JOIN stackoverflow.users AS u ON p.OwnerUserId = u.Id
GROUP BY u.DisplayName ORDER BY total_score DESC LIMIT 20;

Explain pretty

Explain compact

Discussion

• The plan confirms users is the build side of the join, not posts - visible because BuildRuntimeFilter sits in the branch reading stackoverflow.users, filtering on __table2.Id. ClickHouse's join reordering correctly picked the smaller table (~22M rows) to build from over the larger one (~37M rows).
• Unlike Test 1, compact=1 doesn't lose meaningful structure here - it still shows both ReadFromMergeTree calls, the BuildRuntimeFilter step, and the two-sided shape of the join, just without the "Left/Right Pre Join Actions" labels.
• pretty=1 is still the easier first read, purely because those Left/Right labels tell you which physical branch is which without having to trace read order manually.

Test 4 - ORDER BY + LIMIT

Isolates how ClickHouse® represents a plain sort-and-cap operation, without aggregation muddying the picture.

SELECT *
FROM stackoverflow.posts
ORDER BY CreationDate DESC
LIMIT 100;

EXPLAIN pretty=1
SELECT * FROM stackoverflow.posts ORDER BY CreationDate DESC LIMIT 100;

EXPLAIN compact=1
SELECT * FROM stackoverflow.posts ORDER BY CreationDate DESC LIMIT 100;

Explain pretty

Explain compact

Discussion

• There's a clear preliminary LIMIT step, confirmed by name, applied before the full sort completes.
• The more interesting detail: SELECT * with ORDER BY ... LIMIT 100 doesn't read every column for every row. JoinLazyColumnsStep and LazilyReadFromMergeTree show ClickHouse sorting using only the CreationDate column across the full table, then fetching the remaining columns only for the 100 rows that actually made the cut.
• This kind of optimization isn't visible from the SQL text alone - it only shows up once you look at the plan, which is exactly the case for reaching for EXPLAIN in the first place.
• Both formats surface this lazy-read structure; compact=1 simply tightens the indentation without hiding it.

Test 5 - Complex Query

Combines JOIN + WHERE + GROUP BY + HAVING + ORDER BY + LIMIT in one query - this is where a readable format stops being a nice-to-have and starts being the only practical way to understand what's happening.

SELECT
    u.DisplayName,
    count() AS answer_count,
    avg(p.Score) AS avg_score
FROM stackoverflow.posts AS p
INNER JOIN stackoverflow.users AS u ON p.OwnerUserId = u.Id
WHERE p.PostTypeId = 2  -- Answers
  AND p.CreationDate >= '2023-01-01'
GROUP BY u.DisplayName
HAVING answer_count > 5
ORDER BY avg_score DESC
LIMIT 25;

EXPLAIN pretty=1
SELECT u.DisplayName, count() AS answer_count, avg(p.Score) AS avg_score
FROM stackoverflow.posts AS p
INNER JOIN stackoverflow.users AS u ON p.OwnerUserId = u.Id
WHERE p.PostTypeId = 2 AND p.CreationDate >= '2023-01-01'
GROUP BY u.DisplayName HAVING answer_count > 5
ORDER BY avg_score DESC LIMIT 25;

EXPLAIN compact=1
SELECT u.DisplayName, count() AS answer_count, avg(p.Score) AS avg_score
FROM stackoverflow.posts AS p
INNER JOIN stackoverflow.users AS u ON p.OwnerUserId = u.Id
WHERE p.PostTypeId = 2 AND p.CreationDate >= '2023-01-01'
GROUP BY u.DisplayName HAVING answer_count > 5
ORDER BY avg_score DESC LIMIT 25;

Explain pretty

Explain compact

Discussion

• The join sides flip compared to Test 3: here, users is on the left (build side), and posts is on the right - but this time the WHERE (PostTypeId = 2) AND (CreationDate >= 2023-01-01) filter is folded directly into that branch, ahead of the runtime filter.
• This shows table size alone doesn't decide the plan - selectivity matters too. Once posts is cut down significantly by the WHERE clause, the optimizer restructures the join around that, rather than always building from the smaller raw table by default.
• At 15 nodes, pretty=1 still holds up - the indentation guides make it possible to trace which table feeds which join branch at a glance. Doing the same with compact=1's flat 8-line list means counting indentation levels carefully rather than reading labels directly.
• HAVING answer_count > 5 appears as its own explicit Filter (HAVING) step, clearly separated from Aggregating - confirming HAVING is a distinct plan stage, not merged into the aggregate itself.
• This is the test case that best supports the article's core argument: the more operations stack up, the more pretty=1 earns its verbosity.

Comparing pretty=1 and compact=1

They're not rivals - most of the value in this article comes from noticing that the gap between them widens as the query gets more complex. At Test 1, they're nearly interchangeable. By Test 5, the choice actually matters.

Practical Use Cases

Use pretty=1 when:

• Learning how ClickHouse® executes a specific query shape
• Debugging an unexpected slowdown
• Performance tuning, where you need to see every step
• Documentation or blog posts (like this one)

Use compact=1 when:

• Everyday development - you just want a shape check
• Quick inspections mid-debugging session
• CI logs, where terminal width and log volume matter
• You already know the query well and just need to confirm the plan didn't change

Best Practices

• Always inspect the execution plan before optimizing - guessing at the bottleneck wastes more time than reading EXPLAIN would have.
• Compare plans before and after schema changes (e.g., adding a projection or changing ORDER BY) to confirm the optimizer actually used what you built.
• Use a realistic dataset when testing plans - small toy tables produce misleadingly simple plans.
• Combine EXPLAIN with system.query_log to validate that what the plan says will happen matches what actually happened (rows read, memory used).
• Reach for EXPLAIN PIPELINE when you need to see execution stages and parallelism, not just logical structure - pretty=1/compact=1 apply to the logical plan, not the pipeline view.

Final Thoughts

EXPLAIN pretty=1 and EXPLAIN compact=1 don't change query performance - they change your ability to understand how ClickHouse® executes a query. Neither is objectively better: pretty wins when you need to learn or debug, compact wins when you already understand the query and just need a quick shape check. The right call depends on whether you're doing a deep dive or a five-second sanity check.

For more ClickHouse® optimization and operational guidance, explore the CHOps feature page at CH-Ops.

References

• ClickHouse® Explain Statement
• ClickHouse® Stack Overflow dataset
• ClickHouse® system.query_log
• ClickHouse® Release 26.3