StarRocks Join Performance Gap: Paimon/Iceberg vs Internal Tables - Root Causes & Fixes

When testing query performance between lake tables and StarRocks internal tables, the author found that after enabling cache, single-table query latency on lake tables nearly matches internal tables. However, for complex multi-table joins, a large performance gap persists.

This article dissects the deep technical reasons why StarRocks queries on Paimon/Iceberg (Parquet) tables via Catalog, even with cache select pre-warming, still exhibit far lower multi-table join performance than StarRocks internal tables (.dat format). The core lies in understanding fundamental differences in storage design, data layout, cache mechanisms, execution engine, and how these differences propagate to join performance.

1. Storage Format Differences (Core Root Cause)

StarRocks internal tables use the proprietary .dat format (SRF, StarRocks File Format), while Paimon/Iceberg use Parquet. Their design goals and optimization directions differ completely, directly determining column data read/processing efficiency.

Key Principle

The essence of a join is column data scan + hash/merge computation . SRF is deeply adapted for StarRocks' vectorized execution engine: column data is stored as vector arrays, so after reading, no format conversion is needed; data enters join computation directly.

Parquet, as a universal format, requires metadata parsing → decompression → conversion of generic column vectors to StarRocks-compatible vector format upon reading. This conversion consumes significant CPU, and during multi-table joins the CPU overhead compounds exponentially.

2. Data Layout & Partition/Bucket Adaptability Differences

StarRocks query performance heavily relies on "data layout locality," but lake table layout logic is completely decoupled from StarRocks' Tablet architecture.

1. StarRocks Internal Table Layout Advantages

Internal table data is organized hierarchically: Database → Table → Partition → Tablet:

Tablet is the smallest storage unit (bound to BE nodes), achieving 100% data localization.

Data organized by Bucket Key + Sort Key ; if the join key matches the Sort Key, Merge Join can be triggered directly (no hash table build, performance far exceeds Hash Join).

Buckets map 1:1 with BE nodes, so joins require little or no cross-node shuffle, reducing network overhead.

2. Lake Table (Paimon/Iceberg) Layout Disadvantages

After Catalog mapping, StarRocks has "no control" over lake table layout:

Lake table partitioning/bucketing is designed for "lakehouse integration" (e.g., Paimon bucketing adapts to Flink writes), unrelated to StarRocks Tablet architecture; data cannot bind to BE nodes.

StarRocks only perceives lake table "Partitions," not "Row Group" level layout. Predicate pushdown stops at partition level (not row group), causing more irrelevant data scans.

Lake tables lack StarRocks Sort Keys, so joins can only use Hash Join (requiring hash table build, consuming large memory+CPU). Multi-table joins must pull full lake table data to join nodes, causing shuffle overhead to explode.

Key Principle

Merge Join performance is 5-10x Hash Join (especially for large table joins). Internal tables leverage Sort Key to trigger Merge Join; lake tables are limited to Hash Join — this is the core watershed for join performance gap.

3. Cache Mechanism Adaptability Differences (Core Reason Why Pre-warming Still Slow)

The cache select pre-warmed "cache" means completely different things for internal vs lake tables — cached content, format, and utilization differ vastly.

1. Internal Table Cache: Native Adaptation, Zero Conversion Overhead

After pre-warming, cache holds: SRF-format column data blocks + index info (bitmap/dictionary) , natively compatible with StarRocks execution engine.

Cache location: BE node local memory (long-lived, hit rate 99%+).

On read: directly fetch column data blocks from memory, no parsing/conversion, directly usable for join computation (e.g., ordered column data for Merge Join, hash table build for Hash Join).

2. Lake Table Cache: Poor Adaptation, Extra Overhead Remains

After pre-warming, cache holds: Parquet-parsed raw column vectors (must first parse Parquet metadata → decompress → convert generic column vectors to StarRocks format). The parsing process itself consumes CPU.

