EXPLAIN Literacy Across Engines

Module: Operating Query Engines | Duration: 20 min hands-on | Lesson: 1 of 14

Priya is now on-call for four engines: Trino, ClickHouse, Druid, and DuckDB. A query is slow on each, in turn, and each prints a completely different EXPLAIN: Trino shows fragments and distribution, ClickHouse shows a pipeline of processors, Druid shows native JSON, DuckDB shows a tree of operators. Four formats, four vocabularies, one tired engineer at 3am.

Here's the freeing realization: they're all describing the same handful of things. Scan, filter, join, aggregate, exchange, sort. Once you can map any engine's EXPLAIN onto that shared mental model from Query Engine Foundations, the format stops mattering. This lesson builds that map, and brings up the lab you'll use for the rest of the course.

2. Concept Explanation

Every query engine's EXPLAIN, however it's formatted, answers the same questions. Learn the questions, and any format becomes readable.

The five questions every EXPLAIN answers

1. What's read, and how much is pruned? The scan, and whether filters/partitions/indexes cut it down before reading. (ClickHouse: granules selected. Trino: pushed-down predicates + dynamic filters. Druid: which segments. DuckDB: which row groups.)
2. What's pushed down vs done in the engine? Did the filter/projection/aggregation happen at the source/storage, or after reading? (Especially Trino connectors; also ClickHouse PREWHERE.)
3. How are joins done and distributed? Hash vs sort-merge; broadcast vs partitioned/shuffle. (The foundations join lessons, made concrete per engine.)
4. Where are the pipeline breakers? The sorts, hash builds, and global aggregations where memory concentrates and streaming stops.
5. What's the parallelism? How many lanes/tasks run each stage. (ClickHouse (xN); Trino tasks per stage; etc.)

The same operators, different words

The labels differ; the operators are the ones you already know. EXPLAIN literacy is translation, not relearning.

Two EXPLAIN modes, everywhere

Almost every engine has two flavors, and you use both:

• Static plan (EXPLAIN): the shape the optimizer chose, before running. Answers "what will it do?"
• Analyzed/profiled (EXPLAIN ANALYZE in Trino/DuckDB, EXPLAIN PIPELINE + query log in ClickHouse, query metrics in Druid): the real timings after running. Answers "where did the time actually go?"

The method, which the next lesson formalizes, is always: read the static plan for structure, then the analyzed plan for where time went.

3. Worked Example

Bring up the lab and read the same query's plan through two engines.

Bring up the lab (one time, shared across all courses):

git clone https://github.com/petascalelabs/petascalelabs-lab-setup.git
cd petascalelabs-lab-setup/query-engines-and-olap/query-engine-operations/operating-query-engines/
./scripts/setup.sh

Verify ClickHouse and Trino are up over the same data:

./scripts/verify.sh
# expected: "Operating Query Engines lab ready: ClickHouse + Trino, same data through both"

Now read one query's plan in both engines. The data (tws.events) is identical; only the EXPLAIN dialect differs.

# ClickHouse: pipeline of processors, with (xN) parallelism
docker compose exec clickhouse clickhouse-client --query \
  "EXPLAIN PIPELINE SELECT country, count() FROM tws.events GROUP BY country"

# ClickHouse: index usage (how much got pruned)
docker compose exec clickhouse clickhouse-client --query \
  "EXPLAIN indexes=1 SELECT count() FROM tws.events WHERE event_date='2026-03-05'"

# Trino: the same group-by over the SAME data via the clickhouse connector
docker compose exec trino trino --execute \
  "EXPLAIN SELECT country, count(*) FROM clickhouse.tws.events GROUP BY country"

# Trino: analyzed plan with real timings
docker compose exec trino trino --execute \
  "EXPLAIN ANALYZE SELECT country, count(*) FROM clickhouse.tws.events GROUP BY country"

Map them onto the five questions: in ClickHouse's EXPLAIN PIPELINE, find the (xN) (parallelism) and the Aggregating (aggregate); in Trino's plan, find the TableScan (what's pushed to ClickHouse), the Aggregate, and the fragment boundaries (exchanges). Same five questions, two accents. Do this once and every future EXPLAIN is just translation.

I'm working through the "Operating Query Engines" course on data-learning. I want to compare EXPLAIN output across query engines and map them to one mental model.

My environment:
- OS: <fill in: macOS / Linux / Windows>
- RAM: <fill in, e.g. 16GB>
- I have <Docker / nothing> available

Please help me:
1. Run a simple aggregation query on two different engines I have access to (e.g. Trino and ClickHouse, or DuckDB).
2. For each, run EXPLAIN and EXPLAIN ANALYZE and show me how to read it.
3. Map both plans onto five questions: what's scanned/pruned, what's pushed down, how joins are done, where the pipeline breakers are, and how parallel it is.

Keep it to my OS only. Do not give me commands for other platforms.

4. Your Turn

Exercise: Translate across engines.

1. A ClickHouse EXPLAIN PIPELINE shows (x1) on the aggregation; a Trino plan shows one task on the aggregate stage. Which of the five questions is each answering, and what's the shared concern?
2. You see a Trino Filter node above a TableScan (not pushed). What's the ClickHouse analog you'd look for to check if filtering happened early?
3. Map these to the shared operator: ClickHouse ReadFromMergeTree, Trino TableScan, DuckDB SEQ_SCAN.
4. For "where will this query use the most memory?", which of the five questions do you read, and what operators are you hunting for in any engine?

5. Real-World Application

EXPLAIN literacy across engines is the core skill of multi-engine on-call. A platform team running Trino for federation, ClickHouse for product analytics, and Druid for user-facing dashboards needs engineers who can read any of their plans, and the only sane way to do that is the shared mental model, not memorizing four formats. The five questions are the same diagnostic checklist regardless of which pager went off.

This lesson is deliberately the gateway to the whole Operations track. Every later lesson, profiling, memory, concurrency, incidents, starts from "read the plan." Build the translation habit now and the rest of the course is applying it under production pressure. It's also why this skill appears in both S6.1 (as a concept) and here (as a per-engine operational skill): the concept and the craft reinforce each other.

6. Recap + Bridge

Every engine's EXPLAIN answers the same five questions: what's scanned/pruned, what's pushed down, how joins distribute, where the pipeline breakers are, and how parallel it is. The operators are the ones from Query Engine Foundations wearing different labels. Read the static plan for structure, the analyzed plan for timings. EXPLAIN literacy is translation, not relearning.

Reading a plan is step one. Turning it into a fix needs method. Next: Profiling a Slow Query, a methodology that works before you reach for any tool.