Query Plans

How to read these

Plans render leaf-last: the Scan at the bottom is where bytes come off disk, and each node above it consumes its child's output. The optimizer has already run, so what the Scan node reports is what it genuinely read.

cols=[…]: the columns actually read off disk, after projection pushdown
cols=[row counts only]: no column values were needed, so the narrowest column on disk was read for its row count alone
parts=[…]: predicates resolved from directory names, eliminating whole files
filter=[…]: predicates tested against footer min/max to skip row-groups, then applied to the rows that survive
group=[-]: an aggregate over the whole input, with no GROUP BY

What the optimizer does

Projection pushdown. Walks the tree collecting every referenced column and pushes that set into the Scan. Unreferenced columns are never read, which on a columnar format is the single biggest win available.
Predicate pushdown. Splits the WHERE clause on AND and routes each conjunct by what it touches: partition columns become file-elimination rules, data columns become row-group statistics tests. The split matters because only one of those costs zero I/O.
Filter removal. A Filter node whose predicates were entirely absorbed by the scan is deleted rather than left in place re-checking rows that cannot fail.

Three rules, applied to a fixed point. There is no cost model and no join ordering — with a single-table scan as the only leaf there is nothing to order.

Run #15 · trips

success read 16.5% of dataset bytes 17.76 ms

graph TD n0["Aggregate group=[region] aggs=[count(*),sum(distance)]"] n1["Scan trips cols=[trip_id,distance] part_filter pushdown"] n0 --> n1

Text plan

Aggregate group=[region] aggs=[count(*),sum(distance)]
  Scan trips cols=[trip_id,distance] part_filter pushdown

SQL

SELECT region, COUNT(*) AS n, SUM(distance) AS total_distance
FROM trips
WHERE region = 'EU' AND trip_id > 2374
GROUP BY region

10 of 15 files were never opened — the partition filter ruled them out from the directory path alone, costing zero I/O.
Only 2 of 7 columns came off disk — the other 5 were never referenced, and Parquet is columnar.

Run #14 · trips

success read 23.2% of dataset bytes 30.01 ms

graph TD n0["Sort n desc"] n1["Aggregate group=[region] aggs=[count(*),avg(distance)]"] n2["Scan trips cols=[distance]"] n1 --> n2 n0 --> n1

Text plan

Sort n desc
  Aggregate group=[region] aggs=[count(*),avg(distance)]
    Scan trips cols=[distance]

SQL

SELECT region, COUNT(*) AS n, AVG(distance) AS avg_distance
FROM trips
GROUP BY region
ORDER BY n DESC

Only 1 of 7 columns came off disk — the other 6 were never referenced, and Parquet is columnar.

Run #13 · trips

success read 26.9% of dataset bytes 34.74 ms

graph TD n0["Aggregate group=[-] aggs=[count(*)]"] n1["Scan trips cols=[trip_id] pushdown"] n0 --> n1

Text plan

Aggregate group=[-] aggs=[count(*)]
  Scan trips cols=[trip_id] pushdown

SQL

SELECT COUNT(*) AS total
FROM trips
WHERE trip_id > 2374

Only 1 of 7 columns came off disk — the other 6 were never referenced, and Parquet is columnar.

Run #12 · trips

success read 0.5% of dataset bytes 7.99 ms

graph TD n0["Aggregate group=[-] aggs=[count(*)]"] n1["Scan trips cols=[passengers] part_filter"] n0 --> n1

Text plan

Aggregate group=[-] aggs=[count(*)]
  Scan trips cols=[passengers] part_filter

SQL

SELECT COUNT(*) AS total
FROM trips
WHERE region = 'EU'

10 of 15 files were never opened — the partition filter ruled them out from the directory path alone, costing zero I/O.
Only 1 of 7 columns came off disk — the other 6 were never referenced, and Parquet is columnar.

Run #11 · trips

success read 100.0% of dataset bytes 89.94 ms

graph TD n0["Limit 20"] n1["Scan trips cols=[*]"] n0 --> n1

Text plan

Limit 20
  Scan trips cols=[*]

SQL

SELECT *
FROM trips
LIMIT 20

Nothing was pruned: this query had no filter the engine could push down, so every file, row-group and column had to be read. This is the baseline the other demo queries are measured against.

Run #10 · trips

success read 16.5% of dataset bytes 13.7 ms

graph TD n0["Aggregate group=[region] aggs=[count(*),sum(distance)]"] n1["Scan trips cols=[trip_id,distance] part_filter pushdown"] n0 --> n1

Text plan

Aggregate group=[region] aggs=[count(*),sum(distance)]
  Scan trips cols=[trip_id,distance] part_filter pushdown

SQL

SELECT region, COUNT(*) AS n, SUM(distance) AS total_distance
FROM trips
WHERE region = 'EU' AND trip_id > 2374
GROUP BY region

10 of 15 files were never opened — the partition filter ruled them out from the directory path alone, costing zero I/O.
Only 2 of 7 columns came off disk — the other 5 were never referenced, and Parquet is columnar.

Run #9 · trips

success read 23.2% of dataset bytes 28.05 ms

graph TD n0["Sort n desc"] n1["Aggregate group=[region] aggs=[count(*),avg(distance)]"] n2["Scan trips cols=[distance]"] n1 --> n2 n0 --> n1

Text plan