Cache location: BE node temporary memory (StarRocks' external table cache policy is conservative: short TTL, low cache space allocation).

Missing cache content: only column data cached, no StarRocks index info (bitmap/dictionary). Joins still require re-filtering data and building hash tables.

Low cache utilization: Parquet organizes data by "Row Group," while StarRocks caches by "column block." Mismatch leads to low hit rates (even after pre-warming, 30%-50% of column data must be re-fetched and parsed).

Key Principle

Cache pre-warming only solves "object storage network I/O overhead," but Parquet parsing and format conversion CPU overhead remains. In multi-table joins, each lake table's CPU parsing overhead stacks up, ultimately making performance slower than internal tables.

4. Execution Engine Optimization Bias (Missing Full-Link Optimization)

StarRocks execution engine applies "full-link OLAP optimization" to internal tables, while optimization capability for lake tables is severely limited. This gap amplifies during multi-table joins.

1. Internal Table Full-Link Optimization

Predicate Pushdown to the Extreme : Can push down to Tablet → column block → row level, scanning only the minimal qualifying data range.

Column Pruning at Zero Cost : Reads only columns needed for join; SRF format skips irrelevant columns with no extra overhead.

Intelligent Join Strategy Selection : Based on internal table statistics (row count, column cardinality, data distribution), prefers Merge Join (when Sort Key matches) or small-table-build Hash Join (reducing memory footprint).

Native Vectorized Execution Adaptation : SRF column data stored as vector arrays, directly fed into vectorized computation, achieving 80%+ CPU utilization.

2. Lake Table Optimization Gaps

Incomplete Predicate Pushdown : Only pushes to lake table "Partition" level, not Parquet "Row Group/Page" level, forcing more data scans.

Column Pruning with Extra Overhead : Although columns can be pruned, must first parse Parquet metadata (Manifest files) to locate column positions, then read — overhead 3-5x internal tables.

Single Join Strategy : Cannot leverage lake table ordering to trigger Merge Join; only Hash Join available (large table joins cause hash table spill to disk, performance plummets).

Poor Vectorized Adaptation : Parquet-parsed column vectors adapt to StarRocks vectorized engine at only ~60%, CPU utilization below 50%.

Key Principle

Multi-table joins involve "predicate filtering → column pruning → data shuffle → hash/merge computation" stages. Internal tables have dedicated optimizations at every stage; lake tables incur extra overhead at every stage, culminating in a "performance avalanche."

5. Metadata & Statistics Differences (Missing Foundation for Join Optimization)

StarRocks join optimization heavily relies on precise metadata statistics, but lake table metadata cannot be fully utilized by StarRocks.

1. Internal Table Metadata Advantages

Metadata stored in FE metadata service, updated in real-time (schema, buckets, sort keys, row count, column cardinality, data distribution).

Execution engine uses statistics to optimize: ① choose optimal join order (small table as build side, reducing hash table size); ② estimate hash table memory usage to avoid spill; ③ precisely prune partitions/Tablets.

2. Lake Table Metadata Disadvantages

Metadata stored in object storage (Paimon Snapshots, Iceberg Manifest files). StarRocks reads via Catalog, obtaining only basic info (schema, partitions), unable to retrieve critical stats like column cardinality and data distribution.

Lake table metadata has cache latency (Catalog sync cycle typically 30s+), stats become stale. Engine falls back to "default strategy" (e.g., join by table name order), easily causing "large table builds hash table" (memory spill → disk → performance crash).

Lake table multi-version management (ACID) adds metadata parsing overhead: queries must first parse version metadata, filter expired data. Multi-table joins repeat this per table, stacking overhead.

Key Principle

Join order optimization can yield orders-of-magnitude speedup (e.g., "small table joins large table" 10x faster than reverse). Internal tables optimize precisely; lake tables "blindly compute," a major reason for poor multi-table join performance.

6. Data Consistency & Version Management Extra Overhead

As ACID lake tables, Paimon/Iceberg version management overhead amplifies during multi-table joins.

Internal tables: data is "eventually consistent" after write; reads need no version handling, directly read latest data.

Lake tables: StarRocks must first parse lake table version metadata (e.g., Paimon Snapshot ID, Iceberg Manifest List), determine file list, then filter expired version data. This adds "metadata read → version filter" two-step overhead.

Multi-table joins require each lake table to execute version parsing/filtering, stacking overhead, making join "startup time" 5-10x longer than internal tables.

Core Summary (Deep Principle Extraction)

The join performance gap between StarRocks internal tables (.dat) and lake tables (Parquet) is essentially a gap between a closed-loop optimization system and a general compatibility system :

Storage Layer : Custom SRF format optimized for StarRocks engine; Parquet's generality sacrifices adaptability, high column read/parse overhead.

Layout Layer : Internal Tablet+Sort Key enables Merge Join; lake tables limited to Hash Join — core performance gap.

Cache Layer : Internal cache natively adapted; lake cache only solves I/O, not CPU parsing/conversion overhead.

Execution Layer : Internal full-link OLAP optimization; lake optimization missing, multi-table join overhead stacks.

Metadata Layer : Internal precise statistics drive join optimization; lake statistics missing, forcing "blind computation."

Performance Optimization Directions (Targeted Fixes for Slow Lake Table Joins)

To improve lake table join performance, consider these dimensions ( cannot match internal tables, but can narrow the gap ):

Storage Layer: When creating Paimon tables, specify file.format=parquet + write.sort-by=Join键, making Parquet row groups ordered by join key to trigger StarRocks Merge Join.

Cache Layer: Increase StarRocks cache disk size.

Execution Layer: Manually specify join order ( /*+ JOIN_ORDER(t1, t2, t3) */) to make small table the build side.

Architecture Layer: Import lake table data into StarRocks internal tables via INSERT INTO then join (sacrifice real-time for performance).