http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Q1 — Network compression (EXCHANGE ...
# questions-and-troubleshooting
g
@Rocky Q1 — Network compression (EXCHANGE SHUFFLE bottleneck)
In our EXPLAIN ANALYZE, 
NonDefaultVariables
shows 
transmission_compression_type: AUTO → NO_COMPRESSION
. The system default is AUTO, so something is overriding it to NO_COMPRESSION for our queries. We are NOT explicitly setting this in our SET_VAR hints. Our hint is:
SET_VAR(cbo_cte_reuse_rate=1, enable_materialized_view_rewrite=false, connector_max_split_size=8388608, pipeline_dop=4, enable_connector_adaptive_io_tasks=false, enable_shared_scan=true)
Question: Which of these hint variables causes 
transmission_compression_type
to flip to NO_COMPRESSION? Is it 
enable_shared_scan=true
? And can we override it per-query with 
SET_VAR(transmission_compression_type=LZ4_FRAME)
alongside our other hints? Would LZ4 compression help when EXCHANGE SHUFFLE is 30% of execution time and moving ~54GB allocated?
Q2 — Aggregation pushdown disabled
EXPLAIN ANALYZE shows 
enable_rewrite_simple_agg_to_hdfs_scan: true → false
and 
enable_scan_predicate_expr_reuse: true → false
in NonDefaultVariables. The system default for both is 
true
, but our queries run with them 
false
. We are not explicitly setting these. Our queries use 
enable_connector_adaptive_io_tasks=false
and 
enable_shared_scan=true
.
Question: Which of our SET_VAR settings is disabling 
enable_rewrite_simple_agg_to_hdfs_scan
? If we restore it to 
true
, will StarRocks push SUM/COUNT aggregations into the Iceberg Parquet scan (reducing rows flowing to EXCHANGE)? For our queries that aggregate 22-25M rows from Iceberg down to ~100K output rows, this seems like it would be a major win.
Q3 — ScheduleTime 99.8%+ with EXCHANGE SHUFFLE bottleneck
For our all-brands queries, EXPLAIN shows:
• `ScheduleTime: 99.82%`of ExecutionTime
• `Network: 159%`of ExecutionTime (multiple BEs' network time overlapping)
• EXCHANGE SHUFFLE at 32% of total time (2.6s out of 9s execution)
The ScheduleTime pattern suggests pipeline backpressure — downstream operators wait for EXCHANGE data. We're at`pipeline_dop=4`.
Question: For Iceberg queries where EXCHANGE SHUFFLE dominates ScheduleTime, is increasing pipeline_dop helpful (more sender/receiver threads) or is there another mechanism — e.g.,
exchange_node_data_chunk_size
,
exchg_node_buffer_size_bytes
, or`transmission_encode_level`— that reduces EXCHANGE stall time? Our previous tests showed DOP=8 was neutral for all-brands but that was with OLAP MVs; now with Iceberg the bottleneck is different.
Q4 — NEST_LOOP_JOIN (CROSS JOIN) with 2-row build side
In 
latest_final_all_brands_product
, the plan has:
NEST_LOOP_JOIN (id=73) [CROSS JOIN, NONE]
<BUILD> total_combined_cte — 1 row (scalar aggregation result)
<PROBE> combined_data — 301K rows
This is a 301K × 1 cross-join, which produces 301K rows. The NEST_LOOP_JOIN takes 520µs which is fine. But we use 
INNER JOIN total_combined_cte tcc ON 1=1
pattern intentionally to broadcast scalar totals.
Question: Is there a more efficient pattern than 
ON 1=1
cross-join for broadcasting a single-row scalar CTE? For example, would 
CROSS JOIN [BROADCAST] total_combined_cte
explicitly force BROADCAST and avoid any chance of NEST_LOOP? And does StarRocks ever convert a 1-row CROSS JOIN to a constant-fold optimization (where the single row's values are treated as literals in the child scan)?
Q5 — Optimal 
connector_max_split_size
for Parquet on S3
We're using 
connector_max_split_size=8388608
(8MB splits) for Iceberg tables on S3. Our Parquet files target 128MB–256MB. So each file generates ~16-32 splits. Our cluster has 3 BEs with 
pipeline_dop=4
= 12 total parallel reader threads.
Question: With 3 BEs × DOP=4 = 12 parallel readers and ~16-32 splits per 128MB file, what's the optimal 
connector_max_split_size
? Is 8MB optimal, or should we match to file size / (BEs × DOP) = 128MB / 12 ≈ 10MB? Does the split size affect S3 
GetObject
request sizes, or does StarRocks always read full Parquet row groups regardless of split boundary?
r
Based on your 
EXPLAIN ANALYZE
and system settings, here is the breakdown of the behaviors you are seeing and how to optimize them. Q1 — Network Compression (
AUTO → NO_COMPRESSION
) * The Cause: The shift from 
AUTO
to 
NO_COMPRESSION
is likely triggered by the 
rpc_compress_ratio_threshold
 (default 
1.1
). When set to 
AUTO
, StarRocks samples the data; if the compression ratio (
uncompressed_size / compressed_size
) is less than 1.1, it disables compression to save CPU. For 54GB of data, if it's already semi-compressed (e.g., Parquet data being moved after decompression in the scan), the sampler might decide the CPU cost isn't worth the small bandwidth gain. * The Shared Scan Link: While 
enable_shared_scan
doesn't directly toggle compression, it changes the pipeline structure. In some versions, the FE reports 
NO_COMPRESSION
if the data volume per "chunk" in the pipeline is too small for the adaptive compressor to kick in. * Recommendation: Yes, you should override this. Set 
SET_VAR(transmission_compression_type=LZ4)
 (or 
LZ4_FRAME
). For a shuffle that accounts for 30% of execution time and moves 54GB, forcing LZ4 is a major win. It will significantly reduce the bytes on the wire at a very low CPU cost. Q2 — Aggregation Pushdown Disabled * The Culprit: 
enable_shared_scan=true
is disabling your aggregation pushdown. * Why: Aggregation pushdown (e.g., 
enable_rewrite_simple_agg_to_hdfs_scan
) modifies the scanner to perform `SUM`/`COUNT` inside the Parquet/Iceberg reader. However, 
enable_shared_scan
is designed to let multiple downstream operators share the same data stream from one scanner. If one operator wants a 
SUM(x)
and another wants raw rows, StarRocks cannot push the aggregation into the scan. * The Win: For your case (22M rows → 100K rows), pushing the aggregation is far more valuable than sharing the scan. Sharing a scan saves S3 I/O, but pushing the aggregation saves the massive cost of shuffling 22M rows. * Action: Try setting 
enable_shared_scan=false
 and ensuring 
enable_rewrite_simple_agg_to_hdfs_scan=true
. This should push the `SUM`/`COUNT` into the Iceberg scan. Q3 — ScheduleTime 99.8% & Exchange Bottlenecks * Diagnosis: A 
ScheduleTime
of 99%+ combined with high 
NetworkTime
indicates Pipeline Backpressure. The execution threads are "Ready" but blocked waiting for buffers. The receiver is likely full, or the sender is waiting for network ACKs. * Tuning Variables: * 
exchg_node_buffer_size_bytes
: The default is often too low for large shuffles (10MB–64MB). Increase this to 
134217728
(128MB) or 
268435456
(256MB) to absorb network bursts. * 
pipeline_dop
: Increasing DOP to 8 might help if you have spare CPU, but if the bottleneck is the 54GB network move, more threads may just increase contention. * 
max_transmit_batched_bytes
: Increase this (e.g., to 1MB or 2MB) to batch more data into fewer RPC calls, reducing the overhead of the network stack. Q4 — NEST_LOOP_JOIN (1-row Cross Join) * Efficiency: StarRocks is already handling this efficiently. Even though it's a 
NEST_LOOP_JOIN
, since the build side is 1 row, it is effectively a Broadcast and the cost is negligible (as seen in your 520µs result). * Alternative: You can use 
CROSS JOIN [BROADCAST] total_combined_cte
 to be explicit, but the optimizer already defaults to this for 1-row results. * Constant Folding: StarRocks does not typically "constant-fold" a join into the scanner (i.e., treating the 1-row total as a literal in the scan's filter) because the join happens after the scan. However, for a 1-row CTE, the memory overhead is non-existent. Q5 — Optimal 
connector_max_split_size
for S3 * The 8MB Problem: 8MB is likely too small for S3 Parquet. Every split results in at least one S3 
GetObject
request (plus metadata calls). With 8MB splits on a 128MB file, you are making 16 requests.
* The Parquet Factor: StarRocks reads Parquet by Row Group. If your 128MB Parquet file has 128MB row groups (common), all 16 splits will end up contending for the same row group data. * Recommendation: Match the split size to your Row Group size or file size. For 128MB files, try 
connector_max_split_size=67108864
(64MB) or even 128MB. * Calculation: With 3 BEs and 
DOP=4
, you have 12 slots. If your query hits multiple files, 64MB-128MB splits will still provide plenty of work to keep those 12 threads busy without the overhead of tiny S3 requests. References • administration/management/BE_configuration.md • unloading/Spark_connector.md • administration/management/FE_configuration.md
g
@Rocky Q1 — The FULL OUTER JOIN runtime filter limitation:
Our query's 
fact_insights_market_product_seller_daily_poc
CTE (ICEBERG_SCAN id=53, 84.87M rows) feeds into a FULL OUTER JOIN (id=58 
BUCKET_SHUFFLE(S)
) as the PROBE side. This scan has zero runtime filter. The other large scan 
fact_insights_market_product_daily_poc
(ICEBERG_SCAN id=20, 106M rows) feeds into BROADCAST INNER JOINs and gets a 42.77% runtime filter. Is it correct that runtime filters cannot be pushed to the probe side of a FULL OUTER JOIN in StarRocks? If so, what query restructuring would allow a runtime filter on fact_seller_daily — e.g., could we change the FULL OUTER JOIN to a LEFT OUTER JOIN + UNION approach, or add an explicit predicate to the CTE that allows filter pushdown?
Q2 — Why 155 GB allocated memory / ScheduleTime 99.96%:
Our query 
QueryAllocatedMemoryUsage: 155 GB
, 
ScheduleTime: 99.96%
(severe pipeline back-pressure). The two large fact scans output 84.87M + 106.35M rows into aggregation nodes. What is causing 155 GB allocation — is this the hash aggregation table for GROUP BY 
market_product_id
over 190M rows? Our cluster has 3 BE nodes. Would Iceberg date partitioning on these two fact tables (to skip non-queried years) meaningfully reduce both the scan rows and the memory allocation?
Q3 — Date partitioning to skip non-queried years:
Both fact tables scan all historical data (84.87M + 106.35M rows) even though our query has explicit date range predicates 
date BETWEEN '2025-01-01' AND '2025-12-31' OR date BETWEEN '2024-01-01' AND '2024-12-31'
. We use Spark to write these Iceberg tables (no Hive partitioning). If we add Iceberg partitioning 
PARTITIONED BY (years(date))
to both fact tables, will StarRocks 3.3 use partition pruning to skip all data outside 2024-2025? What's the recommended partition spec for this use case — 
years(date)
, 
months(date)
, or 
identity(year)
— to minimize partition overhead while maximizing pruning?
Q4 — 
enable_scan_predicate_expr_reuse: true → false
cluster override:
Our EXPLAIN shows 
enable_scan_predicate_expr_reuse: true -> false
as a cluster-level NonDefaultVariable. Our query uses IF(date BETWEEN X AND Y, ..., 0) as many as 30+ times per fact scan — both 
2025-01-01 AND '2025-12-31'
and 
2024-01-01 AND '2024-12-31'
date ranges appear in every metric. What does 
enable_scan_predicate_expr_reuse
do exactly, and would enabling it via SET_VAR help avoid re-evaluating the same date BETWEEN predicates 30+ times per row? Should we try 
enable_scan_predicate_expr_reuse=true
in our SET_VAR hint?
Q5 — pipeline_dop strategy for memory vs throughput:
With 
pipeline_dop=4
(from SET_VAR hint), 
ScheduleTime=99.96%
, and 155 GB memory across 3 BEs (~52 GB/BE), we're in severe back-pressure. We also have 
pipeline_dop=16
in the session-level SET (but the hint overrides to 4). Would increasing pipeline_dop to 8 or 16 help spread the aggregation state across more threads and reduce per-thread memory peaks? Or would higher DOP worsen the situation by creating more concurrent scans that compete for memory?
r
Based on the 
EXPLAIN ANALYZE
metrics and your current query patterns, here is a breakdown of the performance bottlenecks and recommended optimizations for your StarRocks 3.3 environment. Q1 — FULL OUTER JOIN Runtime Filter Limitation * The Limitation: It is correct that runtime filters cannot be pushed to the probe side of a FULL OUTER JOIN. By definition, a 
FULL OUTER JOIN
must return rows from the probe side even if they do not match the build side. If StarRocks applied a runtime filter at the scan level to prune probe-side rows that don't match the build side, those "unmatched" rows would be lost, leading to incorrect results. * Restructuring: To get filter pushdown, you can restructure the query using a 
UNION ALL
approach: 1. Perform a LEFT JOIN (Fact A to Fact B) where Fact A is the probe side (runtime filters can be pushed here if B is the build side). 2. 
UNION ALL
with a RIGHT ANTI JOIN (Fact A to Fact B) to get the rows only in Fact B. Alternatively, if you can determine that one table always contains the superset of keys or if you only need matches, switching to a 
LEFT JOIN
or 
INNER JOIN
will immediately enable runtime filters on the probe side. Q2 — Memory Allocation & ScheduleTime Backpressure * The Cause: The 155 GB allocation is almost certainly the Hash Table used for the 
GROUP BY market_product_id
aggregation. When you aggregate ~190M rows (84M + 106M) into a single aggregation node, StarRocks builds a massive in-memory hash table to track every unique 
market_product_id
. * The Backpressure: 
ScheduleTime: 99.96%
confirms your BE threads are spending all their time waiting for memory or for the aggregation to finish processing the massive volume of rows. * The Win: Partitioning is the most effective way to solve this. Reducing the 190M rows at the source (via pruning) directly reduces the size of the hash table and the amount of data moving through the pipeline. Q3 — Iceberg Date Partitioning Strategy * Recommendation: Use 
PARTITIONED BY (years(date))
 (Iceberg Hidden Partitioning). * StarRocks 3.3 Support: StarRocks 3.3 fully supports Iceberg's hidden partitions. When you use a predicate like 
date BETWEEN '2025-01-01' AND '2025-12-31'
, StarRocks will recognize that this covers exactly the 2025 year partition and skip all other files. * Granularity: 
years(date)
is optimal for your query pattern. 
months(date)
would provide even finer pruning but creates more metadata overhead. Since your queries target full years, 
years(date)
strikes the best balance. Q4 — 
enable_scan_predicate_expr_reuse
 * Definition: This variable controls whether the scanner reuses the results of complex expressions that appear multiple times in the query. * Your Case: Since you have 30+ metrics using the same 
IF(date BETWEEN X AND Y, ...)
logic, StarRocks is likely re-evaluating that date range check for every single column per row. * Action: You should set 
enable_scan_predicate_expr_reuse=true
. This allows the scanner to evaluate the 
date BETWEEN
predicate once and reuse the boolean result for all 30+ metrics in that row, significantly reducing CPU cycles at the scan stage. Q5 — 
pipeline_dop
Strategy: Memory vs. Throughput * DOP and Memory: Increasing 
pipeline_dop
(e.g., to 16) will increase memory pressure. Each pipeline instance maintains its own buffers and partial aggregation hash tables. With 155 GB already allocated, a higher DOP may cause your BEs to hit their 
mem_limit
and trigger a query failure or heavy spilling. * Recommendation: Keep 
pipeline_dop
at 4 or 8 until you implement partitioning. * If you implement partitioning and the row count drops, you can increase DOP to 16 to maximize throughput.
* If you remain at 190M rows, consider enabling 
enable_spill=true
 to allow the aggregation to offload some of that 155 GB to disk, which will reduce the 
ScheduleTime
backpressure at the cost of some I/O. Summary of Recommended Hints
Copy code
sql
SELECT /*+ SET_VAR(
  enable_scan_predicate_expr_reuse=true,
  transmission_compression_type='LZ4',
  pipeline_dop=4,
  enable_shared_scan=false,
  enable_rewrite_simple_agg_to_hdfs_scan=true
) */ ...
References • sql-reference/System_variable.md • faq/resource_isolation_faq.md • knowledge/usage/best-practice.md • using_starrocks/caching/query_cache.md • faq/Sql_faq.md
6 Views
Open in Slack
PreviousNext
Powered by