Sort n desc
  Aggregate group=[region] aggs=[count(*),avg(distance)]
    Scan trips cols=[distance]

SQL

SELECT region, COUNT(*) AS n, AVG(distance) AS avg_distance
FROM trips
GROUP BY region
ORDER BY n DESC

Only 1 of 7 columns came off disk — the other 6 were never referenced, and Parquet is columnar.

Run #8 · trips

success read 26.9% of dataset bytes 31.37 ms

graph TD n0["Aggregate group=[-] aggs=[count(*)]"] n1["Scan trips cols=[trip_id] pushdown"] n0 --> n1

Text plan

Aggregate group=[-] aggs=[count(*)]
  Scan trips cols=[trip_id] pushdown

SQL

SELECT COUNT(*) AS total
FROM trips
WHERE trip_id > 2374

Only 1 of 7 columns came off disk — the other 6 were never referenced, and Parquet is columnar.

Run #7 · trips

success read 0.5% of dataset bytes 8.12 ms

graph TD n0["Aggregate group=[-] aggs=[count(*)]"] n1["Scan trips cols=[passengers] part_filter"] n0 --> n1

Text plan

Aggregate group=[-] aggs=[count(*)]
  Scan trips cols=[passengers] part_filter

SQL

SELECT COUNT(*) AS total
FROM trips
WHERE region = 'EU'

10 of 15 files were never opened — the partition filter ruled them out from the directory path alone, costing zero I/O.
Only 1 of 7 columns came off disk — the other 6 were never referenced, and Parquet is columnar.

Run #6 · trips

success read 100.0% of dataset bytes 101.34 ms

graph TD n0["Limit 20"] n1["Scan trips cols=[*]"] n0 --> n1

Text plan

Limit 20
  Scan trips cols=[*]

SQL

SELECT *
FROM trips
LIMIT 20

Nothing was pruned: this query had no filter the engine could push down, so every file, row-group and column had to be read. This is the baseline the other demo queries are measured against.

Run #5 · trips

success read 16.5% of dataset bytes 17.57 ms

graph TD n0["Aggregate group=[region] aggs=[count(*),sum(distance)]"] n1["Scan trips cols=[trip_id,distance] part_filter pushdown"] n0 --> n1

Text plan

Aggregate group=[region] aggs=[count(*),sum(distance)]
  Scan trips cols=[trip_id,distance] part_filter pushdown

SQL

SELECT region, COUNT(*) AS n, SUM(distance) AS total_distance
FROM trips
WHERE region = 'EU' AND trip_id > 2374
GROUP BY region

10 of 15 files were never opened — the partition filter ruled them out from the directory path alone, costing zero I/O.
Only 2 of 7 columns came off disk — the other 5 were never referenced, and Parquet is columnar.

Run #4 · trips

success read 23.2% of dataset bytes 30.89 ms

graph TD n0["Sort n desc"] n1["Aggregate group=[region] aggs=[count(*),avg(distance)]"] n2["Scan trips cols=[distance]"] n1 --> n2 n0 --> n1

Text plan

Sort n desc
  Aggregate group=[region] aggs=[count(*),avg(distance)]
    Scan trips cols=[distance]

SQL

SELECT region, COUNT(*) AS n, AVG(distance) AS avg_distance
FROM trips
GROUP BY region
ORDER BY n DESC

Only 1 of 7 columns came off disk — the other 6 were never referenced, and Parquet is columnar.

Run #3 · trips

success read 26.9% of dataset bytes 31.97 ms

graph TD n0["Aggregate group=[-] aggs=[count(*)]"] n1["Scan trips cols=[trip_id] pushdown"] n0 --> n1

Text plan

Aggregate group=[-] aggs=[count(*)]
  Scan trips cols=[trip_id] pushdown

SQL

SELECT COUNT(*) AS total
FROM trips
WHERE trip_id > 2374

Only 1 of 7 columns came off disk — the other 6 were never referenced, and Parquet is columnar.

Run #2 · trips

success read 0.5% of dataset bytes 9.06 ms

graph TD n0["Aggregate group=[-] aggs=[count(*)]"] n1["Scan trips cols=[passengers] part_filter"] n0 --> n1

Text plan

Aggregate group=[-] aggs=[count(*)]
  Scan trips cols=[passengers] part_filter

SQL

SELECT COUNT(*) AS total
FROM trips
WHERE region = 'EU'

10 of 15 files were never opened — the partition filter ruled them out from the directory path alone, costing zero I/O.
Only 1 of 7 columns came off disk — the other 6 were never referenced, and Parquet is columnar.

Run #1 · trips

success read 100.0% of dataset bytes 102.01 ms

graph TD n0["Limit 20"] n1["Scan trips cols=[*]"] n0 --> n1

Text plan

Limit 20
  Scan trips cols=[*]

SQL

SELECT *
FROM trips
LIMIT 20

Nothing was pruned: this query had no filter the engine could push down, so every file, row-group and column had to be read. This is the baseline the other demo queries are measured against.

Showing the 15 most recent runs that recorded a plan.

How to read these

What the optimizer does

Run #15 · trips

Run #14 · trips

Run #13 · trips

Run #12 · trips

Run #11 · trips

Run #10 · trips

Run #9 · trips

Run #8 · trips

Run #7 · trips

Run #6 · trips

Run #5 · trips

Run #4 · trips

Run #3 · trips

Run #2 · trips

Run #1 · trips