Skip to content

Query performance on lake tables

Query performance on lake tables comes mostly from reading less: fewer files, fewer row groups and fewer columns.

Before reading

Partitioning · Apache Iceberg: table format basics

Why it matters

Engines spend most of their time and money fetching and decoding data from object storage. A query that reads 1% of a table is roughly a hundred times cheaper than one that reads all of it, and no amount of compute makes up for reading the wrong data.

How it works

Skipping happens at several levels, each using metadata the level above could not see.

flowchart TB
  T[Whole table] -->|partition pruning| P[Matching<br/>partitions]
  P -->|file skipping by<br/>column bounds| F[Matching<br/>files]
  F -->|row group and<br/>page statistics| R[Matching<br/>row groups]
  R -->|projection| C[Only the<br/>columns used]

Partition pruning drops partitions using the table's partition rules. File skipping drops files whose column bounds, kept in the table metadata, cannot match. Inside each file, row group and page statistics drop more. Projection reads only the columns the query uses.

All of this depends on predicate pushdown: the engine passing the query's filter down to the table reader, so that the reader can skip data before it is fetched. Pushdown often fails when the filter hides the column. A condition such as format_datetime(ordered_at, 'yyyy-MM-dd') = '2026-10-28' cannot be compared with partition values or column bounds, so everything is read and filtered afterwards. A range on the column itself, ordered_at >= ... AND ordered_at < ..., can be pushed down. Comparing a column with a value of a different type can have the same effect. Some engines rewrite simple casts so they still prune; the plan shows whether that happened.

Statistics skip only when values are clustered. If a column's values are spread randomly across files, every file's bounds cover nearly the whole range. Sorting within files, by the columns queries filter on after the partition key, makes the bounds narrow. Z-ordering interleaves two or more columns, so that filters on any of them skip reasonably well. Both cost extra work at write or compaction time.

File size is a balance. Small files add overhead per file: a request to open it, a footer to read, an entry to plan. Very large files limit parallelism and make skipping coarser. Targets between a few hundred megabytes and about 1 GB are common, which is why compaction matters.

The optimizer also needs statistics about the data: row counts and estimates of distinct values per column. With them it can choose join order and decide whether to send a small table, such as dim_customer, to every worker (the machines that run parts of the query in parallel) instead of shuffling both sides of a join, that is, redistributing their rows across workers by the join key. Many engines collect these with an ANALYZE command.

Reading a plan shows what really happened. EXPLAIN shows the plan without running it, including the filter pushed into each table scan. EXPLAIN ANALYZE runs the query and reports rows and bytes read per stage. Compare the bytes read with the table's size: if a one-day query reads the whole table, pushdown failed.

Key terms

  • Predicate pushdown: passing a filter to the table reader so data can be skipped before it is read.
  • Projection pushdown: reading only the columns a query uses.
  • File skipping: dropping files whose column bounds cannot match a filter.
  • Clustering: placing similar values together, by sorting or Z-ordering, so statistics can skip.
  • Table statistics: row counts and distinct value estimates used by the optimizer.
  • Query plan: the steps an engine will run, shown by EXPLAIN.

Example

shop.orders holds a little over three months of data, partitioned by day(ordered_at).

-- slow: the function hides ordered_at, so every file is read
SELECT sum(amount) FROM shop.orders
WHERE format_datetime(ordered_at, 'yyyy-MM-dd') = '2026-10-28';

-- fast: a range on the column itself prunes to one day,
-- about 1% of the table's files
SELECT sum(amount) FROM shop.orders
WHERE ordered_at >= TIMESTAMP '2026-10-28 00:00:00 UTC'
  AND ordered_at <  TIMESTAMP '2026-10-29 00:00:00 UTC';

-- check: input bytes per stage
EXPLAIN ANALYZE
SELECT sum(amount) FROM shop.orders
WHERE ordered_at >= TIMESTAMP '2026-10-28 00:00:00 UTC'
  AND ordered_at <  TIMESTAMP '2026-10-29 00:00:00 UTC';

Customer lookups filter on customer_id, which is not a partition key. Sorting files by it makes their bounds narrow:

ALTER TABLE shop.orders SET PROPERTIES sorted_by = ARRAY['customer_id'];
-- existing files are sorted when compaction next rewrites them

After large loads, refresh the optimizer's statistics:

-- collect row counts and distinct value estimates
ANALYZE shop.orders;

Pitfalls

  • Functions and casts on filtered columns. Many turn off pruning and skipping without any warning; check the plan.
  • SELECT *. It reads every column, even in a columnar format.
  • Tuning without measuring. Check bytes read with EXPLAIN ANALYZE before and after a change.
  • Sorting by many columns. Only the first one or two get useful bounds.
  • Stale statistics. After large loads, the optimizer may choose a poor join order until statistics are refreshed.

Learn more

  • Trino, EXPLAIN ANALYZE: https://trino.io/docs/current/sql/explain-analyze.html
  • Apache Iceberg, performance: https://iceberg.apache.org/docs/latest/performance/

SQL query engines · Iceberg internals: manifests and planning · Iceberg maintenance · Columnar files and Parquet · Cost and FinOps