Iceberg tables are much slower than MergeTree in the performance tests: subcolumns, sort order, join planning, position deletes

The issue lists subproblems with files like src/Storages/ObjectStorage/StorageObjectStorage.h and IcebergWrites.cpp. Pick one case, reproduce with tests/performance/scripts/perf.py --print-queries tests/performance/<test>.xml, study its plan, and implement the described cause fix. Done when the Iceberg/MergeTree time ratio for that test nears 1.

The new Iceberg configuration of the performance tests (https://github.com/ClickHouse/ClickHouse/pull/128261) runs 200 perf tests a second time with their MergeTree tables created as Iceberg tables, on the same binary and runner type. This issue collects the cases where an Iceberg table is much slower than the same data in MergeTree and the cause is in ClickHouse: each one was reproduced locally on master (26.11.1.956, IcebergLocal and MergeTree side by side) and explained with the query plan and profile events. Rewrite artifacts, noise and slowdowns without a confirmed cause are listed at the end and are not part of this issue.

The ratios are Iceberg time / MergeTree time of the worst query of the test (median client_time), from the CI run of e1b3517dafb90fd7867b9c37fc21256f94d64c79 (https://performance.ci.clickhouse.com/runs/pr-128261-e1b3517dafb90fd7867b9c37fc21256f94d64c79). IcebergS3 is the job-local SeaweedFS. The per-test table of all 199 tests is tests/performance/docs/iceberg_vs_mergetree.md in that pull request. Print a query with tests/performance/scripts/perf.py --print-queries tests/performance/<test>.xml --queries-to-run <#>.

1. Subcolumn reads are disabled for object storage: the whole Map/Array column is read

Fixable: yes.

Test	IcebergLocal	IcebergS3
optimize_functions_to_subcolumns	60.1x (7 ms -> 432 ms, #2)	64.7x (7 ms -> 466 ms, #2)
map_contains_like_subcolumn	25.1x (17 ms -> 417 ms, #2)	25.5x (16 ms -> 419 ms, #0)
map_contains_value_subcolumn	23.3x (102 ms -> 2377 ms, #1)	24.9x (102 ms -> 2535 ms, #1)
map_keys_values_filter_only	22.4x (65 ms -> 1464 ms, #0)	23.5x (65 ms -> 1534 ms, #0)
map_bucketed_serialization	11.2x (43 ms -> 478 ms, #1)	11.4x (43 ms -> 485 ms, #1)
map_has_subcolumn	7.8x (61 ms -> 472 ms, #2)	7.9x (61 ms -> 477 ms, #2)
map_subcolumns_prewhere	3.3x (220 ms -> 717 ms, #4)	3.4x (220 ms -> 758 ms, #4)

has(mapValues(m), 15) (map_contains_value_subcolumn #1): MergeTree rewrites mapValues(m) to the subcolumn m.values and reads 149 KB (0.29 s CPU); the Iceberg table reads the whole map, 1.08 GB from disk and 5.2 GB decompressed (15.6 s CPU). notEmpty(m) (optimize_functions_to_subcolumns): 0.9 MB on MergeTree, 102 MB read and 800 MB decompressed on Iceberg.

Cause: StorageObjectStorage::supportsOptimizationToSubcolumns returns false (src/Storages/ObjectStorage/StorageObjectStorage.h): the Parquet reader cannot serve .size0, .null, .keys, .values without reading the parent column. Parquet stores the keys and the values of a map as separate leaf columns, and the sizes of arrays and maps follow from the repetition levels, so the reader can serve these subcolumns without the rest of the column.

2. ClickHouse does not record the sort order of the data files it writes: no read in order

Fixable: yes.

Test	IcebergLocal	IcebergS3
read_in_order_many_parts	22.5x (26 ms -> 586 ms, #0)	24.2x (26 ms -> 632 ms, #0)
cast_nullable_read_in_order	14.7x (6 ms -> 90 ms, #0)	15.9x (6 ms -> 97 ms, #0)
limit_by_in_pk_order	10.4x (71 ms -> 740 ms, #11)	21.2x (349 ms -> 7395 ms, #10)
parallel_full_sorting_merge_join	6.3x (85 ms -> 536 ms, #4)	6.4x (85 ms -> 547 ms, #4)
read_in_order_virtual_row_filtered	6.1x (3 ms -> 20 ms, #1)	6.8x (3 ms -> 22 ms, #1)

The tables are created with ORDER BY (an Iceberg sort order), and INSERT sorts each block by it (sortBlockByKeyDescription in IcebergWrites.cpp), but the data files it writes have no sort_order_id: system.iceberg_files shows NULL for all of them (32 of 32 files in cast_nullable_read_in_order, 44 of 44 in parallel_full_sorting_merge_join, 8 of 8 in limit_by_in_pk_order). Only compaction preserves a sort_order_id. So IcebergMetadata::isDataSortedBySortingKey returns false, and the plan has a full Sorting where MergeTree has Read type: InOrder, for ORDER BY ... LIMIT, LIMIT BY over the sort key and the inputs of full_sorting_merge.

Fix: set the sort_order_id of the table's sort order on the data files that INSERT writes sorted.

3. Join planning without statistics of Iceberg tables

Fixable: yes, as four separate changes (a-d).

Test	IcebergLocal	IcebergS3	Cause
ie_join_selectivity_key_choice	178.0x (14 ms -> 2527 ms, #0)	170.5x (14 ms -> 2420 ms, #0)	a
propagate_predicate_across_equi_join	135.9x (4 ms -> 598 ms, #0)	156.7x (4 ms -> 689 ms, #0)	b, c, d
join_seal_gated_reading	26.0x (22 ms -> 570 ms, #2)	28.6x (22 ms -> 626 ms, #2)	d
lazy_materialization_join	11.6x (54 ms -> 619 ms, #2)	15.9x (54 ms -> 849 ms, #2)	b, partly

a. Inequality join keys chosen without column statistics. l.a1 < r.b1 AND l.a2 < r.b2 AND l.a3 < r.b3 with b3 in a range 1000 times narrower: MergeTree (with auto_statistics_types) makes IEJoin sort by a2/b2 and check a3 < b3, the Iceberg table falls back to the first two conditions, a1/b1 and a2/b2, for the same 60000 rows: 0.01 s vs 2.6 s CPU. Iceberg manifests keep the lower and upper bounds of every column per data file, which is enough to estimate the selectivity of such range conditions; the object storage engines implement no getConditionSelectivityEstimator. use_iceberg_manifest_statistics does not change it.

b. No row estimates by default. The join planner shows rows estimated no stats for every Iceberg read, so propagate_predicate_across_equi_join #0 builds the hash table from all 30M rows of the unfiltered table, and lazy_materialization_join #2 joins the two large tables before the selective one. use_iceberg_manifest_statistics (new in 26.11, off by default) estimates the rows from the record_count in the manifests: with it, the join order of propagate_predicate_across_equi_join is right and the query goes from 0.825 s to 0.276 s locally (MergeTree: 0.008 s); lazy_materialization_join only improves from 1.56 s to 1.43 s (MergeTree: 0.22 s), the rest of that gap is not explained yet. Consider enabling the setting by default.

c. Equi-join predicate propagation does not reach object storage reads. For (SELECT * FROM orders WHERE k = 15000000) AS o JOIN lineitem AS l ON o.k = l.k, MergeTree adds k = 15000000 to the read of lineitem (Propagated equi-join filter, one granule); the Iceberg read of lineitem gets only the runtime filter, also with use_iceberg_manifest_statistics = 1, and reads 30M rows. The same predicate written by hand prunes 28 of 29 files and 7 of the 8 pages of the remaining one.

d. Runtime join filters do not prune files, row groups or pages. join_seal_gated_reading #2: on MergeTree the runtime filter of the join key drops 10864 of 10987 granules by index analysis (RuntimeFilterIndexAnalysisReads, RuntimeFilterGranulesDropped); on Iceberg it filters 80.5M rows after reading all 93 data files and 2769 pages. The key range of the build side (k BETWEEN 45000000 AND 45999999) as a static predicate prunes 84 files and 28 pages of the Iceberg table, so the runtime filter can use the same file min/max and Parquet statistics.

4. A large position delete file is applied again on every read

Fixable: yes.

Test	IcebergLocal	IcebergS3
lightweight_delete	71.3x (6 ms -> 456 ms, #0)	68.4x (6 ms -> 438 ms, #0)

DELETE FROM lwd_test WHERE id < 9999999 on 10M rows writes one position delete file with 9999999 positions over the 10 data files. Every query applies it again: SELECT id, length(value) FROM lwd_test ORDER BY id LIMIT 1 spends 2.56 s CPU to return the 2 remaining rows, while the lightweight delete mask of MergeTree costs 11 ms over the same 10M rows. Applying position deletes needs a cached per-file representation (e.g. a bitmap) and skipping data files that are deleted entirely; compaction removes the cost too but is not automatic.

5. No secondary indexes on Iceberg tables

Fixable: not in the current Iceberg read path. The Iceberg specification has no secondary indexes (they could live in Puffin files); the pruning of an Iceberg table is per data file (partitions, column bounds) and per Parquet row group and page, coarser than the 8192-row granules of MergeTree.

Test	IcebergLocal	IcebergS3
text_index_multi_search_any	33.0x (4 ms -> 138 ms, #2)	33.0x (4 ms -> 139 ms, #2)
text_index_like	23.8x (4 ms -> 83 ms, #1)	28.6x (4 ms -> 100 ms, #1)
text_index_lazy_wide_granule	21.2x (8 ms -> 162 ms, #8)	21.5x (8 ms -> 164 ms, #8)
text_index_like_array	12.6x (11 ms -> 141 ms, #6)	13.4x (11 ms -> 150 ms, #6)
text_index_posting_list_codec	12.4x (4 ms -> 43 ms, #13)	12.5x (4 ms -> 44 ms, #13)
text_index_phrase_cursor	11.4x (8 ms -> 90 ms, #0)	12.7x (8 ms -> 100 ms, #0)
key_condition_single_column_key_point_lookup	4.2x (3 ms -> 13 ms, #1)	5.3x (3 ms -> 17 ms, #1)
primary_key_exclusion_search_non_key_conditions	3.9x (7 ms -> 27 ms, #0)	5.0x (7 ms -> 34 ms, #0)

text_index_multi_search_any #2 reads 106496 rows of MergeTree through the text index and all 10M rows of the Iceberg table. key_condition_single_column_key_point_lookup #1 (id IN (...) of 5 values) prunes on Iceberg too, by row group and page, but reads 1.76M rows where MergeTree reads 38528.

Not included

• Artifacts of the rewrite: Iceberg cannot store LowCardinality, Enum, FixedString and 8/16-bit integers, so the tests on them run on widened types (enum_in_set, enum_string_operation, low_cardinality_many_marks_point_lookups, has_all, prewhere_combine_filters, ...); tests of MergeTree codecs and partitions measure what the rewrite drops (delta_decode, codecs_*, zstd_codec_context_reuse, limit_by_partitions, distinct_partitions).
• Not reproduced locally: has_any_constant_string_needles (14x in CI, 17 ms vs 25 ms locally).
• The cost of the Parquet format, about 2x or less with the same bytes read: string_filters_during_scan (0.73 s vs 1.41 s), a plain single-threaded INSERT of 10M rows (2.0 s vs 3.2 s, the same size on disk).
• Without a confirmed cause: merge_table_large_query (2.5 s vs 3.9 s locally, only about 0.2 s of it in Iceberg metadata events); optimize_time_filter_with_preimage (an Iceberg table stores DateTime as DateTime64(6)).

Related: https://github.com/ClickHouse/ClickHouse/pull/128261