http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> How can we enable query result cach...
# questions-and-troubleshooting
g
@Rocky How can we enable query result cache for iceberg queries in the shared data architecture?
r
In StarRocks, enabling "query result cache" for Iceberg queries involves several layers of caching. While the term "Query Cache" specifically refers to result-set/intermediate result caching, Iceberg performance in a shared-data architecture is primarily driven by the Data Cache (block-level) and Metadata Cache. 1. Data Cache (Unified Block Cache) In shared-data architecture (v3.0+) and for external catalogs like Iceberg, StarRocks uses a block-level cache on the BE/CN local disks to avoid repeated remote storage (S3/HDFS) access. From v3.4.0 onwards, StarRocks uses a unified Data Cache instance for both internal cloud-native tables and external catalogs. * How to enable: It is typically enabled by default. Check your 
be.conf
for:
Copy code
conf
    starlet_use_star_cache = true
* Warmup: You can proactively load Iceberg data into the cache using the 
CACHE SELECT
command:
Copy code
sql
    CACHE SELECT * FROM iceberg_catalog.db.table WHERE ...;
2. Iceberg Metadata Cache Iceberg queries rely heavily on metadata (manifest files). StarRocks caches this metadata in memory and on disk. * Catalog Properties: When creating or altering the Iceberg catalog, ensure metadata caching is enabled:
Copy code
sql
    CREATE EXTERNAL CATALOG iceberg_catalog
    PROPERTIES (
        "type" = "iceberg",
        "enable_iceberg_metadata_cache" = "true", -- Enabled by default
        "iceberg_table_cache_refresh_interval_sec" = "60" -- Interval for freshness check
    );
* Automatic Refresh (v3.3+): StarRocks can periodically refresh metadata in the background to ensure the cache stays hot: * Set the FE configuration 
enable_background_refresh_connector_metadata
to 
true
. * Adjust 
background_refresh_metadata_interval_millis
(default is 10 minutes). 3. Query Cache (Result-Set Level) The Query Cache caches the computation results (e.g., aggregations). * Status for External Tables: Officially, the StarRocks Query Cache is designed for native OLAP tables and cloud-native tables (internal tables in shared-data mode). * Official Documentation: As of the latest documentation (v3.4/v4.0), it states: "The query cache does not support queries on external tables." * Alternative: If you need the equivalent of a "result cache" for Iceberg with high performance and data consistency, the recommended approach is to use Asynchronous Materialized Views. These can store the pre-computed results of Iceberg queries and refresh automatically when the Iceberg table changes. Summary Checklist for Iceberg Acceleration: Cache Type | Scope | How to Enable / Check Data Cache | Raw data blocks | 
starlet_use_star_cache = true
in 
be.conf
Metadata Cache | Manifest/Snapshots | Catalog property 
"enable_iceberg_metadata_cache" = "true"
Distributed Plan | Planning Speed | 
SET plan_mode = 'distributed';
(for v3.3+ with large metadata) Query Cache | Result sets | 
SET enable_query_cache = true;
(limited to internal tables) For most Iceberg scenarios in shared-data clusters, ensuring the Data Cache has sufficient disk space and the Metadata Cache is configured for periodic refresh provides the best performance. References • release_notes/release-3.4.md • using_starrocks/caching/block_cache.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/feature-support-data-lake-analytics.md
g
@Rocky We have CN nodes ( not BE nodes) , we have already created catalog for iceberg but not getting query result cache output in 50-100 millie seconds
r
To achieve sub-second latency (50–100ms) for Iceberg queries in a shared-data architecture with CN nodes, it is important to distinguish between the Query Result Cache (which caches final output) and the Data/Metadata Caches (which cache the building blocks). In StarRocks, the 
enable_query_cache
(result-set cache) feature does not currently support external tables (like Iceberg). It is restricted to internal OLAP and cloud-native tables. To reach your 50–100ms performance goal, you should focus on the following three strategies: 1. Use Asynchronous Materialized Views (Recommended) This is the only reliable way to get 50ms response times on Iceberg data. An MV stores the pre-computed results of your Iceberg query in StarRocks' internal format. * Why: It bypasses Iceberg metadata parsing and remote data access entirely. * Setup:
Copy code
sql
    CREATE MATERIALIZED VIEW iceberg_mv
    REFRESH ASYNC EVERY (INTERVAL 1 HOUR) -- Adjust based on freshness needs
    AS
    SELECT ... FROM iceberg_catalog.db.table;
* Query Rewrite: If configured, StarRocks can automatically rewrite your queries against the Iceberg table to hit the MV instead. 2. Verify CN Data Cache (Block Cache) On CN nodes, data from Iceberg is cached at the block level on local disks. If this is not configured, every query must fetch data from S3/HDFS, making 100ms impossible. * Check `cn.conf`: Ensure 
storage_root_path
is set to a high-performance SSD directory.
Copy code
properties
    storage_root_path = /data/starrocks/storage
* Check Cache Stats: Run your query and then check the profile or 
sys.pipes
(in newer versions) to see if 
DataCacheReadBytes
is high and 
RemoteReadBytes
is low. * Warmup: Use 
CACHE SELECT ...
to pre-load the data into the CN's local disks. 3. Optimize Metadata Caching (FE & CN) Direct Iceberg queries often spend most of their time in "Planning" (parsing manifest files). * Enable Disk Metadata Cache (FE): By default, metadata is in memory. For large Iceberg tables, enable disk caching on the FE:
Copy code
sql
    -- FE configuration
    ADMIN SET FRONTEND CONFIG ("enable_iceberg_metadata_disk_cache" = "true");
* Refresh Interval: If your data changes frequently, ensure the FE is polling for changes so it doesn't have to perform a "cold" metadata fetch during your query:
Copy code
sql
    ALTER CATALOG iceberg_catalog SET ("iceberg_table_cache_refresh_interval_sec" = "60");
* Distributed Planning (v3.3+): If your Iceberg table has thousands of manifest files, ensure StarRocks is using distributed planning:
Copy code
sql
    SET plan_mode = 'distributed'; -- Allows CNs to help the FE parse metadata
Why you aren't seeing 50-100ms currently: Even with all caches (Metadata + Data) enabled, a direct Iceberg query usually takes 200ms–1s because the FE still needs to validate snapshots and the execution engine needs to process the cached blocks. To get under 100ms, you almost always need to use a Materialized View. References • data_source/catalog/iceberg/iceberg_catalog.md • introduction/Architecture.md • data_source/feature-support-data-lake-analytics.md • deployment/shared_data/s3.md • using_starrocks/Materialized_view/Materialized_view.md**.md) References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/feature-support-data-lake-analytics.md • release_notes/release-3.4.md
g
@Rocky Which icebeg properties would you suggest for below query and table? DDL -
Copy code
CREATE TABLE adhoc.hourly_sales_poc (
order_hour_utc TIMESTAMP,
order_date_utc DATE,
listing_id STRING,
asin STRING,
market_product_id BIGINT,
master_product_id BIGINT,
customer_id BIGINT,
vendor_id BIGINT,
merchant_id STRING,
customer_type INTEGER,
customer_state_id INTEGER,
marketplace_id INTEGER,
quantity BIGINT,
order_items BIGINT,
converted_revenue DECIMAL(18,6),
converted_revenue_tax_adjustment DECIMAL(18,6),
converted_revenue_tax_adjusted DECIMAL(18,6),
local_currency_symbol STRING,
local_currency_code STRING,
local_revenue DECIMAL(18,6),
local_revenue_tax_adjustment DECIMAL(18,6),
local_revenue_tax_adjusted DECIMAL(18,6),
converted_revenue_aed DECIMAL(18,6),
converted_revenue_tax_adjustment_aed DECIMAL(18,6),
converted_revenue_tax_adjusted_aed DECIMAL(18,6),
converted_revenue_ars DECIMAL(18,6),
converted_revenue_tax_adjustment_ars DECIMAL(18,6),
converted_revenue_tax_adjusted_ars DECIMAL(18,6),
converted_revenue_aud DECIMAL(18,6),
converted_revenue_tax_adjustment_aud DECIMAL(18,6),
converted_revenue_tax_adjusted_aud DECIMAL(18,6),
converted_revenue_cad DECIMAL(18,6),
converted_revenue_tax_adjustment_cad DECIMAL(18,6),
converted_revenue_tax_adjusted_cad DECIMAL(18,6),
converted_revenue_cny DECIMAL(18,6),
converted_revenue_tax_adjustment_cny DECIMAL(18,6),
converted_revenue_tax_adjusted_cny DECIMAL(18,6),
converted_revenue_eur DECIMAL(18,6),
converted_revenue_tax_adjustment_eur DECIMAL(18,6),
converted_revenue_tax_adjusted_eur DECIMAL(18,6),
converted_revenue_gbp DECIMAL(18,6),
converted_revenue_tax_adjustment_gbp DECIMAL(18,6),
converted_revenue_tax_adjusted_gbp DECIMAL(18,6),
converted_revenue_jpy DECIMAL(18,6),
converted_revenue_tax_adjustment_jpy DECIMAL(18,6),
converted_revenue_tax_adjusted_jpy DECIMAL(18,6),
converted_revenue_usd DECIMAL(18,6),
converted_revenue_tax_adjustment_usd DECIMAL(18,6),
converted_revenue_tax_adjusted_usd DECIMAL(18,6),
sold_by_threepn BOOLEAN,
order_count BIGINT,
buyer_count BIGINT,
sns_order_count BIGINT,
source STRING,
lob_id INT,
converted_revenue_krw DECIMAL(18,6),
converted_revenue_tax_adjustment_krw DECIMAL(18,6),
converted_revenue_tax_adjusted_krw DECIMAL(18,6),
converted_revenue_pln DECIMAL(18,6),
converted_revenue_tax_adjustment_pln DECIMAL(18,6),
converted_revenue_tax_adjusted_pln DECIMAL(18,6),
converted_revenue_sek DECIMAL(18,6),
converted_revenue_tax_adjustment_sek DECIMAL(18,6),
converted_revenue_tax_adjusted_sek DECIMAL(18,6)
) USING ICEBERG
PARTITIONED BY (customer_id)
TBLPROPERTIES (
  'write.format.default' = 'parquet',
  'write.parquet.compression-codec' = 'zstd',
  'write.target-file-size-bytes' = '268435456',
  'write.parquet.row-group-size-bytes' = '134217728',
  'write.distribution-mode' = 'range',
  'write.metadata.metrics.default' = 'truncate(16)',
  'write.metadata.metrics.column.customer_id' = 'full',
  'write.metadata.metrics.column.marketplace_id' = 'full',
  'write.metadata.metrics.column.market_product_id' = 'full',
  'write.metadata.metrics.column.lob_id' = 'full',
  'write.metadata.metrics.column.order_hour_utc' = 'full',
  'write.metadata.metrics.column.order_date_utc' = 'full',
  'write.parquet.bloom-filter-enabled.column.customer_id' = 'true',
  'write.parquet.bloom-filter-enabled.column.marketplace_id' = 'true',
  'write.parquet.bloom-filter-enabled.column.market_product_id' = 'true',
  'write.parquet.bloom-filter-enabled.column.lob_id' = 'true'
);
query -
Copy code
SELECT
    hs.market_product_id,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', converted_revenue, 0)) AS revenue_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', converted_revenue, 0)) AS revenue_comparison,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', converted_revenue_tax_adjusted, 0)) AS revenue_tax_adj_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', converted_revenue_tax_adjusted, 0)) AS revenue_tax_adj_comparison,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', quantity, 0)) AS units_sold_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', quantity, 0)) AS units_sold_comparison,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', order_count, 0)) AS orders_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', order_count, 0)) AS orders_comparison,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', buyer_count, 0)) AS buyer_count_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', buyer_count, 0)) AS buyer_count_comparison,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', sns_order_count, 0)) AS sns_order_count_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', sns_order_count, 0)) AS sns_order_count_comparison,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', order_items, 0)) AS order_items_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', order_items, 0)) AS order_items_comparison
FROM adhoc.hourly_sales_poc hs
    WHERE hs.customer_id = 2
        AND hs.lob_id = 2
        AND hs.order_hour_utc >= '2023-01-01 07:00:00'
        AND hs.order_hour_utc < '2025-01-01 07:00:00'
GROUP BY hs.market_product_id
r
To achieve 50-100ms latency on this specific query, your current Iceberg properties are a good start (especially the Bloom filters and metrics), but you need to optimize how the data is sorted within the partitions and how StarRocks caches the metadata. Here are the specific recommendations for your table and StarRocks configuration: 1. Improved Iceberg Table Properties While you have Bloom filters, the query performs a wide range scan on 
order_hour_utc
(spanning two years) and filters on 
lob_id
. Without a sort order, StarRocks must scan the entire partition for 
customer_id = 2
. Add a Sort Order to your DDL to ensure that data within each partition is clustered. This allows the Parquet reader to use min/max skipping for your 
lob_id
filter and 
order_hour_utc
range. Suggested changes to `TBLPROPERTIES`:
Copy code
sql
ALTER TABLE adhoc.hourly_sales_poc SET TBLPROPERTIES (
  -- Cluster data to make 'lob_id' and 'order_hour_utc' filters extremely fast
  'write.sort.order' = 'lob_id ASC, order_hour_utc ASC',
  'write.distribution-mode' = 'range',
  -- Keep your existing metrics and bloom filters
  'write.metadata.metrics.column.lob_id' = 'full',
  'write.metadata.metrics.column.order_hour_utc' = 'full'
);
Note: After changing sort properties, only new data will be sorted. You may need to rewrite existing data using 
INSERT OVERWRITE
to see the benefit. 2. StarRocks Catalog Properties (Critical for CN) Since you are using CN nodes, the bottleneck for a 50ms query is often the FE metadata fetch and CN plan distribution. Ensure your StarRocks catalog is configured to cache Iceberg metadata aggressively. Update your Catalog definition:
Copy code
sql
CREATE EXTERNAL CATALOG iceberg_catalog
PROPERTIES (
    "type" = "iceberg",
    "enable_iceberg_metadata_cache" = "true", -- Cache manifest files in FE memory
    "iceberg_table_cache_refresh_interval_sec" = "300", -- Increase if data doesn't change every minute
    "enable_background_refresh_connector_metadata" = "true" -- Keeps the cache "hot"
);
3. The "50ms" Solution: Asynchronous Materialized View Even with perfect Iceberg properties, a direct query on Iceberg often takes 200ms+ due to the overhead of the Iceberg library and Parquet footers. To hit your 50-100ms goal, you should wrap this specific query in an Asynchronous Materialized View. This transforms the Iceberg data into StarRocks' native storage format on your CN nodes, which supports the true Query Result Cache.
Copy code
sql
CREATE MATERIALIZED VIEW hourly_sales_agg_mv
DISTRIBUTED BY HASH(market_product_id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR) -- Adjust based on your SLA
AS
SELECT
    hs.customer_id,
    hs.lob_id,
    hs.market_product_id,
    hs.order_hour_utc,
    -- ... include all your SUM and column logic here ...
FROM adhoc.hourly_sales_poc hs;
4. Query Execution Settings Run these settings in your session to ensure the CN nodes are optimized for this query: * 
SET enable_scan_block_cache = true;
— Ensures CN uses local disk cache. * 
SET plan_mode = 'distributed';
— Allows CNs to assist the FE in metadata processing if the Iceberg table is large. * 
SET enable_query_cache = true;
— (Only works if you use the Materialized View approach mentioned above). Summary for your specific Query: 1. Partition Pruning: You are already filtering by 
customer_id = 2
(Partition Key). This is the best optimization possible. 2. Min/Max Pruning: By adding 
write.sort.order = 'lob_id, order_hour_utc'
, the query will skip files/row-groups where 
lob_id
is not 2. 3. Data Cache: On the first run, it will be slow. On the second run, the CN should read from its local SSD. Ensure your CN 
storage_root_path
is on an NVMe/SSD.
g
@Rocky goal is to achieve 100 ms without caching or async view. So basically we want cold query ( directly on top of iceberg means directly from s3) getting data and doing calculation in less than 100 ms.
r
Achieving sub-100ms latency for a "cold" query (reading directly from S3 via the Iceberg catalog without an Async MV or Result Cache) is extremely challenging due to the physical latency of S3 (often 100ms+ per request). However, you can reach this target if you eliminate the Planning overhead and maximize I/O Parallelism for the scan. Here are the specific configurations and strategies to hit 100ms on direct Iceberg queries: 1. Zero-Latency Metadata Planning (FE) The most common reason Iceberg queries exceed 100ms is the FE fetching manifest files from S3 to figure out which Parquet files to read. You must ensure the FE has these in memory before the query starts. Set these in your FE 
fe.conf
and restart:
Copy code
properties
# Enable disk cache for Iceberg metadata to avoid S3 calls during planning
enable_iceberg_metadata_disk_cache = true
# Increase memory for metadata objects
iceberg_metadata_memory_cache_capacity = 2147483648 # 2GB
# Ensure StarRocks proactively polls for metadata changes
enable_background_refresh_connector_metadata = true
background_refresh_metadata_interval_millis = 60000 # 1 minute
Why: This ensures the "Execution Plan" is generated in <10ms by using local metadata. 2. Maximize CN Concurrency (Session Variables) To fetch data from S3 in <100ms, you need to open many parallel connections to S3 simultaneously. Run these in your session before the query:
Copy code
sql
-- Use more threads to process S3 data
SET parallel_fragment_exec_instance_num = 16; -- Adjust based on CN core count

-- Increase the number of concurrent I/O tasks for S3
SET connector_io_tasks_per_scan_context = 16;

-- Ensure the planning stays local to the FE for low latency
SET plan_mode = 'local';

-- Enable the late materialization to skip reading columns that don't match filters
SET enable_late_materialization = true;
3. Physical Data Layout (Table Level) Since you are filtering on 
customer_id
(Partition Key), 
lob_id
, and 
order_hour_utc
, you need to minimize the number of Parquet files the CN has to open. 1. Small File Compaction: If the partition 
customer_id=2
has hundreds of small files, StarRocks will spend all its time on S3 GET overhead. Ensure you have run an Iceberg rewrite/compaction job to produce fewer, larger files (e.g., 256MB+). 2. Sorting: You must ensure data is sorted by 
lob_id
and 
order_hour_utc
inside the files. This allows the CN to skip over the majority of the Parquet row groups without downloading them. _ _Check:* Run 
EXPLAIN ANALYZE <QUERY>
and look at 
PushDownPredicates
. Ensure 
lob_id
is being pushed down to the Parquet reader. 4. Optimize the Query Structure Your current query uses 
SUM(IF(...))
. While StarRocks vectorizes this well, for a cold query, you want to ensure the engine isn't doing unnecessary work. * Predicate Pushdown: Your 
WHERE
clause is already excellent. * Column Pruning: Your DDL has ~60 columns, but your query only uses ~10. StarRocks is a columnar engine, so it will only fetch the required 10. *Do not use `SELECT ` in any subqueries. Summary Checklist for <100ms: 1. Is Metadata cached? (Check 
fe.log
for planning time. If planning is > 20ms, tune FE metadata cache). 2. Is the partition too big? If 
customer_id=2
has 10GB of data, S3 cannot transfer it to a single CN in 100ms. You need more CNs or more aggressive filtering. 3. Are Bloom Filters working? Since you have Bloom filters on 
lob_id
, ensure the query is actually hitting them (check query profile for 
BloomFilterFilterRows
). Conclusion: If you strictly disable the Data Cache (block cache), you are at the mercy of S3's 100ms-200ms TTFB (Time to First Byte). To consistently hit <100ms, you must allow the CN nodes to use their local 
storage_root_path
(Data Cache), even if you don't use Materialized Views. References
g
@Rocky analyze this query and suggest iceberg table configuration changes or query optimizations. DDL - 1)
Copy code
CREATE TABLE adhoc.hourly_sales_poc (
order_hour_utc TIMESTAMP,
order_date_utc DATE,
listing_id STRING,
asin STRING,
market_product_id BIGINT,
master_product_id BIGINT,
customer_id BIGINT,
vendor_id BIGINT,
merchant_id STRING,
customer_type INTEGER,
customer_state_id INTEGER,
marketplace_id INTEGER,
quantity BIGINT,
order_items BIGINT,
converted_revenue DECIMAL(18,6),
converted_revenue_tax_adjustment DECIMAL(18,6),
converted_revenue_tax_adjusted DECIMAL(18,6),
local_currency_symbol STRING,
local_currency_code STRING,
local_revenue DECIMAL(18,6),
local_revenue_tax_adjustment DECIMAL(18,6),
local_revenue_tax_adjusted DECIMAL(18,6),
converted_revenue_aed DECIMAL(18,6),
converted_revenue_tax_adjustment_aed DECIMAL(18,6),
converted_revenue_tax_adjusted_aed DECIMAL(18,6),
converted_revenue_ars DECIMAL(18,6),
converted_revenue_tax_adjustment_ars DECIMAL(18,6),
converted_revenue_tax_adjusted_ars DECIMAL(18,6),
converted_revenue_aud DECIMAL(18,6),
converted_revenue_tax_adjustment_aud DECIMAL(18,6),
converted_revenue_tax_adjusted_aud DECIMAL(18,6),
converted_revenue_cad DECIMAL(18,6),
converted_revenue_tax_adjustment_cad DECIMAL(18,6),
converted_revenue_tax_adjusted_cad DECIMAL(18,6),
converted_revenue_cny DECIMAL(18,6),
converted_revenue_tax_adjustment_cny DECIMAL(18,6),
converted_revenue_tax_adjusted_cny DECIMAL(18,6),
converted_revenue_eur DECIMAL(18,6),
converted_revenue_tax_adjustment_eur DECIMAL(18,6),
converted_revenue_tax_adjusted_eur DECIMAL(18,6),
converted_revenue_gbp DECIMAL(18,6),
converted_revenue_tax_adjustment_gbp DECIMAL(18,6),
converted_revenue_tax_adjusted_gbp DECIMAL(18,6),
converted_revenue_jpy DECIMAL(18,6),
converted_revenue_tax_adjustment_jpy DECIMAL(18,6),
converted_revenue_tax_adjusted_jpy DECIMAL(18,6),
converted_revenue_usd DECIMAL(18,6),
converted_revenue_tax_adjustment_usd DECIMAL(18,6),
converted_revenue_tax_adjusted_usd DECIMAL(18,6),
sold_by_threepn BOOLEAN,
order_count BIGINT,
buyer_count BIGINT,
sns_order_count BIGINT,
source STRING,
lob_id INT,
converted_revenue_krw DECIMAL(18,6),
converted_revenue_tax_adjustment_krw DECIMAL(18,6),
converted_revenue_tax_adjusted_krw DECIMAL(18,6),
converted_revenue_pln DECIMAL(18,6),
converted_revenue_tax_adjustment_pln DECIMAL(18,6),
converted_revenue_tax_adjusted_pln DECIMAL(18,6),
converted_revenue_sek DECIMAL(18,6),
converted_revenue_tax_adjustment_sek DECIMAL(18,6),
converted_revenue_tax_adjusted_sek DECIMAL(18,6)
) USING ICEBERG
PARTITIONED BY (customer_id)
TBLPROPERTIES (
  'write.format.default' = 'parquet',
  'write.parquet.compression-codec' = 'zstd',
  'write.target-file-size-bytes' = '268435456',
  'write.parquet.row-group-size-bytes' = '134217728',
  'write.distribution-mode' = 'range',
  'write.metadata.metrics.default' = 'truncate(16)',
  'write.metadata.metrics.column.customer_id' = 'full',
  'write.metadata.metrics.column.marketplace_id' = 'full',
  'write.metadata.metrics.column.market_product_id' = 'full',
  'write.metadata.metrics.column.lob_id' = 'full',
  'write.metadata.metrics.column.order_hour_utc' = 'full',
  'write.metadata.metrics.column.order_date_utc' = 'full',
  'write.parquet.bloom-filter-enabled.column.customer_id' = 'true',
  'write.parquet.bloom-filter-enabled.column.marketplace_id' = 'true',
  'write.parquet.bloom-filter-enabled.column.market_product_id' = 'true',
  'write.parquet.bloom-filter-enabled.column.lob_id' = 'true'
);
ALTER TABLE adhoc.hourly_sales_poc WRITE ORDERED BY customer_id ASC, order_hour_utc ASC;
2)
Copy code
CREATE TABLE adhoc.fact_insights_market_product_daily_poc (
date STRING,
market_product_id BIGINT,
master_product_id BIGINT,
marketplace_id INTEGER,
customer_id BIGINT,
vendor_id BIGINT,
customer_type INTEGER,
customer_state_id INTEGER,
estimated_total_page_views DECIMAL(38,9),
estimated_total_page_views_non_china DECIMAL(38,9),
estimated_total_sold_units DECIMAL(38,9),
estimated_page_views_lost_to_suppression DECIMAL(18,9),
estimated_lost_suppression_sales DECIMAL(18,9),
estimated_lost_suppression_sales_aed DOUBLE,
estimated_lost_suppression_sales_ars DOUBLE,
estimated_lost_suppression_sales_aud DOUBLE,
estimated_lost_suppression_sales_cad DOUBLE,
estimated_lost_suppression_sales_cny DOUBLE,
estimated_lost_suppression_sales_eur DOUBLE,
estimated_lost_suppression_sales_gbp DOUBLE,
estimated_lost_suppression_sales_jpy DOUBLE,
estimated_lost_suppression_sales_usd DOUBLE,
avg_rating DECIMAL(3,2),
reviews_count BIGINT,
estimated_lost_suppression_sales_krw DOUBLE,
estimated_lost_suppression_sales_pln DOUBLE,
estimated_lost_suppression_sales_sek DOUBLE,
estimated_total_sales DECIMAL(38,9),
estimated_total_sales_aed DOUBLE,
estimated_total_sales_ars DOUBLE,
estimated_total_sales_aud DOUBLE,
estimated_total_sales_cad DOUBLE,
estimated_total_sales_cny DOUBLE,
estimated_total_sales_eur DOUBLE,
estimated_total_sales_gbp DOUBLE,
estimated_total_sales_jpy DOUBLE,
estimated_total_sales_usd DOUBLE,
estimated_total_sales_krw DOUBLE,
estimated_total_sales_pln DOUBLE,
estimated_total_sales_sek DOUBLE
) USING ICEBERG
PARTITIONED BY (customer_id)
TBLPROPERTIES (
  'write.format.default' = 'parquet',
  'write.parquet.compression-codec' = 'zstd',
  'write.target-file-size-bytes' = '16777216',
  'write.parquet.row-group-size-bytes' = '16777216',
  'write.distribution-mode' = 'range',
  'write.metadata.metrics.default' = 'truncate(16)',
  'write.metadata.metrics.column.customer_id' = 'full',
  'write.metadata.metrics.column.marketplace_id' = 'full',
  'write.metadata.metrics.column.market_product_id' = 'full',
  'write.metadata.metrics.column.date' = 'full',
  'write.parquet.bloom-filter-enabled.column.customer_id' = 'true',
  'write.parquet.bloom-filter-enabled.column.marketplace_id' = 'true',
  'write.parquet.bloom-filter-enabled.column.market_product_id' = 'true',
  'commit.manifest-merge.enabled' = 'true',
  'write.metadata.delete-after-commit.enabled' = 'true'
);
ALTER TABLE adhoc.fact_insights_market_product_daily_poc WRITE ORDERED BY customer_id ASC, date ASC;
3)
Copy code
CREATE TABLE adhoc.fact_insights_market_product_seller_daily_poc (
date STRING,
market_product_id BIGINT,
catalog_seller_id BIGINT,
source STRING,
lob_id INT,
master_product_id BIGINT,
marketplace_id INTEGER,
customer_id BIGINT,
vendor_id BIGINT,
customer_type INTEGER,
customer_state_id INTEGER,
estimated_pattern_page_views DECIMAL(38,9),
estimated_pattern_page_views_non_china DECIMAL(38,9),
estimated_unit_share_pattern_sold_units DECIMAL(38,9),
estimated_lost_buybox_sales DECIMAL(18,9),
estimated_buybox_sales DECIMAL(18,9),
avg_buybox_price_by_spv DECIMAL(38,9),
est_in_stock_opportunity DOUBLE,
est_total_opportunity DOUBLE,
ad_sales DOUBLE,
ad_spend DOUBLE,
unique_visitors_count DECIMAL(38,6),
buyers_count BIGINT,
add_to_cart_count DECIMAL(38,6),
add_to_fav DECIMAL(38,6),
bounce_rate DECIMAL(38,6),
clicks DECIMAL(38,6),
ctr DECIMAL(38,6),
cpc DECIMAL(38,6),
refund_amount DOUBLE,
refund_order_count INT,
estimated_lost_buybox_sales_aed DOUBLE,
estimated_lost_buybox_sales_ars DOUBLE,
estimated_lost_buybox_sales_aud DOUBLE,
estimated_lost_buybox_sales_cad DOUBLE,
estimated_lost_buybox_sales_cny DOUBLE,
estimated_lost_buybox_sales_eur DOUBLE,
estimated_lost_buybox_sales_gbp DOUBLE,
estimated_lost_buybox_sales_jpy DOUBLE,
estimated_lost_buybox_sales_usd DOUBLE,
estimated_buybox_sales_aed DOUBLE,
estimated_buybox_sales_ars DOUBLE,
estimated_buybox_sales_aud DOUBLE,
estimated_buybox_sales_cad DOUBLE,
estimated_buybox_sales_cny DOUBLE,
estimated_buybox_sales_eur DOUBLE,
estimated_buybox_sales_gbp DOUBLE,
estimated_buybox_sales_jpy DOUBLE,
estimated_buybox_sales_usd DOUBLE,
est_in_stock_opportunity_aed DOUBLE,
est_in_stock_opportunity_ars DOUBLE,
est_in_stock_opportunity_aud DOUBLE,
est_in_stock_opportunity_cad DOUBLE,
est_in_stock_opportunity_cny DOUBLE,
est_in_stock_opportunity_eur DOUBLE,
est_in_stock_opportunity_gbp DOUBLE,
est_in_stock_opportunity_jpy DOUBLE,
est_in_stock_opportunity_usd DOUBLE,
est_total_opportunity_aed DOUBLE,
est_total_opportunity_ars DOUBLE,
est_total_opportunity_aud DOUBLE,
est_total_opportunity_cad DOUBLE,
est_total_opportunity_cny DOUBLE,
est_total_opportunity_eur DOUBLE,
est_total_opportunity_gbp DOUBLE,
est_total_opportunity_jpy DOUBLE,
est_total_opportunity_usd DOUBLE,
ad_sales_aed DOUBLE,
ad_sales_ars DOUBLE,
ad_sales_aud DOUBLE,
ad_sales_cad DOUBLE,
ad_sales_cny DOUBLE,
ad_sales_eur DOUBLE,
ad_sales_gbp DOUBLE,
ad_sales_jpy DOUBLE,
ad_sales_usd DOUBLE,
ad_spend_aed DOUBLE,
ad_spend_ars DOUBLE,
ad_spend_aud DOUBLE,
ad_spend_cad DOUBLE,
ad_spend_cny DOUBLE,
ad_spend_eur DOUBLE,
ad_spend_gbp DOUBLE,
ad_spend_jpy DOUBLE,
ad_spend_usd DOUBLE,
refund_amount_aed DOUBLE,
refund_amount_ars DOUBLE,
refund_amount_aud DOUBLE,
refund_amount_cad DOUBLE,
refund_amount_cny DOUBLE,
refund_amount_eur DOUBLE,
refund_amount_gbp DOUBLE,
refund_amount_jpy DOUBLE,
refund_amount_usd DOUBLE,
cpc_aed Decimal(38, 6),
cpc_ars Decimal(38, 6),
cpc_aud Decimal(38, 6),
cpc_cad Decimal(38, 6),
cpc_cny Decimal(38, 6),
cpc_eur Decimal(38, 6),
cpc_gbp Decimal(38, 6),
cpc_jpy Decimal(38, 6),
cpc_usd Decimal(38, 6),
cpc_krw Decimal(38, 6),
cpc_pln Decimal(38, 6),
cpc_sek Decimal(38, 6),
estimated_lost_buybox_sales_krw DOUBLE,
estimated_lost_buybox_sales_pln DOUBLE,
estimated_lost_buybox_sales_sek DOUBLE,
estimated_buybox_sales_krw DOUBLE,
estimated_buybox_sales_pln DOUBLE,
estimated_buybox_sales_sek DOUBLE,
est_in_stock_opportunity_krw DOUBLE,
est_in_stock_opportunity_pln DOUBLE,
est_in_stock_opportunity_sek DOUBLE,
est_total_opportunity_krw DOUBLE,
est_total_opportunity_pln DOUBLE,
est_total_opportunity_sek DOUBLE,
ad_sales_krw DOUBLE,
ad_sales_pln DOUBLE,
ad_sales_sek DOUBLE,
ad_spend_krw DOUBLE,
ad_spend_pln DOUBLE,
ad_spend_sek DOUBLE,
refund_amount_krw DOUBLE,
refund_amount_pln DOUBLE,
refund_amount_sek DOUBLE
) USING ICEBERG
PARTITIONED BY (customer_id)
TBLPROPERTIES (
  'write.format.default' = 'parquet',
  'write.parquet.compression-codec' = 'zstd',
  'write.target-file-size-bytes' = '16777216',
  'write.parquet.row-group-size-bytes' = '16777216',
  'write.distribution-mode' = 'range',
  'write.metadata.metrics.default' = 'truncate(16)',
  'write.metadata.metrics.column.customer_id' = 'full',
  'write.metadata.metrics.column.marketplace_id' = 'full',
  'write.metadata.metrics.column.market_product_id' = 'full',
  'write.metadata.metrics.column.lob_id' = 'full',
  'write.metadata.metrics.column.date' = 'full',
  'write.parquet.bloom-filter-enabled.column.customer_id' = 'true',
  'write.parquet.bloom-filter-enabled.column.marketplace_id' = 'true',
  'write.parquet.bloom-filter-enabled.column.lob_id' = 'true',
  'write.parquet.bloom-filter-enabled.column.market_product_id' = 'true'
);


ALTER TABLE adhoc.fact_insights_market_product_seller_daily_poc WRITE ORDERED BY customer_id ASC, date ASC
single_brand_product_done.sql
r
To achieve a <100ms cold query directly from S3, you must minimize the number of S3 calls and maximize the efficiency of each data fetch. Currently, your table configurations have a few "bottlenecks" that will prevent you from hitting that 100ms target. 1. Critical Table Property Changes A. Fix the "Small File" Problem (Tables 2 & 3) Tables 2 and 3 have 
'write.target-file-size-bytes' = '16777216'
(16MB). * The Issue: S3 has a per-request latency of ~20–50ms. If your query needs to scan 10 files of 16MB each, the metadata and connection overhead alone will exceed 100ms. * The Fix: Increase this to 128MB or 256MB. Fewer, larger files allow StarRocks to use large, streaming S3 GET requests which are much faster. * Update: 
'write.target-file-size-bytes' = '134217728'
B. Improve the Sort Order (Table 1) You currently have 
WRITE ORDERED BY customer_id ASC, order_hour_utc ASC
. * The Issue: Since the table is partitioned by 
customer_id
, all data in a single folder already has the same 
customer_id
. Sorting by it within the file is redundant. Your query filters on 
lob_id
and 
order_hour_utc
. * The Fix: Move 
lob_id
to the front of the sort order. This allows Parquet "Page Index" skipping. * Update: 
ALTER TABLE adhoc.hourly_sales_poc WRITE ORDERED BY lob_id ASC, order_hour_utc ASC;
C. Change 
date
from STRING to DATE (Tables 2 & 3) * The Issue: Using 
STRING
for dates is significantly slower for range pruning and requires more CPU to parse during the query. * The Fix: Change the column type to 
DATE
. StarRocks can prune 
DATE
types much more efficiently at the Iceberg manifest level. D. Optimize Bloom Filters and Metrics * Partition Keys: Remove Bloom filters on 
customer_id
. Since it's the partition key, StarRocks prunes the entire directory; a Bloom filter inside the file adds overhead without any benefit. * Metrics: For 
lob_id
and 
marketplace_id
(INTs), use 
full
instead of 
truncate
. Truncation is for long strings. * Update: 
'write.metadata.metrics.column.lob_id' = 'full'
────────── 2. Query Level Optimizations In your query, you use many 
SUM(IF(...))
blocks. While StarRocks is fast at this, you can optimize the cold scan by ensuring Late Materialization:
Copy code
sql
-- Ensure StarRocks only reads the 'converted_revenue' bytes for rows 
-- that actually pass the 'order_hour_utc' filter.
SET enable_late_materialization = true;
If you find yourself joining these tables, ensure you use Colocated Joins by ensuring all three tables are partitioned by the same key (
customer_id
)—which you have already done. ────────── 3. StarRocks Engine Tuning (The "Cold" Performance Secret) To hit 100ms without a cache, the FE must generate the execution plan in <10ms. If the FE has to talk to S3 during the query to read Iceberg manifests, you will never hit 100ms. Apply these to your StarRocks Cluster: 1. FE Metadata Background Refresh: Force StarRocks to keep the Iceberg manifest files "hot" in FE memory so it doesn't fetch them during your 100ms window.
Copy code
sql
    -- Run this for each catalog
    ALTER CATALOG iceberg_catalog SET ("iceberg_table_cache_refresh_interval_sec" = "60");
2. Increase I/O Concurrency: Since you are on CN nodes, you need to pull data from S3 using more parallel "pipelines."
Copy code
sql
    SET parallel_fragment_exec_instance_num = 16; -- Use more cores
    SET connector_io_tasks_per_scan_context = 16; -- More concurrent S3 threads
3. Ensure Planning is Local:
Copy code
sql
    SET plan_mode = 'local';
Recommended DDL Structure (Optimized for <100ms) ```sql CREATE TABLE adhoc.hourly_sales_poc ( ... columns ... ) USING ICEBERG PARTITIONED BY (customer_id) TBLPROPERTIES ( 'write.format.default' = 'parquet', 'write.target-file-size-bytes' = '268435456', -- Larger files 'write.metadata.metrics.column.lob_id' = 'full',
'write.metadata.metrics.column.order_hour_utc' = 'full', 'write.parquet.bloom-filter-enabled.column.lob_id' = 'true', 'write.parquet.bloom-filter-enabled.column.market_product_id' = 'true' ); -- SORT BY the filter columns, NOT the partition column ALTER TABLE adhoc.hourly_sales_poc WRITE ORDERED BY lob_id ASC, order_hour_utc ASC; ``` Summary of the Path to 100ms: 1. Metadata: Use 
background_refresh
so planning time is <10ms. 2. Pruning: Use 
WRITE ORDERED BY lob_id
so the CN only downloads tiny "ranges" of the Parquet file from S3 (using HTTP Range Requests). 3. Parallelism: Use 
connector_io_tasks_per_scan_context
to fetch those ranges in parallel.
g
@Rocky 1. Page Index skipping: For Iceberg Parquet files with`WRITE ORDERED BY customer_id`, does StarRocks 3.3 use Parquetpage-level statistics(Page Index) to skip individual pages within a row group, or does it decode the entire row group once the row group passes min/max pruning? What's the minimum row group size to make this effective? 2. Redundant sort key: Our fact tables have`PARTITIONED BY (customer_id)`AND`WRITE ORDERED BY customer_id ASC, date ASC`. Since all rows in a partition already share the same customer_id, is sorting by customer_id within the partition redundant? Should we change to`WRITE ORDERED BY date ASC`only? 3. OS page cache vs S3: When`IOTaskWaitTime ≈ 70µs`(data is in Linux OS page cache, not S3), does increasing`connector_io_tasks_per_scan_operator`still help? Or does it only benefit true S3 reads? 4. Optimal file count per partition: We have 3 BEs. For a customer partition with ~60MB of compressed data, what target file size gives optimal parallelism — 3 files (20MB each), or more? 5. True cold benchmark: Is there a StarRocks command to flush/evict the OS page cache on BE nodes so we can measure true S3 cold latency accurately?
r
Here are the technical answers to your benchmarking and optimization questions for StarRocks 3.3: 1. Parquet Page Index Skipping StarRocks 3.3 does support Parquet Page Index skipping (controlled by the BE config 
parquet_page_index_enable
, which is 
true
by default). * How it works: If a row group survives min/max pruning, StarRocks uses the Page Index to skip individual pages (typically 1MB or 64KB units) within that row group. It does not need to decode the entire row group if the Page Index indicates the target data isn't there. * Optimal Size: Page Index is most effective when row groups are large (e.g., 128MB to 256MB). If your row groups are too small (e.g., 16MB), the overhead of the Page Index can outweigh the skipping benefits, and you're better off just pruning at the row-group level. 2. Redundant Sort Keys Yes, sorting by the partition key within that partition is redundant for pruning. * Pruning: Since every row in the partition 
customer_id=2
has the same value, the min/max values for that column in every segment/page will be 
[2, 2]
. This provides zero additional pruning power beyond the initial partition pruning. * Prefix Index Waste: StarRocks builds a "Short Key Index" using the first 36 bytes of your sort key. If 
customer_id
is the first key and it's constant, you are wasting those 36 bytes on a value that doesn't help distinguish data. * Recommendation: Change to 
WRITE ORDERED BY date ASC
only. This allows the index to focus entirely on the time-dimension, which is your primary filter. 3. OS Page Cache vs. S3 Concurrency If your 
IOTaskWaitTime
is ~70µs, your data is already in the OS Page Cache (RAM). * Does increasing tasks help? No. 
connector_io_tasks_per_scan_operator
is designed to hide IO latency (S3's 50ms+ TTFB) by having multiple requests in flight. * Bottleneck: When data is in RAM, your bottleneck shifts to CPU decoding (Parquet decompression and materialization). Increasing IO tasks beyond your available CPU cores will likely decrease performance due to thread context-switching and cache contention. 4. Optimal File Count for 3 BEs For a partition with ~60MB of data and 3 BEs: * Better Parallelism: Use 3 files of ~20MB. * Why: In StarRocks, the "Morsel" (unit of work) for external scans is typically a file or a split of a file. While StarRocks can split a single large Parquet file (by row group), a 60MB file usually contains only one or two row groups. By having 3 separate files, you guarantee that each BE can pick up at least one file and work in parallel. _ _Note:* In a high-throughput system, 20MB is considered a "small file." But for your 100ms latency goal, 3 files are better than 1 because they distribute the CPU-heavy decoding work across all 3 nodes. 5. True Cold Benchmark (Flushing Cache) StarRocks does not have a single SQL command to flush the Linux OS page cache, but you can achieve a "True Cold" state using these methods: A. To flush the OS Page Cache (Root access on BEs required): Run this on all BE/CN nodes:
Copy code
bash
sync; echo 3 > /proc/sys/vm/drop_caches
B. To flush the StarRocks Data Cache (Shared-Data/Cloud-Native): If you are using StarRocks' internal "Data Cache" (Block Cache), you can clear it without a restart by temporarily setting the disk quota to 0:
Copy code
sql
-- Identify your BE IDs using SHOW BACKENDS
UPDATE be_configs SET VALUE="0" WHERE NAME="datacache_disk_size";
-- Wait 10 seconds for eviction, then set it back to your original size (e.g., 100G)
UPDATE be_configs SET VALUE="100G" WHERE NAME="datacache_disk_size";
C. To bypass the Query Result Cache: Ensure your query doesn't hit the FE-level result cache:
Copy code
sql
SET enable_query_cache = false;
Summary for your 100ms Goal: Use 3 files per partition, sort only by 
date
, and ensure FE Background Refresh is on so that your "Cold" query only pays the S3 data price, not the S3 metadata price. References
g
1. @Rocky Runtime filter + Page Index interaction:The dim scan (id=42) now outputs 703 rows instead of the expected 10,431 rows for customer_id=2. This suggests a runtime filter on`market_product_id`is being pushed to the dim scan. When a runtime filter is active alongside Page Index, does StarRocks still use Page Index to skip pages, or does it fall back to full row group decoding? 2. Partitioning unpartitioned dim table:Our`market_products_details_poc`has no partition key. With`WRITE ORDERED BY customer_id`, all customer_id=2 rows land in 1 file → 1 BE. If we add`PARTITIONED BY (customer_id)`with`write.target-file-size-bytes = 20971520`(20MB, matching the "3 files per partition" recommendation), would this reliably spread the ~60MB of customer_id=2 data across 3 BEs? Any downside to partitioning a dim table by customer_id? 3. FSIOTime 555ms for ~20MB:With 128MB row group and Page Index skipping 99.35% of pages, our heavy BE still shows FSIOTime=555ms. The data is in OS page cache (IOTaskWaitTime=67µs). Is there a BE-side setting to increase decompression parallelism (e.g.
parquet_reader_max_group_worker_count
) to speed up the ~20MB read from page cache?
r
To hit your sub-100ms target while scaling to multiple BEs, here is the analysis of your profile findings and the recommended adjustments: 1. Runtime Filter + Page Index Interaction Yes, StarRocks 3.3 maintains Page Index skipping even when Runtime Filters are active. * The Mechanism: StarRocks converts Runtime Filters (e.g., from a Join) into standard predicates at the Scan operator. These predicates are pushed down into the Parquet reader. If a column has a Page Index, the reader uses these pushed-down predicates to calculate matching row ranges and skip pages. * Proof in your Profile: The fact that your row count dropped from 10,431 to 703 before the final output proves the filter is working at the storage level. If it were a fallback, you would see "RawRowsRead" as 10,431 and then a filter would drop it to 703 later. The Page Index is successfully preventing the decompression of those extra 9,000+ rows. 2. Partitioning the Dimension Table Reliability: Yes, partitioning by 
customer_id
with a 
20MB
target file size is the most reliable way to force StarRocks to spread that ~60MB of data across 3 BEs. * How it works: StarRocks treats each Iceberg file as a "Split" (morsel). If you have 1 file of 60MB, only 1 BE can work on it at a time. If you have 3 files of 20MB, the FE will generate 3 splits, allowing 3 BEs to process them in parallel. * The Downsides: * Metadata Pressure (FE): If you have millions of 
customer_id
values, you will end up with millions of directories and manifest entries. This will bloat the Iceberg metadata and could push your FE planning time from <10ms to >200ms, defeating your 100ms goal. * Recommendation: Only use this strategy if the number of unique 
customer_id
values is manageable (e.g., <10,000) or if you use Iceberg's Partition Evolution to only partition recent/hot data. 3. FSIOTime 555ms & Parallel Decoding 
FSIOTime
in the StarRocks profile for Iceberg/Parquet includes the time spent decompressing and decoding the Parquet data into the StarRocks in-memory format. If 
IOTaskWaitTime
is low, your 555ms is almost entirely CPU-bound decoding. The "Secret" BE Configuration: StarRocks has a BE setting specifically to parallelize the decoding of columns within a Parquet row group: * 
parquet_reader_max_group_worker_count
: This controls the number of threads used to decode a single Parquet row group. * Suggested Change: Set this to 
4
or 
8
in your 
be.conf
(or via 
ADMIN SET FRONTEND CONFIG
if applicable, though it is usually a BE flag). * Why it helps: By default, a single scan thread might decode columns serially. Increasing this allow the BE to use multiple cores to decode the ~20MB of data simultaneously. 4. Optimal Query Session Variables To ensure the 3 files are actually processed in parallel across your 3 BEs, verify these session variables:
Copy code
sql
-- Ensure we have enough "instances" to handle 1 split per BE
SET parallel_fragment_exec_instance_num = 16; 

-- Enable the pipeline engine to handle these splits asynchronously
SET enable_pipeline_engine = true;

-- For cold queries, ensure we don't wait for a slow S3 connection
SET connector_io_tasks_per_scan_context = 16;
Summary of Recommendations: 1. Partition the Dim table by 
customer_id
with a 20MB target file size to get 3 files for your 3 BEs. 2. Verify FE Planning Time: Run 
EXPLAIN ANALYZE
and look at the 
Query Plan
time. If it's > 20ms, you need to tune 
iceberg_metadata_memory_cache_capacity
. 3. Tune BE Decoding: If 
FSIOTime
remains high despite data being in the OS cache, increase the BE parameter 
parquet_reader_max_group_worker_count
. References • sql-reference/System_variable.md • administration/management/BE_configuration.md
g
@Rocky After partitioning 
market_products_details_poc
by 
customer_id
with 
WRITE ORDERED BY market_product_id ASC
and 20MB files: the final query joins 
dim
on 
market_product_id
with a runtime filter of ~2620 IDs. With market_product_id sorted and Page Index active, StarRocks should skip pages outside the runtime filter's ID range. But the ~2620 IDs may not be contiguous (they're real product IDs, likely non-sequential integers). Does Page Index skipping still work efficiently for a non-contiguous IN list of ~2620 values, or does it fall back to full page reads? If it falls back, would it be better to sort dim by a contiguous key (e.g., no sort / natural order) and rely purely on parallelism from the 3-file split?
r
Page Index skipping remains highly efficient for your non-contiguous ID list, and you should not switch to natural order. Here is the technical breakdown of why your current configuration is optimal for a <100ms target: 1. Does Page Index work for non-sequential IDs? Yes. Even if your 2,620 
market_product_id
values are non-sequential (e.g., 105, 2001, 55000), they are contiguous in the sort order. * The Clustering Benefit: Because you used 
WRITE ORDERED BY market_product_id
, Parquet Page 1 might contain IDs 1–1000, Page 2 contains 1001–2000, and so on. * The Skipping Logic: If your filter contains ID 
105
and 
55000
, StarRocks will consult the Page Index, see that Page 1 contains the range 
[1, 1000]
and Page 55 contains 
[54000, 56000]
. It will only seek to and decompress those two specific pages. * The Alternative (Natural Order): If you don't sort, those 2,620 IDs will be scattered randomly across every single page in the 20MB file. You would be forced to decode 100% of the row group, significantly increasing your 
FSIOTime
. 2. The "In-List" Threshold StarRocks 3.3 converts runtime filters into ranges for the Parquet reader. However, if the number of values in your 
IN
list is very large, there is a small risk the engine might treat it as a broad range (min of list to max of list) rather than individual points. * Optimization: Ensure your runtime filter is being passed as an 
IN
list rather than just a Bloom Filter. * Session Variable: 
SET runtime_filter_type = 'IN_OR_BLOOM';
* This encourages the FE to pass the actual ID list down to the scan node if it’s small enough, which is much more effective for Page Index skipping than a Bloom Filter alone. 3. Why Parallelism (3-file split) alone isn't enough For a <100ms cold query, pruning is always cheaper than parallel decoding. * A 20MB Parquet file typically takes ~50–150ms to fully decompress and decode on a single core. _ Even if you split it across 3 BEs (20MB each), if you have to decode the _entire* file (natural order), you are still looking at ~100ms just for the CPU decoding work, leaving zero room for S3 network latency or FE planning. * By keeping the Sort + Page Index, you reduce the data actually decoded to ~1–2MB per BE, which takes <10ms. 4. How to Verify in the Profile To confirm the Page Index is winning against your 2,620 non-contiguous IDs, look for these metrics in the 
SCAN
node of your query profile: * 
PushDownPredicates
: Should show your 
market_product_id IN (...)
. * 
RowsProcessed
vs. 
RowsRead
: 
RowsProcessed
will be your final ~700 rows, but 
RowsRead
(or 
RawRowsRead
) should be significantly lower than the total row count of the file. If 
RowsRead
is nearly equal to the total rows, the skipping is failing. * 
DictFilterRows
: If you use dictionary encoding on that column, this will also show skipping. Final Recommendation: Keep the 
WRITE ORDERED BY market_product_id
. It is the only way to ensure that a "point lookup" query (even one with 2,000+ points) behaves like an index seek rather than a full table scan. table scan. One final tweak: Since your IDs are 
BIGINT
, ensure the Iceberg property 
'write.metadata.metrics.column.market_product_id' = 'full'
is set. This ensures the Iceberg manifest files have the exact min/max for every file, allowing the FE to skip entire files before the BE even starts the Page Index check.
g
@Rocky we have 
connector_io_tasks_per_scan_operator=16
and 
parallel_fragment_exec_instance_num=16
set. ICEBERG_SCAN nodes still show extreme BE skew — 1 BE at max=191ms, another at min=200µs. Root cause: customer_id=2's data fits in exactly 1 Parquet file (< 16MB). The file has 
write.parquet.row-group-size-bytes = write.target-file-size-bytes = 16MB
, meaning 1 row group per file = 1 IO task unit. Does reducing 
write.parquet.row-group-size-bytes
to 2MB (giving 8 row groups per 16MB file) allow the 16 IO tasks to decompress those 8 row groups in parallel? i.e., does 
connector_io_tasks_per_scan_operator
operate at the row-group granularity? If yes, would the scan time for that 1 file drop from ~190ms to ~25ms (8× speedup)? Secondary question: our data is OS-page-cache warm (IOTaskWaitTime=66µs). With 8 row groups in parallel, is the bottleneck then CPU decompression (LZ4)? Is 
parquet_reader_max_group_worker_count
a BE-level or session-level setting, and can it be set via SQL?
r
For your sub-100ms goal, the bottleneck you've identified is a classic "morsel-level skew" problem. Here is the technical breakdown of how to resolve it: 1. Row Group Granularity & Parallelism Yes, StarRocks generates "splits" (morsels) for Parquet files based on row group boundaries. * The Problem: If your 16MB file has only one row group, it is treated as a single, indivisible unit of work. Only one thread can process it, regardless of your 
connector_io_tasks_per_scan_operator
or BE thread pool settings. This explains why one BE is working for 191ms while others are idle. * The Fix: Reducing 
write.parquet.row-group-size-bytes
to 2MB will indeed split that 16MB file into 8 row groups. * IO Task Interaction: 
connector_io_tasks_per_scan_operator
operates exactly at this granularity for external scans. With 8 row groups, StarRocks can issue 8 concurrent "IO tasks" for that single file. * Expected Speedup: Since each 2MB row group is a smaller decoding task and they will run in parallel, your scan time should drop significantly. While an exact 8x speedup (to 25ms) is rare due to scheduling and shared CPU cache overhead, a drop to ~35–50ms is very realistic. 2. CPU Decompression Bottleneck When your data is in the OS page cache (
IOTaskWaitTime ≈ 66µs
), the "IO Task" in StarRocks is essentially a CPU Task. It spends nearly all its time on: 1. Decompressing the LZ4/ZSTD blocks. 2. Decoding the Parquet page encodings (RLE, Dictionary). 3. Materializing the data into the StarRocks vectorized Chunk format. Increasing row group parallelism directly distributes this CPU-intensive work across more cores. 3. Parameter Clarification: 
parquet_reader_max_group_worker_count
 I must clarify: 
parquet_reader_max_group_worker_count
is not a standard public parameter in StarRocks 3.3. I likely introduced some confusion in our previous exchange. In StarRocks, the standard way to control parallel decoding within a scan is via these settings: * 
connector_io_tasks_per_scan_operator
(Session Variable): This is your primary lever. It defines how many concurrent row-group decoding tasks a single scan operator can submit to the BE's thread pool. * 
scanner_thread_pool_thread_num
(BE Config): This is the total size of the thread pool on the BE that handles these tasks. The default is usually 
48
. Ensure this is not saturated (check 
PeakScanTaskQueueSize
in your profile). * 
parallel_fragment_exec_instance_num
(Session Variable): This increases the number of scan operator instances per BE. If you have 8 row groups and 2 instances, each instance will handle 4 row groups. Recommendations for your POC: 1. Rewrite the Data: Set 
write.parquet.row-group-size-bytes = 2097152
(2MB) when writing your Iceberg table. 2. Balance Row Groups: Ensure the number of row groups is at least equal to your BE core count (or your 
connector_io_tasks
setting) to fully saturate the hardware. 3. Check Profile: After the change, look for 
MorselsCount
 in the 
ICEBERG_SCAN
node. It should now show 
8
(or more) instead of 
1
. 4. Verify Bottleneck: If 
FSIOTime
is still high, check if 
parquet_late_materialization_enable
is 
true
(it is by default). This helps if you are only selecting a few columns from a wide table. By moving from one 16MB row group to eight 2MB row groups, you are effectively turning a sequential task into a parallel one, which is the "silver bullet" for hitting sub-100ms latencies on warm data. References • administration/management/FE_configuration.md • administration/management/BE_configuration.md • sql-reference/System_variable.md
g
@Rocky In our EXPLAIN ANALYZE profile, when we reduced 
write.parquet.row-group-size-bytes
from 16MB to 2MB (keeping file size at 16MB), 
OpenFile
time jumped from ~16ms → 133ms per BE, and 
PageIndexTime
from ~19ms → 130ms per BE. These are sequential operations that run before any parallel row-group decoding begins — so the metadata overhead completely swamped the 8x parallelism gain. Two questions: 1. Is`OpenFile`in the StarRocks scan profile measuring Parquetfooter parsing(CPU work, proportional to number of row groups × columns × stats), or actual file-open I/O? Our data is OS-page-cache warm (IOTaskWaitTime=428µs), yet OpenFile costs 133ms — this seems to be CPU/parsing, not I/O. 2. Given that footer parsing is sequential and scales with row group count,what is the optimal row-group-size strategy for a table where customer_id=2 data fits in 1 file (< 16MB)?We need both small-enough row groups for intra-file parallelism AND manageable footer overhead. Is there a "sweet spot" (e.g., 4MB or 8MB) where parallelism outweighs metadata cost, or is there a StarRocks-level config to parallelize the footer parse itself?
r
The behavior you are seeing is a textbook example of Metadata Inflation. When you moved from 1 to 8 row groups, you increased the number of Thrift-encoded metadata structures in the Parquet footer by 8x. 1. What 
OpenFile
actually measures In the StarRocks Connector Scan profile, 
OpenFile
is primarily a CPU-bound parsing metric, not an I/O metric (especially when your data is in the OS page cache). * The Process: To "open" a Parquet file, StarRocks must read the last ~8 bytes to find the footer length, then read the entire footer (metadata), and finally deserialize the Thrift/Protobuf structures. * The Penalty: The footer contains metadata for every 
(RowGroup, Column)
pair. If you have 100 columns and 8 row groups, StarRocks has to parse 800 
ColumnChunk
metadata objects. This parsing is single-threaded per file. * Sequential Bottleneck: This parsing must finish before the scanner knows where the data pages are located. Thus, it acts as a "serial gatekeeper" that prevents your 8x parallel decoding from starting. 2. Why 
PageIndexTime
jumped 
PageIndexTime
measures the reading and parsing of the 
ColumnIndex
and 
OffsetIndex
structures. These indices are stored separately from the footer. * Like the footer, the Page Index complexity scales linearly with the number of row groups and pages. * With 2MB row groups, you likely created significantly more pages, forcing the reader to spend 130ms just mapping out which pages to skip before it could actually skip them. 3. The "Sweet Spot" Strategy For a 16MB file where you need sub-100ms latency, 2MB row groups are too granular because the metadata-to-data ratio becomes inefficient. The Recommended Strategy: 1. Row Group Size: Move to 4MB or 8MB row groups. * An 8MB row group gives you 2-way parallelism on a 16MB file. While 2x parallelism sounds lower than 8x, the sequential 
OpenFile
overhead will likely drop back down to ~30ms, and 
PageIndexTime
will drop significantly. 2. Use the StarRocks Metadata Cache: * StarRocks (v3.3+) can cache the parsed Parquet footers in its Page Cache (in-memory). * Action: Ensure your BE 
be.conf
has 
datacache_enable = true
. * If the footer is cached, subsequent queries will show 
OpenFile
times of <1ms, regardless of the number of row groups. This is the only way to hit <100ms consistently on many-column Parquet files. 3. Check Column Count: If your dim table is very wide (e.g., 200+ columns) but you only query 5, StarRocks still has to parse the metadata for all 200 columns in the footer. If you can't use the cache, consider dropping unused columns from the POC table to shrink the footer. Summary of Recommendations * Set row-group size to 8MB. This provides a balance of intra-file parallelism and manageable metadata. * Warm the StarRocks Cache. Verify that 
OpenFile
drops on the second execution of the same query. If it doesn't, check your 
datacache
settings in 
be.conf
. * Intra-file Parallelism: There is no StarRocks setting to parallelize the parsing of a single file's footer. Parallelism in StarRocks is designed to handle thousands of files across BEs, so it assumes the per-file metadata is small relative to the data. Target for your next test: With 8MB row groups and a warm footer cache, you should see 
OpenFile
< 5ms and 
ScanTime
~40ms, putting you well within your 100ms budget. References • data_source/data_cache.md • administration/management/BE_configuration.md • best_practices/query_tuning/query_profile_operator_metrics.md • data_source/catalog/iceberg/iceberg_catalog.md
g
@Rocky After a table rebuild with new row counts and statistics, V2's query plan degraded from 5 ICEBERG_SCAN nodes (with MULTI_CAST_DATA_SINK sharing one scan across multiple CTEs) to 15+ nodes (same tables scanned multiple times). V1 still produces a 5-node plan. The change that triggered this: table 
fact_insights_market_product_daily_poc
and 
fact_insights_market_product_seller_daily_poc
were rebuilt with 
WRITE ORDERED BY customer_id ASC, date ASC
. Is the MULTI_CAST/CTE-reuse decision in StarRocks statistics-driven (e.g., estimated row counts changed post-rebuild)? Is there a session variable or query hint to force CTE reuse / MULTI_CAST_DATA_SINK for a specific CTE, similar to 
/*+ SET_VAR(cbo_cte_reuse_rate_v2=...) */
?
r
Yes, the decision to use CTE reuse (physically implemented via 
MULTI_CAST_DATA_SINK
) is cost-based and statistics-driven. 1. Why the plan degraded post-rebuild When you rebuilt your tables with 
WRITE ORDERED BY
, the statistics (cardinality, NDV, and potentially histograms) were updated. The StarRocks Cost-Based Optimizer (CBO) compares two strategies: * Inlining (Redundant Scans): Scanning the table multiple times. This is preferred if the cost of re-scanning (especially with high pruning/parallelism) is lower than the overhead of materializing the CTE. * Reuse (MULTI_CAST): Scanning once and multicasting the result. This has overhead in terms of fragment synchronization and potential "stop-and-wait" buffering in the Multi-Cast Sink. If your new statistics suggest that the table scans are now "cheaper" (due to better sorting/pruning metadata), the CBO may decide that inlining is more efficient than the coordination cost of a 
MULTI_CAST_DATA_SINK
. 2. How to Force CTE Reuse There is no specific 
/_+ REUSE_CTE _/
hint, but you can force this behavior using session variables or 
SET_VAR
hints. The "Force" Variable: 
cbo_cte_reuse_rate
 The most effective way to force reuse is to set 
cbo_cte_reuse_rate
to 0. * Logic: This variable acts as a cost multiplier for the CTE producer. Setting it to 0 tells the optimizer that the "cost" of the shared scan is zero, effectively forcing it to choose the reuse path over redundant scans. * Usage:
Copy code
sql
    -- Session level
    SET cbo_cte_reuse_rate = 0;

    -- Query level hint
    SELECT /_+ SET_VAR(cbo_cte_reuse_rate = 0) _/ ...
The "Hint" variable: 
cbo_cte_reuse
 Ensure this is set to 
true
(default). This enables the rewrite rules that look for reuse opportunities. It also enables the optimizer to respect the 
prefer_cte_rewrite
hint if your version supports it. Threshold Variable: 
cbo_cte_force_reuse_node_count
 If you are on v3.5.3+, you can use 
cbo_cte_force_reuse_node_count
. * If the number of nodes in your CTE's plan tree exceeds this value, StarRocks will force reuse regardless of the cost model. * Setting this to a very low number (e.g., 
1
) will force reuse for almost any CTE. 3. Summary of Parameters Variable | Effect | Recommended for POC 
cbo_cte_reuse_rate
 | Controls the cost threshold for reuse. | Set to 
0
 to force 
MULTI_CAST
. 
cbo_cte_reuse
 | Boolean toggle for the feature. | Ensure 
true
. 
cbo_cte_force_reuse_node_count
 | Node-count threshold for auto-reuse. | Set to 
1
(on v3.5+) to force reuse. Recommendation: Try adding 
/_+ SET_VAR(cbo_cte_reuse_rate = 0) _/
to your query. This should collapse your 15+ 
ICEBERG_SCAN
nodes back down to the shared 5-node plan by making the 
MULTI_CAST_DATA_SINK
appear significantly "cheaper" than redundant scans inlining to the optimizer. References • knowledge/usage/best-practice.md • sql-reference/System_variable.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • best_practices/query_tuning/query_hint.md
g
@Rocky For large fact tables (hourly_sales, fact_daily, fact_seller) with 16MB file+row-group and `WRITE ORDERED BY customer_id ASC, date ASC`: EXPLAIN ANALYZE shows IOTaskExecTime min≈250µs, max≈300ms — 1 IO task doing all work for customer_id=2. connector_io_tasks_per_scan_operator=16 is set. Two-part question: 1. Is the parallelism per-file or per-row-group? Since file_size = row_group_size = 16MB, each file has exactly 1 row group → max 1 IO task per file → parallelism only across files. If customer_id=2 has only 2-3 files in hourly_sales, we have at most 2-3 parallel tasks. Is this the root cause? 2. We tried 2MB file+row-group earlier: OpenFile jumped from 16ms→133ms (8× more footer metadata, sequential parsing). What is the optimal file/row-group combination that balances footer parse overhead vs IO parallelism? For example: 16MB file with 4MB row group (4 tasks per file, 4× more footer) — is there a sweet spot?
r
The profile metrics you are seeing confirm that you have a "Morsel Starvation" problem. Here is the technical explanation and the strategy to find your "sweet spot." 1. Parallelism: Per-File or Per-Row-Group? In StarRocks, the unit of work for a Parquet scan (a "morsel") is the Row Group. * The Root Cause: Your assessment is 100% correct. If 
file_size = row_group_size = 16MB
, then 1 file = 1 morsel. * The Bottleneck: Even though you have 
connector_io_tasks_per_scan_operator = 16
, that variable only sets the ceiling for concurrency. If 
customer_id=2
only has 2 files, the scan operator only finds 2 morsels. It will use 2 threads and leave the other 14 idle. * The Skew: This is why you see a 300ms max (one thread doing all the work for a 16MB row group) vs. a 250µs min (other BEs/threads finishing instantly because they were assigned nothing). 2. The 
OpenFile
Overhead & The "Sweet Spot" The jump in 
OpenFile
time (16ms → 133ms) happened because you reduced the File Size to 2MB. In the StarRocks profile, 
OpenFile
is a cumulative metric for the BE. * Previous Setup: 16MB files. To read 16MB of data, the BE opens 1 file. Overhead: ~16ms. * 2MB Setup: To read that same 16MB of data, the BE had to open 8 files. 8 files × ~16ms = ~128ms. This matches your 133ms result perfectly. * The Sequential Trap: 
OpenFile
(footer parsing) is sequential per file. If you have many small files, you spend all your time in the "serial" phase of the query before the "parallel" decoding phase even begins. The Optimal Strategy: "Fat Files, Thin Row Groups" To hit your sub-100ms goal, you must decouple file size from row group size. You want to minimize the number of files (to keep 
OpenFile
low) while maximizing the number of row groups (to give StarRocks enough morsels to parallelize). Recommended Configuration: * `write.target-file-size-bytes`: 128MB. * This ensures that even for larger customers, you only parse a few footers. For your 16MB 
customer_id=2
case, it will still result in 1 file, keeping 
OpenFile
at ~16ms. * `write.parquet.row-group-size-bytes`: 4MB. * For a 16MB data set, this creates 4 row groups inside that single file. * StarRocks will parse 1 footer (16ms) and then immediately launch 4 parallel IO tasks to decode the 4 row groups (~50-70ms). Summary Recommendation Metric | 16MB File / 16MB RG | 2MB File / 2MB RG | 128MB File / 4MB RG (Proposed) Morsels for 16MB data | 1 | 8 | 4 OpenFile Time | ~16ms | ~130ms | ~18ms Parallelism | None (Single threaded) | High (But offset by serial open) | Moderate (Balanced) Est. Total Time | ~250ms | ~200ms | < 80ms Why 4MB and not 2MB? If your table is "wide" (many columns), the Parquet footer contains a 
ColumnChunk
metadata block for every 
(Row Group * Column)
. If you have 200 columns and 8 row groups (2MB), the footer becomes massive and 
OpenFile
parsing time will increase even if you only have one file. 4MB or 8MB row groups are generally the "Goldilocks zone" for StarRocks external scans on warm data. Session Variable Check: Ensure 
enable_connector_adaptive_io_tasks = true
(default). This allows StarRocks to use the 16 tasks you' capacity effectively once you provide it with enough morsels. References • sql-reference/System_variable.md • unloading/Spark_connector.md
g
@Rocky customer_id=2 has 17M rows in hourly_sales and 4M rows in fact_seller. Both tables use PARTITION BY (customer_id) and WRITE ORDERED BY customer_id ASC. Files are 128MB, row groups 4MB. BackendNum=3 on both scan fragments, but IOTaskExecTime for fact_seller shows min=276µs/max=317ms — 1 BE doing everything. For hourly_sales: min=45ms/max=313ms (some parallelism but skewed). Questions: 1. With PARTITION BY (customer_id), all of customer_id=2's files land in the same S3 prefix. How does StarRocks distribute those files across 3 BEs? Is it file-level consistent hash, or does the whole partition go to 1 BE? How do we force even distribution? 2. For the 500ms cold query target with 17M rows in hourly_sales: is this achievable without`enable_scan_block_cache=true`? What's the theoretical floor for reading ~3.3GB of Iceberg Parquet from S3 cold? 3. Is there a StarRocks session variable to force`enable_scan_block_cache`to cache aggressively so repeated cold queries (first query per day) hit the SSD cache instead of S3?
r
To achieve sub-500ms latencies on 17M+ rows from S3, you are hitting the boundaries of metadata management and I/O scheduling. Here is the breakdown for your configuration: 1. Skew and Backend Distribution Even with 
PARTITION BY (customer_id)
, StarRocks does not pin a partition to a single BE. For external catalogs (Iceberg), the FE generates "splits" (ranges within files) and distributes them across the BEs using a consistent hash of the split ID/path. If you see 
BackendNum=3
but only one BE is working (
IOTaskExecTime
skew), the cause is likely Split Granularity or Scheduler Bottlenecks: * The Problem: If 
customer_id=2
is in one large file (128MB), the FE might only generate a few splits. If those splits happen to hash to the same BE, or if the "morsel" size is too large, you get no intra-node parallelism. * The Solution: * Set 
morsel_max_split_size_per_task = 16777216
(16MB). This forces the FE to break large files into smaller logical chunks (morsels) that can be distributed to more BEs. * Verify 
enable_scan_split_balance = true
. This allows the scheduler to move splits from "busy" BEs to "idle" BEs dynamically. 2. The Theoretical Floor for S3 Cold Queries Reading 3.3GB of Parquet from S3 in <500ms (cold) is nearly impossible due to the Sequential Metadata Penalty: * S3 Latency: Every 
GET
request has a 20-50ms TTFB (Time to First Byte). A cold query must: 1. Get Iceberg Metadata (Avro), 2. Get Parquet Footers, 3. Get Column Indexes, 4. Get Data. That is 4 sequential round-trips (~150-200ms) before the first row is decoded. * Throughput: 3.3GB in 500ms requires 6.6GB/s aggregate throughput. While S3 can scale to this, a 3-node cluster will struggle to saturate that bandwidth cold without hitting S3 throttling or connection pool limits. * Conclusion: You cannot hit 500ms cold for that volume. Your goal should be 500ms warm (using Data Cache) and ~1.5-2s cold. 3. Aggressive Caching Strategy If you have a "first query of the day" problem, you need to use Cache Warmup rather than just relying on the query itself. * Manual Warmup (
CACHE SELECT
): Use this to proactively pull data into the SSD cache before the business day starts:
Copy code
sql
    CACHE SELECT * FROM iceberg_catalog.db.hourly_sales
    WHERE customer_id = 2 AND date = '2024-01-01';
This is a synchronous command that populates the 
datacache
(SSD) immediately. * Aggressive Session Variables: To ensure the first query caches everything it touches without being evicted by other queries: * 
SET enable_scan_datacache = true;
(Ensure 
datacache_enable=true
in 
be.conf
). * Prioritize Cache: While StarRocks doesn't have a "force-cache" per-query hint, you can increase the block size in 
be.conf
(
datacache_block_size = 1048576
, i.e., 1MB) to ensure larger, contiguous chunks are pulled from S3. Summary of Next Steps 1. Balance the Load: Set 
morsel_max_split_size_per_task = 16777216
to break the 128MB file into 8 morsels that can be spread across your 3 BEs. 2. Verify Data Cache: Check 
be.conf
for 
datacache_enable = true
and 
storage_root_path
(ensure it points to your NVMe/SSD). 3. Implement Warmup: Trigger a 
CACHE SELECT
for 
customer_id=2
on a schedule. This transforms the "cold" S3 query into a "warm" local NVMe query, which will hit your <500ms target easily. References • data_source/data_cache.md • administration/management/BE_configuration.md • sql-reference/System_variable.md • data_source/block_cache_warmup.md • data_source/catalog/iceberg/iceberg_catalog.md
g
@Rocky how can i get less than 500 ms latency from below tables n query DDLs- query -
single_brand_product_done.sqlDDLs.sql
r
To achieve sub-500ms latency on 17M+ row Iceberg tables from S3, you must eliminate the "1 BE doing everything" bottleneck and the "S3 cold start" metadata overhead. Below is the optimized configuration for your specific layout (128MB files, 4MB row groups, sorted by 
customer_id
): 1. Fix the Parallelism (Solving the 1 BE Skew) Your current 
IOTaskExecTime
skew (one BE at 300ms, others at 0) happens because the FE is assigning the entire 128MB file as a single "split" to one node. * Action: Set the split size at the session level to force the FE to break the 128MB file into multiple morsels that can be distributed across all 3 BEs.
Copy code
sql
    -- Force the FE to break large files into 16MB chunks (splits)
    SET connector_max_split_size = 16777216;
_This ensures that your 128MB file for 
customer_id=2
is split into 8 morsels, which StarRocks will then distribute across your 3 BEs, effectively cutting the execution time by ~3x._ 2. Accelerate Metadata (FE Caching) Cold queries often spend 200–300ms just parsing Iceberg manifest files and footers from S3 before any data is read. * Action: Enable FE-side disk caching for metadata and ensure the background refresher is active.
Copy code
sql
    -- Session variable to ensure metadata is cached
    SET enable_iceberg_metadata_cache = true;

    -- FE config (set in fe.conf or via ADMIN SET FRONTEND CONFIG)
    -- enable_iceberg_metadata_disk_cache = true
* Plan Mode: If your query only touches one customer (small metadata), set 
plan_mode = 'local'
. If it scans many files, use 
plan_mode = 'distributed'
. 3. Data Caching (BE Side) S3 cold reads (even with 16 parallel tasks) will rarely hit <500ms for 3.3GB of data due to S3 throughput limits and latency. You must hit the SSD cache. * Action: Ensure Data Cache is enabled and use a manual warmup for your "first query of the day."
Copy code
sql
    -- 1. Enable cache for the session
    SET enable_scan_datacache = true;

    -- 2. Warm up the cache for customer_id=2 (Do this on a schedule)
    CACHE SELECT * FROM iceberg_catalog.db.hourly_sales
    WHERE customer_id = 2 AND date = '2024-01-01';
4. Query Hint for CTE/Join (If applicable) Since your query plan degraded from 5 nodes to 15+ nodes post-rebuild, you should force the optimizer to use the 
MULTI_CAST_DATA_SINK
strategy. * Action: Use the 
cbo_cte_reuse_rate
hint to collapse redundant scans.
Copy code
sql
    SELECT /_+ SET_VAR(cbo_cte_reuse_rate = 0) _/ ...
5. The "Final Boss" Solution: Asynchronous MV If the S3/Iceberg overhead remains too high, the most reliable way to hit <500ms is to create an Asynchronous Materialized View on top of the Iceberg table. * Why: An MV stores data in StarRocks' native OLAP format on local SSDs. It eliminates Iceberg manifest parsing and Parquet footer decoding overhead entirely.
Copy code
sql
    CREATE MATERIALIZED VIEW customer_2_fast_mv
    PARTITION BY (date)
    DISTRIBUTED BY HASH(customer_id)
    REFRESH ASYNC EVERY (1 HOUR) -- Or manual
    AS
    SELECT ... FROM iceberg_catalog.db.hourly_sales WHERE customer_id = 2;
Summary of variables to set for your query:
Copy code
sql
SET connector_max_split_size = 16777216;
SET connector_io_tasks_per_scan_operator = 16;
SET enable_scan_datacache = true;
SET cbo_cte_reuse_rate = 0;
SET plan_mode = 'local';

-- Execute your query here
-- References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/data_cache.md • sql-reference/System_variable.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References
g
@Rocky Suggest improvement in the query or iceberg table properties to get cold query latency less than 500 ms
DDLs.sqlsingle_brand_product_done.sql
r
To achieve sub-500ms cold latency on a 3.3GB scan from S3, you are fighting a physical battle against metadata round-trips and S3 throughput. In a 3-node cluster, 3.3GB in 500ms requires an aggregate throughput of 6.6 GB/s (or ~2.2 GB/s per BE). Here are the specific improvements to your table properties and query environment to make this possible: 1. Optimize Iceberg Table Properties The goal is to move as much "knowledge" as possible into the metadata so StarRocks doesn't have to touch S3 for anything other than raw data. * Enable Full Metadata Metrics: Ensure Iceberg writes column stats for all columns. This allows StarRocks' optimizer (CBO) to prune files and row groups without opening the Parquet footers. _ _Table Property:* 
'write.metadata.metrics.default'='full'
* Create Bloom Filters for `customer_id`: If you haven't already, add a Bloom Filter to the 
customer_id
column. This is highly effective for high-cardinality ID columns to skip files entirely during "cold" scans. _ _Table Property:* 
'write.parquet.bloom-filter-enabled.column.customer_id'='true'
* Minimize Delete Files: If your table is Iceberg v2, check for equality or position delete files. These are "latency killers" for cold queries because StarRocks must merge them at read-time. Run an Iceberg Rewrite Data Files or Rewrite Manifests action to merge these into base files. 2. FE-Level "Warm" Metadata (Cold Query fix) A "cold" query often spends 200ms+ just talking to Glue/HMS and reading manifest files. You can eliminate this by enabling the FE Disk Cache and Background Refresh. * FE Configuration (
fe.conf
):
Copy code
properties
    # Enable FE to cache Iceberg manifests on local disk
    enable_iceberg_metadata_disk_cache = true
    # Keep metadata fresh in the background so the first query doesn't wait
    enable_background_refresh_connector_metadata = true
    # Increase the metadata refresh interval if your data changes frequently
    background_refresh_metadata_interval_millis = 60000
* Catalog Property: When creating the catalog, set 
iceberg_manifest_cache_with_column_statistics = true
to allow the FE to cache the min/max stats from the manifest. 3. Force Maximum Parallelism You mentioned one BE doing all the work for 
fact_seller
. This is because the "split" size (how StarRocks chunks the files) is likely defaulting to 64MB or 128MB. For a 3-node cluster, a single 128MB file will only ever go to one BE. Set these session variables before your query:
Copy code
sql
-- Break the 128MB file into smaller 8MB morsels to spread across all 3 nodes
SET connector_max_split_size = 8388608; 

-- Increase the IO ceiling per node
SET connector_io_tasks_per_scan_operator = 32;

-- Enable the CBO to use Iceberg metadata for better pruning
SET enable_get_stats_from_external_metadata = true;

-- Use 'local' plan mode if you are only querying a single partition (less overhead)
SET plan_mode = 'local';
4. Query Improvements * Column Pruning: If your fact tables are wide, ensure you are NOT using 
SELECT *
. Every extra column adds a GET request to S3 for that column's data chunks. * Avoid Complex Expressions in Filters: Use 
WHERE customer_id = 2
rather than 
WHERE CAST(customer_id AS STRING) = '2'
. Type mismatches prevent partition and row-group pruning. * Limit Pushdown: If you only need a sample, ensure 
LIMIT
is at the top level. StarRocks can push this down to the scan to stop S3 IO as soon as the threshold is met. Summary: The "Theoretical Floor" If you implement the FE Metadata Disk Cache and Background Refresh, you eliminate the ~200ms metadata penalty. If you set 
connector_max_split_size = 8MB
, you utilize all 3 BEs. With these changes, a cold query on 3.3GB should look like this: 1. Metadata (FE): 10-20ms (Hit local cache). 2. Scheduling: 10ms. 3. Data Scan (S3): ~300-400ms (Saturating 3 BEs at 1GB/s+ each). 4. Total: ~450ms.
Without the FE metadata cache, it is virtually impossible to stay under 500ms on a truly cold start. References • data_source/catalog/iceberg/iceberg_catalog.md • administration/management/FE_configuration.md • administration/management/BE_configuration.md • data_source/feature-support-data-lake-analytics.md
g
@Rocky StarRocks 3.3.22, Iceberg external tables via Glue catalog, 3-node cluster. Tables have 
PARTITION BY (customer_id)
, 
WRITE ORDERED BY customer_id ASC
, 128MB files, 4MB row groups. Querying customer_id=2 only. EXPLAIN ANALYZE shows: • `hourly_sales`(17M rows in partition): IOTaskExecTime min=45ms, max=313ms → partial parallelism • `fact_seller`(4M rows in partition): IOTaskExecTime min=276µs, max=317ms →1 task doing everything, others idle • Both fragments run on BackendNum=3 • `connector_max_split_size=8388608`(8MB) is set but skewpersists for fact_seller Q1 — Why doesn't 
connector_max_split_size
fix fact_seller's 1-task skew? hourly_sales shows partial improvement (min went from 216µs → 45ms) but fact_seller still shows min=276µs despite the same split setting. Our hypothesis: fact_seller's customer_id=2 partition has fewer large files that all hash to the same BE via consistent hashing. Is this correct? If so, is there a variable to force round-robin file distribution instead of hash-based? Something like 
connector_file_scan_round_robin=true
? Q2 — Can 
lob_id=2
skip row groups within the partition? 
fact_seller
and 
hourly_sales
both have 
write.metadata.metrics.column.lob_id='full'
(full min/max stats) and 
WRITE ORDERED BY customer_id ASC, date ASC
. But 
lob_id
is NOT in the sort key, so rows with lob_id=2 are scattered across all row groups within the customer_id=2 partition. Does 
enable_iceberg_column_statistics=true
(exists in 3.3.22) cause StarRocks to use lob_id min/max to skip row groups? If lob_id happened to be sorted (added as a secondary sort key: 
customer_id ASC, lob_id ASC, date ASC
), how much row group pruning would StarRocks achieve for lob_id=2 out of ~10 distinct lob values? Q3 — 
plan_mode='local'
effect on cold latency? 
plan_mode='local'
exists and can be set per-session. For a query that touches exactly 1 Iceberg partition (customer_id=2), does 
plan_mode='local'
reduce the FE planning and Glue manifest round-trip time? Is there a risk of suboptimal execution plan vs 
plan_mode='auto'
?
r
To achieve sub-500ms cold latency on 17M rows from S3 with a 3-node cluster, you are pushing the limits of I/O and metadata handling. Here are the answers to your specific tuning questions for StarRocks 3.3.22: Q1 — Why doesn't 
connector_max_split_size
fix `fact_seller`'s 1-task skew? Your hypothesis about consistent hashing is likely correct. StarRocks typically hashes splits based on the 
path + offset
. However, if 
fact_seller
(4M rows) results in a small number of 8MB splits (e.g., 20-30 splits), it is statistically possible for a consistent hash to land many of them on the same BE, especially with only 3 nodes. The Fixes: 1. Enable Split Balancing: StarRocks has a dynamic "morsel" scheduler that can rebalance splits from "busy" nodes to "idle" nodes. Ensure this is enabled:
Copy code
sql
    SET enable_scan_split_balance = true;
2. Aggressive Splitting: Since 
fact_seller
is smaller than 
hourly_sales
, try reducing the split size even further to force more tasks:
Copy code
sql
    SET connector_max_split_size = 1048576; -- 1MB splits
3. Adaptive I/O: If 
enable_connector_adaptive_io_tasks
is 
true
(default), StarRocks might be limiting parallelism because it thinks the BE is already saturated. Try disabling it to force the manual task count:
Copy code
sql
    SET enable_connector_adaptive_io_tasks = false;
    SET connector_io_tasks_per_scan_operator = 32;
Note: There is no 
connector_file_scan_round_robin
variable. StarRocks relies on consistent hashing for Data Cache locality (so the same file offset always hits the same BE cache). If you force round-robin, you would destroy your cache hit rate for repeated queries. ────────── Q2 — Can 
lob_id=2
skip row groups within the partition? The short answer: Only if you sort the data by 
lob_id
. 1. Pruning Mechanism: StarRocks uses the min/max stats in the Parquet footers (and Iceberg manifests if 
enable_get_stats_from_external_metadata
is true) to skip row groups. 2. The "Scattered" Problem: If 
lob_id
is scattered, every 4MB row group will likely contain at least one row where 
lob_id=2
. Therefore, the 
min
will be ≤ 2 and the 
max
will be ≥ 2 for every single row group. In this scenario, StarRocks cannot skip any row groups, and the "pruning" effect will be 0%. 3. Effect of Sorting: If you change your sort key to `ORDERED BY customer_id, lob_id, date`: * Rows with 
lob_id=1
will occupy the first few row groups. * Rows with 
lob_id=2
will occupy the next few. * With ~10 distinct 
lob_id
values, StarRocks would be able to skip roughly 90% of the row groups within that partition. This would reduce your I/O from 3.3GB to ~330MB, which is the only way to reliably hit the <500ms cold target. ────────── Q3 — 
plan_mode='local'
effect on cold latency? For a query touching exactly 1 partition, 
plan_mode='local'
is highly recommended. 1. Reduced Overhead: In 
auto
or 
distributed
mode, the FE might decide to launch a "distributed planning" fragment where BEs read the Iceberg manifest files. For small metadata (1 partition), the overhead of starting this distributed fragment often exceeds the time it takes for the FE to just do it itself. 2. Latency Impact: 
plan_mode='local'
tells the FE to parse the Iceberg manifests and generate the scan ranges locally. This typically saves 50ms–150ms of "planning time" in the query profile. 3. The Risk: There is very little risk for a 1-partition query. The risk of 
local
only appears for "massive" queries (e.g., 
SELECT * FROM large_table
with 100,000 files), where the FE might run out of memory or become a single-threaded bottleneck parsing thousands of Avro manifest files. Summary Checklist for <500ms Cold: * FE: 
SET plan_mode = 'local';
* FE: 
SET enable_get_stats_from_external_metadata = true;
* Parallelism: 
SET connector_max_split_size = 1048576;
and 
SET enable_scan_split_balance = true;
g
@Rocky We've exhausted all session variables available in StarRocks 3.3.22 (
connector_max_split_size
, 
connector_io_tasks_per_scan_operator
, 
enable_connector_adaptive_io_tasks
, 
cbo_cte_reuse_rate
, 
plan_mode
). Table properties tried: 2MB/16MB/128MB files, 4MB/16MB row groups, sort keys with/without lob_id, bloom filters on/off, lz4/zstd. Current floor: ~1950ms cold, ~1600ms warm on a 3-node cluster with 17M rows in hourly_sales and 4M rows in fact_seller. Direct question: Is there any session variable, query hint, or table property in StarRocks 3.3.22 that we have NOT tried that could meaningfully reduce cold scan latency on these specific large Iceberg external tables? Please only suggest options that exist in version 3.3.22 — previous suggestions (
morsel_max_split_size_per_task
, 
enable_scan_split_balance
, 
enable_get_stats_from_external_metadata
) did not exist in this version.
r
Since you are on StarRocks 3.3.22 and have already tuned the standard connector variables, you have likely hit the "Metadata & I/O Floor" of the external Iceberg reader. To break below 500ms from your current ~1.9s, you need to address the FE-to-S3 metadata bottleneck and internal parallelism that isn't covered by 
connector_
variables. Here are the specific, version-compatible options you likely haven't tried yet: 1. FE-Side Metadata Disk Caching (Crucial for Cold Latency) A "cold" query in 3.3.22 spends a significant portion of its 1.9s just fetching Iceberg manifest files from S3 to the FE. By default, this is in-memory only. * Action: Add these to your 
fe/conf/fe.conf
and restart the FE. This moves the "cold" manifest metadata from S3 to the FE's local SSD.
Copy code
properties
    enable_iceberg_metadata_disk_cache = true
    iceberg_metadata_disk_cache_capacity = 2147483648 # 2GB
    # Ensure the background refresh keeps this cache hot
    enable_background_refresh_connector_metadata = true
2. Catalog-Level Property: Manifest Stats Cache In 3.3.x, session variables often fail to trigger row-group pruning because the FE doesn't have the column stats cached yet. You must enable this at the Catalog level: * Action: Alter your Iceberg catalog to include this property:
Copy code
sql
    ALTER CATALOG iceberg_catalog SET PROPERTIES (
        "iceberg_manifest_cache_with_column_statistics" = "true"
    );
Why: This forces StarRocks to cache the min/max stats of every column (including your 
lob_id
) directly in the manifest cache, allowing the FE to prune files/row-groups before the query even reaches the BEs. 3. Session Variable: 
pipeline_dop
 While 
connector_io_tasks_per_scan_operator
controls I/O threads, 
pipeline_dop
controls the number of execution pipeline instances. For a 3-node cluster, if the auto-scheduler is being conservative, you may be under-utilizing your CPU. * Action:
Copy code
sql
    SET pipeline_dop = 16; -- Or set to your actual CPU core count per BE
4. Session Variable: Parquet Late Materialization If your query has a selective filter (like 
lob_id = 2
), StarRocks can read only the 
lob_id
column first, and only fetch the other columns for the rows that pass. * Action:
Copy code
sql
    SET enable_parquet_late_materialization = true;
5. Address the 
fact_seller
Skew The reason 
connector_max_split_size
didn't fix the skew is likely Data Cache Locality. StarRocks 3.3 hashes splits to specific BEs to keep the cache "sticky." Since 
enable_scan_split_balance
is indeed a 3.4+ feature, your best 3.3-native workaround is: * Bypass the Cache (for testing): If you turn off the data cache for a specific query, StarRocks might distribute the splits more evenly because it no longer cares about locality.
Copy code
sql
    SET enable_scan_datacache = false;
Note: If the query is faster without the cache, your 3-node cluster is suffering from "hotspotting" where one node's SSD/CPU is the bottleneck for that specific partition's hash. 6. The "Hard Truth" for 500ms on S3 In StarRocks 3.3, scanning 17M rows + 4M rows (3.3GB) from S3 via an external table and hitting <500ms consistently is nearly impossible due to the overhead of the Parquet/Iceberg format compared to StarRocks' native segment format. The only 3.3.22 feature that will definitively get you <500ms is: Asynchronous Materialized Views (MV). * Property: Create an MV on top of your Iceberg tables. * Why: An MV transforms the Iceberg data into StarRocks' native storage format on the BE's local SSDs. * Result: You move from an "External Table" scan (1900ms) to a "Native Table" scan, which typically handles 20M rows in <100ms on a 3-node cluster. Summary of new variables to try in 3.3.22: ```sql SET pipeline_dop = 16; SET enable_parquet_late_materialization = true; SET enable_predicate_reorder = true;
g
@Rocky Context: Shared-data StarRocks cluster (CN nodes only, no BE persistent storage). Iceberg external tables on S3. Cluster default: 
enable_scan_datacache=true
. EXPLAIN ANALYZE shows 
enable_scan_datacache: true -> false
in NonDefaultVariables when we benchmark — meaning our sessions were overriding it. When datacache is left ON (cluster default), warm TotalTime from EXPLAIN is ~922ms despite IOTaskWaitTime≈92µs (data in OS page cache, not S3). The query aggregates 4.7M hourly_sales rows and 1.2M fact_seller rows down to 20 output rows. Q: Given that our warm EXPLAIN TotalTime is ~922ms even with data already in memory (IOTaskWaitTime≈0), is 500ms cold achievable from Iceberg without pre-aggregation? The bottleneck appears to be CPU aggregation on 4.7M + 1.2M rows, not I/O. Even with perfect CN NVMe datacache, would the query execution time floor be ~900ms? If yes, the only path to 500ms is reducing the row count before it reaches StarRocks — either via Spark pre-aggregation or a coarser Iceberg table grain. Please confirm or correct this analysis.
r
Your analysis is correct. If your warm latency (with data already in the local NVMe Data Cache and 
IOTaskWaitTime
near zero) is ~920ms, it is mathematically impossible to achieve a 500ms cold latency on the same raw data. In StarRocks 3.3.22, you have hit the "Iceberg/Parquet Compute Floor." Even without the network penalty of S3, the CPU cost of deserializing Parquet pages, converting them into StarRocks’ vectorized blocks, and performing the aggregation is exceeding your budget. Why the "Floor" is ~900ms (The Iceberg Tax) While StarRocks’ native storage engine can process 50M–100M rows per second per core, the External Iceberg Reader is significantly heavier: * Vectorization Overhead: Reading Parquet requires decompressing pages and decoding RLE/Dictionary encoding into StarRocks memory. This is CPU-intensive compared to reading native segments. * Column Mapping: Every query on Iceberg involves a schema evolution check (mapping Iceberg IDs to Parquet IDs), which adds overhead even if cached. * Aggregate Bottleneck: Since you are aggregating ~5.9M rows down to 20, the cost of the 
AGGREGATE
operator + the 
SCAN
operator is your fixed price. Is there ANY variable left in 3.3.22? If you want to try one last "hail mary" to squeeze the compute time: 1. 
SET pipeline_dop = 0;
(or a high fixed number like 16/32): In 3.3, if your 
pipeline_dop
is defaulting to a low number (like 4 or 8) based on the cluster's core count, the aggregation might be CPU-starved. Force it higher to see if the 922ms drops. 2. 
SET enable_parquet_late_materialization = true;
: This exists in 3.3. If your query has a filter, it only reads the filtered column first. However, if you are scanning almost the whole partition, this won't help. 3. 
SET enable_scan_datacache_propagate = true;
: In some shared-data configurations, this ensures that the metadata fetched during planning is propagated more efficiently to execution nodes. The Verdict: The Path to <500ms Since you cannot change the laws of physics regarding Parquet deserialization speed, you must reduce the volume of data processed. You have three viable paths in 3.3.22: 1. Asynchronous Materialized Views (The "Internal" Path): Create an MV in StarRocks that mirrors your Iceberg tables. * Result: This converts the Iceberg data into native StarRocks segments. Your ~922ms warm time will likely drop to <100ms because StarRocks can skip the Parquet/Iceberg tax and use its native indexing and primary/sort keys. 2. Coarser Iceberg Grain (The "Schema" Path): If your query always aggregates to a certain level (e.g., daily), create an Iceberg "summary" table. If you reduce that 17M rows to 1M rows, your 922ms will scale down linearly to ~100ms. 3. Z-Ordering (The "Layout" Path): If you are filtering by 
lob_id
, you must rewrite the Iceberg data using 
Z-ORDER
or a hierarchical sort (
customer_id, lob_id
). Without this, as you noted, 
lob_id
stats are useless because the data is scattered. Proper sorting would allow StarRocks to skip 90% of the Parquet row groups, potentially bringing the scan time under 500ms even for a cold start. Summary: Your analysis of the CPU bottleneck is spot on. In 3.3.22, you cannot "tune" your way out of a ~900ms compute floor on raw external Parquet files. You must either use Native MVs or Physically Sort the data in S3 to enable row-groups to enable row-group prune.skip Icerberg to enable row-group prune.grouping the 
lob_id
values together. References • deployment/shared_data/feature-support-shared-data.md • sql-reference/System_variable.md • data_source/feature-support-data-lake-analytics.md
g
@Rocky Question 1 — hourly_sales date pruning (biggest win, ~130ms reduction)
Context: 
hourly_sales_poc
is 
WRITE ORDERED BY customer_id ASC, lob_id ASC, order_hour_utc ASC
with 4MB row groups. Iceberg table properties include 
'write.metadata.metrics.column.order_hour_utc' = 'full'
(full min/max stats). The data spans 2015–2026 (11 years). For 
customer_id=2, lob_id=2
, there are ~17.1M rows. EXPLAIN ANALYZE shows 
ICEBERG_SCAN (id=15)
at 282ms / 29.5% of TotalTime.
The current query uses 
sum(if(order_hour_utc >= '2024-01-01', ...))
for bucketing — no WHERE clause lower-bound. If we add 
WHERE order_hour_utc >= '2021-01-01'
, we expect to cut rows from 17.1M → ~12M (30% reduction).
Question: With 
order_hour_utc
as the 3rd sort key and full Iceberg column stats written at row-group level, will StarRocks 3.3.22 use 
PageIndexTime
to skip pre-2021 row groups on the scan? Specifically: within a partition for 
customer_id=2
, data is sorted by 
lob_id
first — so 
order_hour_utc
monotonically increases only within each 
lob_id
segment, not globally. Does StarRocks evaluate the min/max filter per row group independently (yes, it would skip), or does it require the filter column to be the primary sort key? Will 
PageIndexTime
in EXPLAIN ANALYZE confirm skipping is happening?
Question 2 — morsel starvation on fact_seller (potential 100ms reduction)
Context: 
fact_insights_market_product_seller_daily_poc
is 
PARTITIONED BY (customer_id)
with 128MB files and 4MB row groups, 
WRITE ORDERED BY customer_id ASC, lob_id ASC, date ASC
. 
customer_id=2
data has ~4 files × 128MB = 512MB.
Session variable: 
SET connector_max_split_size = 8388608
(8MB). Expected: 512MB / 8MB = 64 IO tasks (parallel).
Actual EXPLAIN ANALYZE: 
IOTaskExecTime: 41.037ms [min=283µs, max=146.339ms]
. min=283µs means one task completed almost instantly (found nothing), max=146ms means one task did ALL the work. This is classic morsel starvation — despite 
connector_max_split_size=8MB
, work is not being distributed evenly.
Question: Why does 
connector_max_split_size=8MB
fail to distribute IO tasks evenly across a 128MB Parquet file with 4MB row groups? Is there a known issue in 3.3.22 with Iceberg partition splits vs connector splits? Does the Parquet row group size need to exactly match 
connector_max_split_size
for even split distribution? Is 
enable_connector_adaptive_io_tasks=true
interfering (consolidating splits that finish quickly instead of running them in parallel)?
Question 3 — sellers scan 118ms for 4420 rows (no Detail Timers)
Context: 
market_product_sellers_poc
has 207K total rows, 2MB Parquet files, 
WRITE ORDERED BY customer_id ASC
. No partition. 
customer_id=2
has 4420 rows. EXPLAIN ANALYZE shows 
TotalTime: 118ms
but no Detail Timers (no IOTaskExecTime / FSIOTime / OpenFile breakdown visible).
Question: What causes 
ICEBERG_SCAN
to emit no Detail Timers in EXPLAIN ANALYZE? Does the absence of Detail Timers indicate the scan ran on a single thread with no IO task parallelism? For a table without partition pruning, does StarRocks read all Parquet files to find `customer_id=2`'s 4420 rows, or does it use the Iceberg min/max stats (
'write.metadata.metrics.column.customer_id' = 'full'
) to skip files? If we add 
PARTITIONED BY (customer_id)
to this table, would it reduce scan time from 118ms to near zero for a single-customer query?
Question 4 — RuntimeFilter not applied to hourly_sales inner join
Context: 
hourly_sales_poc
INNER JOINs with 
filtered_products
CTE (4420 
market_product_id
values). The scan reads all 17.1M rows for 
customer_id=2, lob_id=2
and outputs 4.7M rows — meaning 73% of rows scanned are discarded by the join. No RuntimeFilter is shown in EXPLAIN ANALYZE for scan id=15.
For comparison, 
fact_daily (id=0)
and 
dim (id=55)
show 
RuntimeFilter: X -> X (0.00%)
— the filter is applied but prunes zero rows.
Question: Why does hourly_sales not receive a RuntimeFilter from the filtered_products broadcast? Is there a threshold (e.g., 
runtime_filter_max_in_num
) that prevents generating a filter for 4420 values? Could a per-row IN-list filter (not row-group skip, just row-level filtering during scan) reduce the 12.4M discarded rows before they hit the hash join, saving CPU/memory? Is there a session variable in 3.3.22 to force RuntimeFilter on this join?
Summary of expected gains if all 4 are answered favorably: FixCurrentTargetSavingsDate WHERE filter → PageIndex skip282ms~160ms~122msMorsel starvation fix150ms~50ms~100msPartition sellers by customer_id118ms~30ms~88msRuntimeFilter on hourly_sales——~30ms (CPU)Total EXPLAIN TotalTime1013ms~573ms~440ms At 573ms EXPLAIN TotalTime, wall-clock cold latency would likely be ~900–950ms — within striking distance of sub-1000ms.
r
Your targeted optimizations are exactly where the "last mile" of performance is found in StarRocks 3.3.22. Here are the answers to your specific technical blockers: Q1 — 
hourly_sales
Date Pruning & 
PageIndexTime
 * Will it work? Yes. StarRocks evaluates min/max stats per row-group independently. Because you have 
ORDERED BY lob_id ASC, order_hour_utc ASC
, the data for 
order_hour_utc
is contiguous within each 
lob_id
segment. * Pruning Logic: For a row-group that only contains 
lob_id=1
, the 
order_hour_utc
will span the full range of that segment. However, row-groups belonging to 
lob_id=2
will have their own tight min/max ranges for 
order_hour_utc
. StarRocks will skip any row-group where the 
WHERE
range and the row-group [min, max] are disjoint. * Confirmation: 
PageIndexTime
will indeed confirm this. If you see 
PageIndexTime
increasing while 
IOTaskExecTime
(or total rows read) decreases, it means the engine is successfully using the Parquet Page Index to skip data. * Critical Note: Ensure 
enable_parquet_page_index = true
is set (it is default in 3.3, but verify). Q2 — Morsel Starvation on 
fact_seller
 * The Issue: 
connector_max_split_size
defines the potential number of splits, but it does not guarantee parallel execution. In 3.3.22, StarRocks uses Consistent Hashing to assign splits to CN nodes for Data Cache locality. If 4 files hash to the same CN, and that CN's 
pipeline_dop
is low, the splits may be queued behind a single execution thread. * Is Adaptive I/O interfering? Yes. 
enable_connector_adaptive_io_tasks=true
(default) can consolidate tasks if it perceives the I/O as "fast" (e.g., hitting the OS page cache or NVMe). This reduces CPU overhead but ruins parallelism for your specific sub-500ms goal. * The Fix: 1. Force DOP: 
SET pipeline_dop = 16;
(or matching your core count). This forces more pipeline instances to be created for the scan. 2. Disable Adaptive I/O: 
SET enable_connector_adaptive_io_tasks = false;
. 3. Check Split Count: Look at the 
EXPLAIN
(not analyze) output for 
ICEBERG_SCAN
. It should list the number of splits. If it's 64, but 
Analyze
shows only 1-2 tasks doing work, the issue is definitely the pipeline scheduler being too conservative. Q3 — Sellers Scan 118ms (Missing Timers) * Why no Detail Timers? This usually occurs when the scan completes entirely within the Metadata/Open phase or is too fast for the sampling-based profile. If 
customer_id=2
is part of the sort key, the Iceberg FE planning likely used the manifest min/max to prune the search to exactly one or two files. * Partitioning Benefit: If you 
PARTITION BY (customer_id)
, the 118ms will drop significantly (to ~10-20ms). * Current state: The FE has to parse Iceberg Avro manifest files to look at the 
customer_id
column stats for every file. * Partitioned state: The FE simply performs a "Directory List" or a Glue API call for that specific partition prefix. It skips manifest parsing for all other customers entirely. * Verdict: Partitioning by 
customer_id
is a major win for metadata-heavy queries (low row count queries). Q4 — RuntimeFilter on 
hourly_sales
 * The 4420 threshold: This is exactly the problem. The default 
runtime_filter_max_in_num
is 1024. Since your 
filtered_products
CTE has 4420 IDs, StarRocks decides an 
IN
filter is too large and potentially falls back to a Bloom Filter—or drops it if the Bloom Filter cost estimation is too high. * The Fix:
Copy code
sql
    SET runtime_filter_max_in_num = 10000;
    SET runtime_filter_type = 'IN_OR_BLOOM';
* Impact: This won't skip row-groups (Iceberg row-groups are too coarse for 4420 random IDs), but it will apply a Push-Down Filter during the Parquet decoding process. This prevents the 12.4M discarded rows from ever being converted into StarRocks' internal memory format, which is a massive CPU and memory bandwidth saving.
Updated Action Plan for 573ms Target: 1. SQL Change: Add 
WHERE order_hour_utc >= '2021-01-01'
to enable PageIndex pruning. 2. Catalog Change: 
PARTITION
the sellers table by 
customer_id
. 3. Session Variables:
Copy code
sql
    SET pipeline_dop = 16; -- Force CPU parallelism
    SET runtime_filter_max_in_num = 10000; -- Enable RF for 4420 products
    SET enable_connector_adaptive_io_tasks = false; -- Prevent task consolidation
    SET enable_parquet_page_index = true;
With these 3.3.22 compatible changes, your 573ms EXPLAIN TotalTime is a realistic and achievable floor.
g
@Rocky Questions for StarRocks AI: Q1 — fact_seller CPU explosion with pipeline_dop=16 
fact_insights_market_product_seller_daily_poc
(id=20): With 
pipeline_dop=0
(auto), CPUTime=4ms, ScanTime=146ms (morsel starvation, all IO on 1 task). With 
pipeline_dop=16
, ScanTime improved to 74ms but CPUTime jumped to 122ms, making total node time 197ms (worse than before at 150ms). The 3-node cluster: 3 CN nodes. Question: Is there an optimal 
pipeline_dop
value between 0 and 16 that fixes morsel starvation without introducing excessive aggregation CPU overhead? Or is there a way to set scan DOP independently of aggregation DOP (e.g., 
scan_dop
vs 
pipeline_dop
)?
Q2 — IOTaskWaitTime anomaly on fact_seller
With 
enable_connector_adaptive_io_tasks=false
and `pipeline_dop=16`: 
IOTaskExecTime: 3.446ms [min=288µs, max=70ms]
but 
IOTaskWaitTime: 6.576ms [max=70ms]
. Total IOTask time = ~10ms, yet ScanTime=74ms. Where are the other 64ms of ScanTime going? Is this decompression and predicate evaluation outside the IO task framework?
Q3 — runtime_filter_max_in_num equivalent in 3.3.22
The system reported 
runtime_filter_max_in_num
doesn't exist in 3.3.22, with similar vars being 
runtime_filter_scan_wait_time
, 
runtime_filter_on_exchange_node
, 
runtime_filter_early_return_selectivity
. The query INNER JOINs hourly_sales with 
filtered_products
(4420 market_product_ids) but no RuntimeFilter appears in EXPLAIN for the hourly_sales scan. The default threshold for generating an IN-list RuntimeFilter appears to be 1024. What is the correct 3.3.22 variable to raise this threshold to 10000?
r
The behavior you are seeing in StarRocks 3.3.22 highlights the classic transition from I/O-bound to CPU-bound execution as parallelism increases. Here are the specific answers to your troubleshooting: Q1 — Balancing 
pipeline_dop
vs. Aggregate CPU In StarRocks 3.3, there is no separate 
scan_dop
. The 
pipeline_dop
variable is a "global" lever for the query fragment. When you set it to 16, you create 16 instances of the scan and 16 instances of the aggregator. * Why CPUTime exploded: Each of the 16 pipeline instances now incurs the overhead of managing its own hash table for aggregation. For a small result set (only 20 output rows), the overhead of merging 16 partial hash tables (and the context switching of 16 threads) outweighs the benefit of parallelizing the scan. * Optimal Value: Try 
SET pipeline_dop = 4;
 or 
8
. Since 
0
(auto) led to starvation (likely picking 1 or 2), a small integer is often the sweet spot for queries where the scan is large but the final aggregation is small. * Alternative: Set 
SET enable_shared_scan = true;
. This allows StarRocks to decouple the scan instances from the downstream operators to some extent, potentially reducing the redundant work. Q2 — The 64ms "Missing" ScanTime In the StarRocks profile, 
ScanTime
is not just I/O. It is the total time the Scan Operator was "active." * The Breakdown: 
IOTaskExecTime
only measures the time spent by the background thread pool fetching bytes from the S3/Data Cache into a buffer. * Where the 64ms went: This is CPU-driven Parquet decoding and materialization. Specifically: 1. Decompression (LZ4/Zstandard): Turning compressed Parquet pages into raw bytes. 2. Decoding: Converting Parquet’s encoding (RLE, Bit-packing, Dictionary) into StarRocks’ vectorized 
Chunk
format. 3. Predicate Evaluation: Applying your filters (like 
lob_id=2
) to the rows after they are decoded but before they leave the Scan Operator. * Conclusion: Your scan is now compute-bound on Parquet decoding. This confirms that even with 0ms I/O (perfect cache), the CPU cost of "reading" Iceberg data is ~60-70ms for this volume of data. Q3 — Runtime Filter IN-list limit in 3.3.22 You are correct; 
runtime_filter_max_in_num
was introduced in 3.4. In version 3.3.22, the 1024 limit for IN-list filters is hardcoded in the optimizer's rule for generating "IN" type filters. To get a filter for your 4420 products in 3.3.22, you must switch the strategy: 1. Force Bloom Filter: Instead of an IN-list (which is capped at 1024), use a Bloom Filter, which can handle millions of values.
Copy code
sql
    SET runtime_filter_type = 'BLOOM';
    -- Increase the size to ensure no false positives for 4420 values
    SET runtime_bloom_filter_size = 2097152; -- 2MB
2. Check `runtime_join_filter_push_down_limit`: Ensure this is higher than your build side row count (default is 1,024,000, so you should be fine). 3. Use `LargeInPredicate`: If you rewrite the CTE as a physical 
IN (...)
list in the SQL (though not recommended for 4420 values), StarRocks 3.3 uses 
large_in_predicate_threshold
(default 100,000) to convert large lists into a specialized join, but this is for the FE parser, not the execution-time Runtime Filter. Summary for sub-500ms: * The Bottleneck: You have proven that even with data in memory, Parquet decoding takes ~70ms and aggregation takes ~120ms at high DOP. * The Fix: Lower 
pipeline_dop
to 4 (to balance starvation vs overhead) and switch 
runtime_filter_type
to 'BLOOM' to bypass the 1024 IN-list limit. References • sql-reference/System_variable.md • best_practices/query_tuning/query_profile_operator_metrics.md • release_notes/release-3.3.md
g
@Rocky Questions for StarRocks AI: Q1 — Aggregation 194ms with 30 SUM(IF()) on 4.7M rows
EXPLAIN now shows two aggregation nodes (id=17 and id=22) at 98ms + 96ms = 194ms total. These are the local partial aggregation (serialize/update) and global finalization stages. The query has ~30 
SUM(IF(order_hour_utc >= '2024-01-01 07:00:00', col, 0))
expressions over 4.7M rows with 
pipeline_dop=4
. Is there a session variable or hint to speed up the two-phase aggregation? Specifically: (a) Can 
cbo_push_down_aggregate_mode
or a similar variable reduce the number of aggregation stages? (b) Is 
enable_spill = false
already the default (we don't want memory spilling on a 4.7M row aggregate)? (c) Would rewriting the 30 SUM(IF()) expressions as CASE WHEN perform differently, or does StarRocks normalize them to the same plan?
Q2 — EXCHANGE SHUFFLE 107ms
EXCHANGE (id=23) is taking 107ms for a SHUFFLE operation. With 3 CN nodes, this shuffle redistributes partial aggregation results across nodes. Is 107ms reasonable for this data volume, or is there a 
SET_VAR
hint (like 
prefer_compute_node
or changing the shuffle bucket count) that could reduce shuffle time for this query pattern?
r
In StarRocks 3.3.22, you are optimizing the "last mile" of the compute bottleneck. Here is the breakdown of your aggregation and exchange analysis: Q1 — Aggregation (194ms) with 30 SUM(IF()) expressions Your analysis that the bottleneck is CPU-driven is correct. 30 
SUM(IF())
expressions on 4.7M rows mean 141 million conditional evaluations and state updates. * (a) Reducing stages: The session variable to control this is 
new_planner_agg_stage
. * `0`: AUTO (Default). * `1`: ONE\_STAGE (Forces a single global aggregate). * `2`: TWO\_STAGE (Your current 98ms + 96ms plan). * Recommendation: Do not use 
ONE_STAGE
here. With 4.7M rows and 3 nodes, a two-stage aggregate is much faster because the "Local Aggregation" (Stage 1) reduces 4.7M rows down to almost nothing (20 rows) before the data ever hits the network. A one-stage aggregate would force all 4.7M raw rows to be shuffled across the network, which would take seconds, not milliseconds. * (b) Memory Spilling: In StarRocks 3.3, 
enable_spill
is 
false
by default. For 4.7M rows and 20 output buckets, you are well within memory limits. Spilling is definitely not your issue. * (c) SUM(IF) vs CASE WHEN: StarRocks’ SQL parser normalizes 
IF(cond, a, b)
into 
CASE WHEN cond THEN a ELSE b END
during the planning phase. There is no performance difference. * Optimization Tip: If many of your 30 
SUM(IF())
expressions share the same condition (e.g., several use 
order_hour_utc >= '2024-01-01'
), the Common Sub-expression Elimination (CSE) in the StarRocks JIT compiler should already be optimizing this. If they are all unique, the CPU cost is simply the price of 141M instructions. Q2 — EXCHANGE SHUFFLE (107ms) 107ms to shuffle 20 output rows across 3 nodes is disproportionately high. In a healthy 10Gbps/25Gbps network, moving 60 rows (3 nodes x 20 rows) should take <5ms. * Why it shows 107ms: In 
EXPLAIN ANALYZE
, the 
TotalTime
for an 
EXCHANGE
node often includes WaitTime. It isn't just the time spent moving bytes; it is the time the exchange node sat waiting for the upstream local aggregation to finish its first batch. * The "Gather" Optimization: For such a small result set (20 rows), you should check if StarRocks can use a 
GATHER
instead of a 
SHUFFLE
. * 
auto_gather_threshold
: This variable (default: 10,000 rows) tells the optimizer to use a 
GATHER
(send everything to one node) if the estimated row count is low. * Try this: If your 
EXPLAIN
shows a 
SHUFFLE
, it means the CBO estimated the output would be much larger than 20 rows. Ensure your statistics are up to date (
ANALYZE TABLE hourly_sales_poc
), or try forcing the distribution:
Copy code
sql
    SET auto_gather_threshold = 100; -- If the result is < 100 rows, use GATHER
* Shuffle Bucket Count: Changing the bucket count won't help here because your bottleneck isn't the number of partitions, but rather the synchronization "wait" time between the three CN nodes. Summary of Final Tuning for <500ms: To shave off the remaining 200–300ms, apply these to your session: 1. Keep 
new_planner_agg_stage = 0
(or 2): You need that local reduction. 2. Verify JIT: Ensure 
enable_jit = true
(default). 30 
SUM(IF)
expressions benefit immensely from LLVM code generation. 3. Optimize Network Wait:
Copy code
sql
    SET auto_gather_threshold = 100;
    SET enable_exchange_pass_through = true; -- Skips serialization for local-node data
4. The "Big Win": If the 30 
SUM(IF)
evaluations are still too slow, the only remaining path is to pre-calculate those buckets into a Synchronous or Asynchronous Materialized View, which would turn those 30 expressions into simple column reads. References • sql-reference/System_variable.md
g
@Rocky For a complex query (640 lines, 10+ CTEs, ~30 SUM(IF()) expressions), EXPLAIN ANALYZE shows FE planning overhead of ~410ms out of 660ms TotalTime. Backend execution is only 211ms. This means the query optimizer is spending 2× more time planning than the cluster spends executing. The query is structurally identical on every call (same customer_id=2, lob_id=2 — only runtime values change, not the plan shape). Two questions: 1. Does StarRocks 3.3.22 support query plan caching via`PREPARE`/`EXECUTE`prepared statements (so the FE only plans once and reuses the compiled plan on subsequent calls)? If so, does the plan cache survive across sessions/connections? 2. Is there a way to verify whether`query_cache_entry_max_bytes=134217728`(which appears in our NonDefaultVariables) is actually caching result sets for this query, and if so, whether that's reducing planning overhead on repeated calls?
r
That FE planning time of 410ms for a 640-line query is a significant bottleneck, especially when it outweighs execution time. For a query of that complexity, the Cost-Based Optimizer (CBO) is likely spending most of its time on join reordering, common sub-expression (CTE) analysis, and rule-based rewrites. Here are the answers to your technical questions regarding StarRocks 3.3.22: Q1 — `PREPARE`/`EXECUTE` and Plan Caching StarRocks 3.3.22 supports Prepared Statements (introduced in v3.2), but they have specific characteristics in this version: 1. Plan Caching: Yes, using 
PREPARE
and 
EXECUTE
will cache the physical plan in the FE. This skips the parsing, analysis, and CBO optimization phases, which should reduce your 410ms planning overhead to nearly zero (only the cost of parameter binding and scheduling remains). 2. Scope: By default, prepared statements are session-scoped (standard MySQL behavior). If the connection is closed, the prepared statement and its cached plan are lost. 3. Global Plan Cache: StarRocks also maintains an internal LRU Plan Cache in the FE for literal query matches. If you send the exact same SQL string repeatedly, the FE may reuse the plan. However, for a 640-line query, the slightest change in whitespace or parameters will cause a cache miss. _ _Note:* There is no persistent "Global Parameterized Plan Cache" (like SQL Server's Plan Cache) in 3.3.22 that survives across different sessions for non-prepared statements. 4. Recommendation: If your application uses a connection pool (keeping sessions alive), 
PREPARE
is your best option. If not, consider using SQL Plan Management (SPM) or Query Plan Advisor (v3.3+) to bind a baseline, though these are typically used for plan stability rather than purely for planning speed. Q2 — 
query_cache_entry_max_bytes
and Result Caching The variable 
query_cache_entry_max_bytes
(128MB in your case) belongs to the BE Query Cache, not a planning cache. 1. Does it reduce FE planning? No. The FE must still perform a full planning phase to determine which tablets are involved and generate the semantic keys required to probe the cache on the Backends. The 410ms planning cost will remain. 2. How to verify hits? You cannot verify Query Cache hits via the standard 
EXPLAIN
. You must use 
EXPLAIN ANALYZE
 and look for the 
CacheOperator
 metrics in the profile: * 
CacheProbeBytes
/ `CacheProbeRowNum`: Shows data retrieved from the cache (Hit). * `CachePopulateBytes`: Shows data added to the cache (Miss). * `CachePassthroughBytes`: Shows data that was too large to be cached (exceeding your 128MB limit). 3. What it actually caches: It caches the intermediate per-tablet aggregation results on the BEs. For your query with 30 
SUM(IF())
expressions, this is extremely beneficial for execution time because it avoids re-calculating those 30 expressions for tablets that haven't changed. Final Strategy to sub-500ms To fix the 410ms planning overhead specifically: * Force CTE Reuse: Large CTE trees can explode planning time. Try 
SET cbo_cte_reuse_rate = 0;
to force the optimizer to treat CTEs as a single unit rather than inlining them multiple times. * Prepared Statements: If your application logic allows, use 
PREPARE
to pin the plan within the session. * Limit Join Reordering: If your query has 10+ joins, the CBO may be searching too many permutations. You can try adjusting internal rule thresholds (though usually not recommended without a DBA). * Check Stats: High planning time can also be caused by the FE struggling to fetch/calculate statistics for an Iceberg table with millions of files. Ensure 
ANALYZE
is up to date to provide the optimizer with immediate metadata. References • sql-reference/System_variable.md
g
@Rocky Does StarRocks 3.3.22 SQL Plan Management (SPM) support binding a physical plan to a query so it survives across connections? We would bind the V3 query plan once and all future connections reuse it without CBO re-optimization.
Is there a global FE LRU plan cache for non-prepared statements that reuses plans based on query fingerprint (normalized SQL hash)? If yes, what is the cache size/TTL and how do we confirm hits?
Q2 — NVMe datacache pre-warming for specific partitions
The cluster is shared-data (CN-only) with NVMe local storage used as datacache. Cold queries hit S3 directly (~500MB/s), adding ~700ms over warm (which hits OS page cache). For customer_id=2 data specifically:
hourly_sales_poc: ~300MB (after date + lob filter in Parquet row groups)
fact_seller: ~120MB
fact_daily: ~50MB
If this ~470MB were pre-loaded into CN NVMe datacache, cold reads would be ~5GB/s instead of ~500MB/s → execution time drops from ~900ms to ~180ms cold.
Questions: (1) Does StarRocks 3.3.22 support a 
CACHE SELECT
or 
WARM UP CACHE
command to pre-populate datacache for specific table partitions without running a full query? (2) Is there a way to schedule cache pre-warming for customer_id=2 partitions at off-peak hours (e.g., midnight UTC) so the first business-hours query is "warm"? (3) What 
datacache_disk_path
configuration ensures NVMe is used (not S3 direct) and persists cache across CN pod restarts?
Q3 — S3 read parallelism: connector_max_split_size tuning for cold
For cold queries, more parallel S3 requests = better throughput. Current setting: 
connector_max_split_size=8MB
. hourly_sales for customer_id=2 + lob_id=2 + date≥2023-01-01 spans approximately 8-12 Parquet files × 128MB = ~1GB physical, but row group pruning reduces actual reads to ~300MB (38 splits at 8MB).
With 38 splits across 3 CN nodes and pipeline_dop=4, we issue ~50 parallel S3 requests. Questions: (1) What is the maximum concurrent S3 connection count per CN node in 3.3.22 (controlled by 
object_storage_max_connection
or similar)? (2) Would reducing 
connector_max_split_size
to 2MB (150 splits) increase S3 throughput by issuing more parallel HTTP range requests, or does it increase overhead from per-request latency (~50ms S3 GET initiation)? (3) Is there a session variable to set the per-request read-ahead size for Parquet column chunks independently of the split size?
Q4 — Iceberg manifest/metadata caching: how much of the 400ms FE planning is Glue/S3 metadata reads?
The query touches 5 Iceberg tables in the 
iceberg_adhoc
catalog (Glue on AWS). For each table, the FE must: list Glue partitions, read Iceberg manifest lists, read manifests, and compute partition pruning. For a table with 1000+ customer_id partitions, this can be expensive.
Questions: (1) Is there an FE-level Iceberg metadata cache (manifest cache) in 3.3.22? If so, what is its TTL and max size? (2) Is there a way to see how much of the 400ms FE planning time is metadata I/O (Glue API + S3 manifest reads) vs pure CPU optimization? (3) Would setting the Glue catalog's 
aws.glue.catalog_id
property with metadata caching hints reduce planning time on repeated queries?
Q5 — 
enable_datacache_io_adaptor
and cold query acceleration
Our cold query benchmark shows consistent 1761ms minimum on freshly connected queries with no prior cache. The CN NVMe datacache should theoretically cache data between queries. Questions: (1) After a warm query, is the hourly_sales data written to NVMe datacache? What confirms this — is 
CachePopulateBytes
visible in EXPLAIN ANALYZE? (2) Does 
enable_datacache_io_adaptor=true
in 3.3.22 exist and does it affect cold vs warm performance? (3) If datacache IS being populated after warm runs, why does the next "cold" query (fresh connection, 10s wait) still take 1761ms — is the page cache (RAM) being evicted but NVMe datacache retained?
r
Your 410ms planning bottleneck and 1761ms "cold" start times indicate that while your cluster is powerful, it is currently "fighting" the metadata and I/O initialization overheads inherent to data lakes. Here is the technical deep-dive and the settings to fix it in StarRocks 3.3.22. Q1 — SQL Plan Management (SPM) & Plan Caching 1. SPM Persistence: Yes, StarRocks 3.3.22 SQL Plan Management (SPM) supports binding a physical plan that survives across connections. By using 
CREATE BASELINE
, you bind a "Plan SQL" (which can include hints like 
/_+ SET_VAR(pipeline_dop=8) _/
) to a "Bind SQL." * StarRocks generates a fingerprint (normalized hash) for the Bind SQL. * Future queries with the same fingerprint (regardless of specific constant values like 
customer_id=2
vs 
customer_id=10
) will bypass the 410ms CBO optimization and use the cached physical plan. * To Verify: Run 
EXPLAIN <your_query>
. If successful, the output will show: 
Using baseline plan[id: 12345]
. 2. Global LRU Plan Cache: There is a global LRU plan cache for non-prepared statements (controlled by 
plan_cache_capacity
, default 1000). However, it is strict; even a minor change in the SQL string can cause a miss. For a 640-line query, SPM is far more reliable than the generic plan cache. Q2 — NVMe DataCache Pre-warming & Persistence 1. Warm-up Command: Use the 
CACHE SELECT
 syntax introduced in 3.3:
Copy code
sql
    CACHE SELECT * FROM iceberg_adhoc.db.hourly_sales_poc
    WHERE customer_id = 2 AND event_date >= '2024-01-01';
2. Scheduling: You can schedule this as a background task to run at midnight UTC:
Copy code
sql
    SUBMIT TASK prewarm_customer_2
    SCHEDULE EVERY (INTERVAL 1 DAY)
    AS CACHE SELECT * FROM ...;
3. Persistence & Path: In shared-data mode, the Data Cache (Block Cache) is stored in the directory defined by 
storage_root_path
 in 
be.conf
. * Crucial: Ensure this path points to your NVMe mount point. * The cache is persistent across CN pod restarts if the underlying NVMe volume is persistent (e.g., AWS EBS or a persistent LocalSource volume in K8s). If you use ephemeral "Instance Store," the cache is lost on pod deletion. Q3 — S3 Parallelism & Throughput Tuning 1. Max Connections: The BE/CN setting is 
object_storage_max_connection
. The default is often 20–50. For high-throughput NVMe nodes, raise this to 
500
 or 
1000
 to allow more concurrent S3 GET requests. 2. Split Size vs. Latency: Reducing 
connector_max_split_size
to 2MB (from 8MB) will increase splits from 38 to 150+. * Recommendation: If your S3 
IOTaskWaitTime
is high but 
IOTaskExecTime
is low, your bottleneck is "GET initiation" (TTFB). Stay at 8MB or 16MB. * If your 
IOTaskExecTime
is the bottleneck, lowering to 2MB or 4MB increases parallelism across more CPU cores, which helps if your CN nodes have many cores. 3. Read-Ahead: In 
be.conf
, tune 
io_coalesce_read_max_buffer_size
 (default 1MB) to increase the pre-fetch size for Parquet column chunks. Q4 — FE Iceberg/Glue Metadata Caching The 410ms planning time is likely 90% Metadata I/O (talking to Glue and reading Iceberg manifests from S3). 1. Manifest Cache: StarRocks 3.3.22 has a robust FE-level manifest cache. Ensure these FE configs are set: * 
iceberg_metadata_memory_cache_capacity
(Increase to 
2GB
or more if you have many tables). * 
iceberg_metadata_memory_cache_expiration_seconds
(Set to 
86400
for 24h retention). 2. Identify the Bottleneck: Run 
EXPLAIN ANALYZE
. Look at the 
Summary
section. * If 
Wait for Meta
 or 
Query Analysis
 is high, it's Glue/S3 manifest reading. * If 
CostBaseOptimize
 is high, it's the 640-line join reordering logic. 3. Glue Hint: Ensure the catalog uses 
enable_background_refresh_connector_metadata = true
. This allows StarRocks to refresh manifest lists in the background before your query arrives.
Q5 — The 1761ms Mystery If a "warm" query takes 180ms but a fresh connection 10s later takes 1761ms, the likely culprit is OS Page Cache (RAM) eviction, but there is a deeper StarRocks behavior to check: 1. RAM vs NVMe: 180ms is "Hot" (data is in RAM/PageCache). 1761ms is "Cold" (data is being pulled from S3). 2. The "Populate" Check: If the data were in the NVMe DataCache, the second query should take ~300-500ms (Disk Cache speed), not 1761ms. 3. Diagnostic: Check 
EXPLAIN ANALYZE
for 
DataCacheReadBytes
. * If 
DataCacheReadBytes
is 0 on the 1761ms run, the data never made it to the NVMe disk cache. * Reason: By default, StarRocks uses asynchronous population. The first query finishes before the background thread finishes writing the data to the NVMe. * The Fix: 
SET enable_datacache_async_populate_mode = false;
. This forces the first query to wait until the data is safely on the NVMe before finishing. Final Tuning Recommendation: 1. FE: 
CREATE BASELINE
(Fixes the 410ms planning). 2. FE: Increase 
iceberg_metadata_memory_cache_capacity
(Fixes Metadata lag). 3. CN: Set 
object_storage_max_connection = 500
. 4. Session: 
SET enable_datacache_async_populate_mode = false;
(Ensures your "warm" is actually written to NVMe). References • data_source/block_cache_warmup.md • using_starrocks/SQL_plan_manager.md • data_source/catalog/iceberg/iceberg_catalog.md • administration/management/BE_configuration.md • data_source/data_cache.md References • administration/management/BE_configuration.md • data_source/data_cache_troubleshooting.md • data_source/data_cache.md • using_starrocks/caching/block_cache.md
g
@Rocky Q1 — Non-blocking refresh behavior (critical)
In shared-data architecture with 
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
, when a full MV refresh is running (writing ~1.7GB of data from a 17M-row table to S3), can queries still read from the MV? Does StarRocks use snapshot isolation — queries read the last complete MV snapshot while the new one is being built — or is the MV temporarily unavailable/returns stale partial data during refresh?
Q2 — PREPARE/CTE on native MV tables
Our current PREPARE statement fails with `WITH`/CTE syntax on external Iceberg catalog queries. If we create ASYNC MVs as StarRocks native tables in 
default_catalog
, and the query uses those native tables (no 
set catalog iceberg_adhoc
needed), will PREPARE + EXECUTE work for a 640-line CTE query? Or is the CTE limitation in PREPARE a general SQL parser restriction regardless of table type?
Q3 — Triggering MV refresh after Spark job completes
The source Iceberg tables are fully overwritten by Spark every hour (INSERT OVERWRITE). Instead of a fixed 
EVERY (INTERVAL 1 HOUR)
refresh schedule that may not align with when Spark finishes, can we trigger the ASYNC MV refresh programmatically after the Spark DAG completes? Is there a 
REFRESH MATERIALIZED VIEW mv_name
SQL command we can call from Airflow after the Spark task finishes, and does this work non-blocking for concurrent queries?
Q4 — Storage confirmation for shared-data
Confirming: in StarRocks shared-data (disaggregated) architecture where 
storage_root_path
in 
cn.conf
points to S3, are ASYNC Materialized View data files stored exclusively in S3 (same as regular native tables)? There is NO persistent data written to CN local NVMe or FE servers — the NVMe is only used as a read cache (
datacache_disk_path
) and the FE only stores metadata? Confirming this before committing to the architecture since we can pay for S3 storage but not CN/FE local disk expansion.
r
In StarRocks 3.3.22 (shared-data architecture), you are moving toward a "Medallion Architecture" where the StarRocks native storage acts as your high-performance serving layer. Here are the technical answers to your implementation questions: Q1 — Non-blocking Refresh & Snapshot Isolation StarRocks asynchronous Materialized Views (MVs) provide Snapshot Isolation for queries during a refresh. * Behavior: When a refresh task is running (e.g., your 1.7GB/17M row update), concurrent queries will continue to read from the last successfully completed snapshot of the MV. * No Partial Data: Queries will never see "stale partial data" or intermediate results. The MV is only "swapped" to the new data version once the background write to S3 is fully committed and the metadata is updated in the FE. * Availability: The MV remains 100% available for reads throughout the refresh process. Q2 — PREPARE/CTE on Native MV Tables The 
PREPARE
/ 
EXECUTE
limitation you encountered with external Iceberg catalogs is often due to the FE's inability to "pin" external metadata (like Glue/S3 file lists) during the binding phase of a prepared statement. * Native Table Advantage: Moving your 640-line CTE query to run against native ASYNC MVs in the 
default_catalog
 will resolve this. Because native tables have their metadata (tablets, versions) managed directly by the StarRocks FE, the 
PREPARE
binder can successfully validate and cache the physical plan. * CTE Support: StarRocks 3.3 supports CTEs inside prepared statements for native tables. By querying the MV (a native table), you bypass the external metadata overhead entirely, allowing 
PREPARE
to work as intended. Q3 — Programmatic Refresh via Airflow/SQL You should absolutely trigger the refresh programmatically to align with your Spark pipeline. * Command: Use the following SQL in your Airflow task after the Spark job finishes:
Copy code
sql
    -- Use SYNC mode so Airflow waits for the refresh to actually finish
    REFRESH MATERIALIZED VIEW your_mv_name WITH SYNC MODE;
* Why SYNC MODE? By default, 
REFRESH
is asynchronous (it returns immediately while the task runs in the background). In an Airflow DAG, 
WITH SYNC MODE
ensures the SQL connection stays open until the refresh succeeds, allowing Airflow to correctly mark the task as "Success" or "Failed." * Non-blocking: Just like scheduled refreshes, this manual trigger is non-blocking for readers. Q4 — Shared-Data Storage Confirmation Your understanding of the architecture is 100% correct: * Persistent Data: All data for your Native Tables and ASYNC Materialized Views is stored exclusively in S3. Even if you drop every CN node in your cluster, your data remains safe and intact in the object store. * CN Local Storage: The 
storage_root_path
on your CN nodes (which you should point to your NVMe mount) is used only for the Data Cache (Block Cache). This is a "read-through" cache. If a CN pod restarts and the NVMe is wiped, the cluster simply re-fetches the required blocks from S3 on the next query. * FE Storage: The FE only stores the metadata (table schemas, MV definitions, and pointers to the S3 data files) in its local 
bdbje
logs. * Conclusion: You can scale your CN nodes (compute) and your S3 bucket (storage) independently without needing to worry about persistent local disk expansion on the nodes themselves. Summary Checklist for your DAG: 1. Spark Job: Overwrites Iceberg table. 2. Airflow Task: 
REFRESH MATERIALIZED VIEW mv_sales WITH SYNC MODE;
. 3. Result: Queries hit the MV via 
PREPARE
statements at <200ms execution time, reading from S3-backed data that is cached on CN NVMe. References • using_starrocks/async_mv/Materialized_view.md
g
@Rocky i have 2 tables, one is hourly_sales and other is hourly_customer_sales. hourly_customer_sales is aggregation on hourly_sales on customer_id level. now i have 2 types of queries. one is single customer / customer group example where customer_id in (2,3) and other is all customers query where we do not pass customer id in where condition. Now we have 2 types of queries. one is product level and other is customer level . so we need product level data then we go for hourly_sales n if customer level then hourly_customer_sales. considering this what would you suggest the iceberg properties. query 1 )
Copy code
SELECT
    hs.market_product_id,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', converted_revenue, 0)) AS revenue_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', converted_revenue, 0)) AS revenue_comparison,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', converted_revenue_tax_adjusted, 0)) AS revenue_tax_adj_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', converted_revenue_tax_adjusted, 0)) AS revenue_tax_adj_comparison,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', quantity, 0)) AS units_sold_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', quantity, 0)) AS units_sold_comparison,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', order_count, 0)) AS orders_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', order_count, 0)) AS orders_comparison,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', buyer_count, 0)) AS buyer_count_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', buyer_count, 0)) AS buyer_count_comparison,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', sns_order_count, 0)) AS sns_order_count_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', sns_order_count, 0)) AS sns_order_count_comparison,
    sum(if(hs.order_hour_utc >= '2024-01-01 07:00:00', order_items, 0)) AS order_items_current,
    sum(if(hs.order_hour_utc < '2024-01-01 07:00:00', order_items, 0)) AS order_items_comparison
FROM adhoc.hourly_sales_poc hs
    WHERE hs.customer_id = 2
        AND hs.lob_id = 2
        AND hs.order_hour_utc >= '2023-01-01 07:00:00'
        AND hs.order_hour_utc < '2025-01-01 07:00:00'
GROUP BY hs.market_product_id
query 2)
Copy code
SELECT
        hs.market_product_id,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.converted_revenue_usd, 0))             AS revenue_whole_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.converted_revenue_tax_adjusted_usd, 0)) AS revenue_tax_adj_whole_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.quantity, 0))                          AS units_sold_whole_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.order_count, 0))                       AS orders_whole_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.buyer_count, 0))                       AS buyer_count_whole_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.sns_order_count, 0))                   AS sns_order_count_whole_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.order_items, 0))                       AS order_items_whole_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.converted_revenue_usd, 0))             AS revenue_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2023-12-31 18:30:00' AND '2024-12-31 18:29:59', hs.converted_revenue_usd, 0))             AS revenue_comparison,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.converted_revenue_tax_adjusted_usd, 0)) AS revenue_tax_adj_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2023-12-31 18:30:00' AND '2024-12-31 18:29:59', hs.converted_revenue_tax_adjusted_usd, 0)) AS revenue_tax_adj_comparison,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.quantity, 0))                          AS units_sold_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2023-12-31 18:30:00' AND '2024-12-31 18:29:59', hs.quantity, 0))                          AS units_sold_comparison,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.order_count, 0))                       AS orders_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2023-12-31 18:30:00' AND '2024-12-31 18:29:59', hs.order_count, 0))                       AS orders_comparison,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.buyer_count, 0))                       AS buyer_count_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2023-12-31 18:30:00' AND '2024-12-31 18:29:59', hs.buyer_count, 0))                       AS buyer_count_comparison,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.sns_order_count, 0))                   AS sns_order_count_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2023-12-31 18:30:00' AND '2024-12-31 18:29:59', hs.sns_order_count, 0))                   AS sns_order_count_comparison,
        SUM(IF(hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59', hs.order_items, 0))                       AS order_items_current,
        SUM(IF(hs.order_hour_utc BETWEEN '2023-12-31 18:30:00' AND '2024-12-31 18:29:59', hs.order_items, 0))                       AS order_items_comparison
    FROM adhoc.hourly_sales_poc hs
    WHERE (
            hs.order_hour_utc BETWEEN '2024-12-31 18:30:00' AND '2025-12-31 18:29:59'
         OR hs.order_hour_utc BETWEEN '2023-12-31 18:30:00' AND '2024-12-31 18:29:59'
          )
      AND hs.lob_id NOT IN (1, 3, 158, 175, 176, 179, 236, 342, 320, 322, 331, 324, 314, 315, 318)
      AND hs.customer_state_id != 1
      AND hs.customer_type = 1
    GROUP BY hs.market_product_id
query 3 )
Copy code
SELECT
        hs.customer_id,
        NULL AS marketplace_names,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.converted_revenue_usd, 0))             AS revenue_whole_current,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.converted_revenue_tax_adjusted_usd, 0)) AS revenue_tax_adj_whole_current,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.quantity, 0))                          AS units_sold_whole_current,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.order_count, 0))                       AS orders_whole_current,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.buyer_count, 0))                       AS buyer_count_whole_current,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.sns_order_count, 0))                   AS sns_order_count_whole_current,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.order_items, 0))                       AS order_items_whole_current,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.converted_revenue_usd, 0))             AS revenue_current,
        sum(if(hs.order_hour_utc BETWEEN '2024-07-01 07:00:00' AND '2025-07-01 06:59:59', hs.converted_revenue_usd, 0))             AS revenue_comparison,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.converted_revenue_tax_adjusted_usd, 0)) AS revenue_tax_adj_current,
        sum(if(hs.order_hour_utc BETWEEN '2024-07-01 07:00:00' AND '2025-07-01 06:59:59', hs.converted_revenue_tax_adjusted_usd, 0)) AS revenue_tax_adj_comparison,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.quantity, 0))                          AS units_sold_current,
        sum(if(hs.order_hour_utc BETWEEN '2024-07-01 07:00:00' AND '2025-07-01 06:59:59', hs.quantity, 0))                          AS units_sold_comparison,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.order_count, 0))                       AS orders_current,
        sum(if(hs.order_hour_utc BETWEEN '2024-07-01 07:00:00' AND '2025-07-01 06:59:59', hs.order_count, 0))                       AS orders_comparison,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 00:00:00' AND '2026-06-28 23:59:59', hs.order_count, 0))                       AS orders_delayed_current,
        sum(if(hs.order_hour_utc BETWEEN '2024-07-01 00:00:00' AND '2025-06-28 23:59:59', hs.order_count, 0))                       AS orders_delayed_comparison,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.buyer_count, 0))                       AS buyer_count_current,
        sum(if(hs.order_hour_utc BETWEEN '2024-07-01 07:00:00' AND '2025-07-01 06:59:59', hs.buyer_count, 0))                       AS buyer_count_comparison,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.sns_order_count, 0))                   AS sns_order_count_current,
        sum(if(hs.order_hour_utc BETWEEN '2024-07-01 07:00:00' AND '2025-07-01 06:59:59', hs.sns_order_count, 0))                   AS sns_order_count_comparison,
        sum(if(hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59', hs.order_items, 0))                       AS order_items_current,
        sum(if(hs.order_hour_utc BETWEEN '2024-07-01 07:00:00' AND '2025-07-01 06:59:59', hs.order_items, 0))                       AS order_items_comparison
    FROM adhoc.hourly_customer_sales_poc hs
    WHERE (
            hs.order_hour_utc BETWEEN '2025-07-01 07:00:00' AND '2026-07-01 06:59:59'
         OR hs.order_hour_utc BETWEEN '2024-07-01 07:00:00' AND '2025-07-01 06:59:59'
         OR hs.order_hour_utc BETWEEN '2025-07-01 00:00:00' AND '2026-06-28 23:59:59'
         OR hs.order_hour_utc BETWEEN '2024-07-01 00:00:00' AND '2025-06-28 23:59:59'
          )
      AND hs.marketplace_id IN (0, 835, 58, 60, 57, 912, 59, 61, 780, 781, 841, 56, 115, 87, 853, 879, 906, 834, 112, 914, 901, 881, 892, 910, 907, 887, 917, 888, 915, 886, 891, 911, 913, 916)
      AND (hs.lob_id != 1 AND hs.lob_id != 3)
      AND (hs.lob_id NOT IN (158, 175, 176, 179, 236, 342, 320, 322, 331, 324, 314, 315, 318, 1, 3))
      AND hs.customer_state_id != 1
      AND hs.customer_type = 1
    GROUP BY hs.customer_id
r
To optimize StarRocks performance for these specific query patterns (point-lookups by 
customer_id
vs. wide range scans), you should configure your Iceberg tables with a mix of Hidden Partitioning and Clustered Sorting. Here are the recommended Iceberg properties and table designs for your two tables: 1. Table: 
hourly_sales_poc
(Product Level) This table must satisfy Query 1 (high-frequency point lookup for a specific customer) and Query 2 (large scan for all customers). * Partitioning: Use 
days(order_hour_utc)
. _ _Why:* All queries filter on time. Partitioning by day allows the StarRocks FE to prune thousands of files instantly. * Sorting (Clustering): Sort by 
customer_id, lob_id, order_hour_utc
. _ _Property:* 
write.sort-order = 'customer_id ASC NULLS FIRST, lob_id ASC NULLS FIRST, order_hour_utc ASC NULLS FIRST'
_ _Why:* By making 
customer_id
the leading sort column, Query 1 becomes a "Prefix Index" seek. StarRocks/Iceberg will only read the small range of Parquet pages containing that specific customer's data within each daily partition. * Distribution Mode: Set 
write.distribution-mode = 'hash'
on 
customer_id
. _ _Why:* This ensures that during the Spark write, all data for one customer lands in the same file (or set of files), preventing the "small file problem" for Query 1. Iceberg Table Properties:
Copy code
sql
'write.format.default'='parquet',
'write.metadata.compression-codec'='gzip',
'write.parquet.compression-codec'='zstd',
'write.parquet.bloom-filter-enabled.column'='customer_id,market_product_id',
'write.sort-order'='customer_id ASC, lob_id ASC, order_hour_utc ASC',
'write.distribution-mode'='hash'
────────── 2. Table: 
hourly_customer_sales_poc
(Customer Level) This table supports Query 3, which filters by 
marketplace_id
and aggregates by 
customer_id
. * Partitioning: Use 
days(order_hour_utc)
. * Sorting (Clustering): Sort by 
marketplace_id, customer_id
. _ _Property:* 
write.sort-order = 'marketplace_id ASC, customer_id ASC'
_ _Why:* Query 3 uses 
marketplace_id IN (...)
. Sorting by this column first allows Parquet's "Zone Maps" (min/max stats) to skip entire row groups that don't match the marketplace IDs in your 34-item list. * Bloom Filters: Enable Bloom Filters on 
customer_id
. _ _Why:* Even though you group by it, if you ever add a specific 
customer_id
filter to Query 3, the Bloom filter will allow a "negative hit" check without reading the data pages. ────────── 3. StarRocks-Side Optimizations Since you are using a 640-line query with 30+ 
SUM(IF())
expressions, the following StarRocks settings are critical to handle these specific SQL patterns: A. Enable Global Dict for 
SUM(IF())
 The 
SUM(IF(...))
pattern is CPU-intensive. If 
customer_type
or 
lob_id
are low-cardinality strings, StarRocks can use Low-Cardinality Optimization. * Ensure these columns are 
STRING
or 
VARCHAR
. B. Data Cache Tuning (for Query 2 & 3) Query 2 and 3 scan data for "all customers." This will likely evict Query 1's "hot" data from the NVMe cache unless managed. * Recommendation: Use Data Cache Partial Caching or set 
set session enable_scan_datacache = true
only for the high-priority "Customer Dashboard" queries (Query 1). C. Tackle the "OR" in Range Filters Queries 2 and 3 use multiple 
OR
conditions for 
order_hour_utc
. * Optimization: StarRocks 3.3+ is excellent at "Range Extraction." However, ensure your Iceberg catalog has 
enable_iceberg_metadata_cache = true
. Without this, the FE will spend 200ms+ just reading the manifests to figure out which daily partitions to prune for those 4 OR-ed date ranges. D. Materialized View Strategy (The "Gold" Layer) If Query 2 (All customers, product level) is still taking >2 seconds due to the 640-line complexity: * Create an Asynchronous Materialized View inside StarRocks that pre-aggregates the 
SUM(IF())
logic into columns.
* Example: Create an MV that has columns like 
revenue_2024
, 
revenue_2023
, etc. * StarRocks will then perform Query Rewrite. You send the 640-line query, and the optimizer automatically redirects it to the pre-calculated MV, reducing the 1.7GB S3 scan to a ~10MB local NVMe scan. Recommended Configuration Summary Feature | 
hourly_sales
(Product) | 
hourly_customer_sales
(Customer) Partitioning | 
days(order_hour_utc)
| 
days(order_hour_utc)
Sort Key | 
customer_id, lob_id
| 
marketplace_id, customer_id
Bloom Filter | 
customer_id
, 
market_product_id
| 
customer_id
Write Dist | 
hash(customer_id)
| 
hash(marketplace_id)
SR Cache | Data Cache enabled | Data Cache enabled References • best_practices/partitioning.md • best_practices/table_clustering.md • best_practices/query_tuning/schema_tuning.md
g
@Rocky currents DDLs - DDLS - 1) hourly sales -
Copy code
PARTITIONED BY (customer_id)
TBLPROPERTIES (
  'write.format.default' = 'parquet',
  'write.parquet.compression-codec' = 'zstd',
  'write.target-file-size-bytes' = '134217728',
  'write.parquet.row-group-size-bytes' = '4194304',
  'write.distribution-mode' = 'range',
  'write.metadata.metrics.default' = 'truncate(16)',
  'write.metadata.metrics.column.customer_id' = 'full',
  'write.metadata.metrics.column.marketplace_id' = 'full',
  'write.metadata.metrics.column.market_product_id' = 'full',
  'write.metadata.metrics.column.lob_id' = 'full',
  'write.metadata.metrics.column.order_hour_utc' = 'full',
  'write.metadata.metrics.column.order_date_utc' = 'full'
);

ALTER TABLE adhoc.hourly_sales WRITE ORDERED BY customer_id ASC, lob_id ASC, order_hour_utc ASC
2) PARTITIONED BY (customer_id, lob_id) TBLPROPERTIES ( 'write.format.default' = 'parquet', 'write.parquet.compression-codec' = 'zstd', 'write.target-file-size-bytes' = '16777216', 'write.parquet.row-group-size-bytes' = '4194304', 'write.distribution-mode' = 'range', 'write.metadata.metrics.default' = 'truncate(16)', 'write.metadata.metrics.column.customer_id' = 'full', 'write.metadata.metrics.column.marketplace_id' = 'full', 'write.metadata.metrics.column.lob_id' = 'full', 'write.metadata.metrics.column.order_hour_utc' = 'full', 'write.metadata.metrics.column.order_date_utc' = 'full' ); ALTER TABLE adhoc.hourly_customer_sales WRITE ORDERED BY customer_id ASC, lob_id ASC, order_hour_utc ASC
r
Your current DDLs contain a "Performance Anti-Pattern" for StarRocks (and data lakes in general) that explains exactly why your FE planning is 400ms+ and your cold queries take 1761ms. The Problem: Over-Partitioning by 
customer_id
 You are currently physically partitioning by 
customer_id
. 1. Metadata Bloat: If you have 1,000+ customers, Iceberg has to track 1,000+ separate directories and manifest entries. Every time you run a query, the StarRocks FE must communicate with Glue/S3 to prune these 1,000+ partitions. This is the source of your 400ms FE lag. 2. The "All-Customer" Scan Penalty (Query 2 & 3): When a query does not filter by 
customer_id
, StarRocks is forced to open thousands of small files across thousands of directories. This destroys I/O throughput and prevents the NVMe cache from being used efficiently. 3. Small Files: Your 
hourly_customer_sales
has a 
target-file-size-bytes
of only 16MB. This creates a "death by a thousand cuts" for S3 GET requests. The Solution: Hidden Partitioning + Sorting You should move 
customer_id
from the Partition Key to the Sort Key and use Time-based Partitioning. This gives you the "best of both worlds": fast point-lookups for one customer and high-throughput scans for all customers. Revised DDL 1: 
hourly_sales
(Product Level) * Change: Partition by Day, Sort by Customer. * Benefit: Query 1 (single customer) will still be instant because 
customer_id
is the first sort column (StarRocks will only read the relevant chunks). Query 2 (all customers) will be 10x faster because it only has to scan a few large files per day.
Copy code
sql
-- Partition by Date, NOT Customer
PARTITIONED BY (days(order_hour_utc)) 
TBLPROPERTIES (
  'write.format.default' = 'parquet',
  'write.parquet.compression-codec' = 'zstd',
  'write.target-file-size-bytes' = '536870912',      -- Increase to 512MB for better S3 throughput
  'write.parquet.row-group-size-bytes' = '134217728', -- 128MB Row Groups are optimal for StarRocks
  'write.distribution-mode' = 'hash',                -- Hash by customer_id to keep data clustered
  'write.metadata.metrics.default' = 'full'          -- Full metrics for all columns is better for SR pruning
);

-- Keep this! This makes Query 1 fast without the metadata overhead of partitioning
ALTER TABLE adhoc.hourly_sales WRITE ORDERED BY customer_id ASC, lob_id ASC, order_hour_utc ASC;
Revised DDL 2: 
hourly_customer_sales
 * Critical Change: Remove 
customer_id
and 
lob_id
from the physical partition list. * Why: Query 3 filters by 
marketplace_id
. In your current DDL, because it's partitioned by 
customer_id
, StarRocks cannot prune anything and must scan the entire table.
Copy code
sql
PARTITIONED BY (days(order_hour_utc)) 
TBLPROPERTIES (
  'write.format.default' = 'parquet',
  'write.parquet.compression-codec' = 'zstd',
  'write.target-file-size-bytes' = '134217728',      -- Increase to 128MB
  'write.parquet.row-group-size-bytes' = '67108864',  -- 64MB Row Groups
  'write.distribution-mode' = 'hash'
);

-- Swap sorting order: marketplace_id first for Query 3 performance
ALTER TABLE adhoc.hourly_customer_sales WRITE ORDERED BY marketplace_id ASC, customer_id ASC, order_hour_utc ASC;
Why these changes fix your performance: 1. FE Planning (400ms → 50ms): Instead of checking 1,000+ 
customer_id
partitions in Glue, the FE only checks ~365 
days
partitions for a year of data. 2. S3 Read Efficiency: By increasing 
target-file-size
and 
row-group-size
, each S3 GET request fetches a meaningful amount of data. Your current 16MB files are likely why the 8MB 
connector_max_split_size
feels slow (you're only getting 2 splits per file).
3. NVMe DataCache Hit Rate: In your current "partition-by-customer" setup, the data for 
customer_id=2
is scattered across many tiny files. With the new setup, all data for 
customer_id=2
for a specific day is stored contiguously in one part of one file, making it much easier for the NVMe cache to "warm up" and stay warm. Summary Checklist: * [ ] Switch to 
days(order_hour_utc)
 as the only partition column. * [ ] Increase 
row-group-size-bytes
 to at least 64MB or 128MB. * [ ] Use 
write.distribution-mode = 'hash'
 on the column you most frequently filter by (e.g., 
customer_id
). * [ ] Set 
write.metadata.metrics.default = 'full'
 to ensure StarRocks can use min/max pruning on every column in your complex WHERE. Applying these will likely drop your FE planning time by 80% and your cold query time by 50-60%.
g
@Rocky
Copy code
SELECT
    hs.market_product_id
FROM adhoc.hourly_sales_poc hs
    WHERE hs.customer_id = 2
        AND hs.lob_id = 2
GROUP BY hs.market_product_id
limit 10;
When i'm querying from superset to iceberg table its giving output but not from starRocks. What could be the reason? error -
Copy code
Query 1 ERROR at Line 1: : FileReader::get_next failed. reason = Invalid argument: ZSTD compress failed: Src size is incorrect
be/src/formats/parquet/column_chunk_reader.cpp:174 _compress_codec->decompress(read_data, &_data)
be/src/formats/parquet/column_chunk_reader.cpp:193 _read_and_decompress_page_data(compressed_size, uncompressed_size, true)
be/src/formats/parquet/column_chunk_reader.cpp:132 _parse_data_page()
be/src/formats/parquet/stored_column_reader.cpp:379 _reader->load_page()
be/src/formats/parquet/stored_column_reader.cp
Or
Copy code
couldn't deserialize thrift msg: TProtocolException: Invalid data: file = <s3://pattern-dl/adhoc/hourly_sales_poc/data/customer_id=10/00100-1277-93c7528b-9908-443d-bc90-a8952b7c5c83-0-00001.parquet>: BE:10101
r
These errors (
ZSTD compress failed: Src size is incorrect
and 
TProtocolException: Invalid data
) indicate that the StarRocks Parquet reader is encountering corrupted or improperly formatted metadata in your Iceberg files. Since the query works in Superset but fails in StarRocks, it is likely that StarRocks is struggling with specific Parquet settings in your DDL. Specifically, your 4MB row-group size is the most likely culprit. Why this is happening 1. Non-standard Row Groups: A 
row-group-size
of 4MB is extremely small for Parquet. This forces the writer to create a massive amount of metadata (headers, footers, and page indexes) relative to the actual data. 2. Thrift Deserialization Failure: The 
TProtocolException: Invalid data
error means the Parquet "footer" (which is Thrift-encoded) is either truncated or misaligned. When combined with ZSTD and tiny row groups, Spark sometimes writes footers that some C++ Parquet readers (like StarRocks') find difficult to parse if they don't perfectly align with buffer expectations. 3. Decompression Mismatch: The 
Src size is incorrect
error happens when the StarRocks decompressor expects 
X
bytes based on the metadata, but the actual compressed stream in the file provides a different amount. This often occurs if the file is slightly truncated or if there is a mismatch in how ZSTD "frames" are handled in very small row groups. Immediate Workarounds (Session level) To confirm if this is a reader optimization issue, try running these commands in your session before the query:
Copy code
sql
-- Disable the optimized Parquet reader to use the standard one
SET enable_fast_parquet_read = false;

-- If it still fails, try disabling vectorized decompression
SET enable_parquet_vectorized_reader = false;
If the query works after setting these, it confirms a bug or limitation in StarRocks' high-performance C++ reader when handling your specific Parquet format. The Permanent Fix (Recommended) You should update your table properties to align with StarRocks/Iceberg best practices. Small row groups and over-partitioning are likely causing the "truncated" metadata behavior. 1. Increase Row Group Size Change 
write.parquet.row-group-size-bytes
from 4MB to 128MB.
Copy code
sql
ALTER TABLE adhoc.hourly_sales_poc SET TBLPROPERTIES (
  'write.parquet.row-group-size-bytes' = '134217728',
  'write.target-file-size-bytes' = '536870912'
);
2. Reduce Metadata Complexity You are currently partitioning by 
customer_id
. As discussed earlier, this creates thousands of tiny files. If a Spark job is interrupted while writing one of these thousands of files, you end up with a truncated file that causes the 
TProtocolException
. * Recommendation: Move 
customer_id
from the 
PARTITIONED BY
clause to the 
ORDERED BY
clause (Sort Key). * Partition by: 
days(order_hour_utc)
. 3. Verify File Integrity The error message points to a specific file: 
<s3://pattern-dl/adhoc/hourly_sales_poc/data/customer_id=10/00100-1277-93c7528b-9908-443d-bc90-a8952b7c5c83-0-00001.parquet>
Check the size of this file in S3. If it is very small (e.g., < 1KB), it might be a "zombie" file created by a failed Spark task that Iceberg hasn't cleaned up yet. Summary Checklist 1. Try 
SET enable_fast_parquet_read = false;
. 2. Check if the specific file in the error message is valid (not 0 bytes or truncated). 3. Re-write the data using 128MB row groups and Day-based partitioning. This will eliminate the metadata bloat that is currently breaking the Thrift deserialization.
g
@Rocky current hourly_sales properties -
Copy code
) USING ICEBERG
PARTITIONED BY (customer_id)
TBLPROPERTIES (
  'write.format.default' = 'parquet',
  'write.parquet.compression-codec' = 'zstd',
  'write.target-file-size-bytes' = '134217728',
  'write.parquet.row-group-size-bytes' = '4194304',
  'write.distribution-mode' = 'range',
  'write.metadata.metrics.default' = 'truncate(16)',
  'write.metadata.metrics.column.customer_id' = 'full',
  'write.metadata.metrics.column.marketplace_id' = 'full',
  'write.metadata.metrics.column.market_product_id' = 'full',
  'write.metadata.metrics.column.lob_id' = 'full',
  'write.metadata.metrics.column.order_hour_utc' = 'full',
  'write.metadata.metrics.column.order_date_utc' = 'full',
);
What could be the issue?
r
The error you are seeing—specifically 
ZSTD compress failed: Src size is incorrect
and `TProtocolException: Invalid data`—is a strong indicator of a mismatch between how the data was written and how the StarRocks C++ reader is attempting to decompress it. The root cause is almost certainly your 4MB Row Group Size combined with High-Cardinality Partitioning. Why this is happening: 1. 4MB Row Group Size is too small: Parquet is designed for large row groups (typically 128MB or 256MB). At 4MB, you are creating a massive amount of metadata overhead. For every 4MB of data, Spark has to write a row group header and footer. 2. The "ZSTD Src Size" Error: This is a low-level error from the ZSTD decompressor. It happens because StarRocks' native C++ reader expects a certain block size based on the Parquet metadata, but the actual data chunk in the file doesn't match. This often happens with tiny row groups where page boundaries and compression frames get misaligned or truncated during the write process. 3. Why Superset works: Superset (or the engine behind it, like Trino/Presto/Hive) uses a Java-based Parquet reader. Java readers are generally slower but much more "lenient"—they often have better fallback logic for non-standard or slightly malformed Parquet files. StarRocks' reader is written in C++ for extreme performance (SIMD/Vectorization) and is much stricter about Parquet specifications. How to Fix It: 1. Immediate Workaround (Query from StarRocks) Run these commands in your session before your query. This will force StarRocks to use a more conservative reading path, which often bypasses decompression errors caused by tiny row groups:
Copy code
sql
-- Disable the high-performance C++ reader optimizations
SET enable_fast_parquet_read = false;

-- If it still fails, disable vectorized decompression
SET enable_parquet_vectorized_reader = false;
2. The Permanent Fix (DDL Changes) You need to re-create or 
ALTER
the table with standard Parquet settings. A 4MB row group is considered an "anti-pattern" for analytical databases. Change your properties to these values:
Copy code
sql
ALTER TABLE adhoc.hourly_sales_poc SET TBLPROPERTIES (
  'write.parquet.row-group-size-bytes' = '134217728', -- 128MB (Standard)
  'write.target-file-size-bytes' = '536870912',      -- 512MB
  'write.metadata.metrics.default' = 'full'          -- Avoid 'truncate' if possible
);
3. Stop Partitioning by 
customer_id
 Your DDL shows 
PARTITIONED BY (customer_id)
. * If you have thousands of customers, you are creating thousands of directories. * In Spark, this often leads to "task thrashing" where files are closed prematurely or metadata is not committed correctly, leading to the 
TProtocolException
(corrupt footer) you saw. * Recommendation: Partition by 
days(order_hour_utc)
and use 
WRITE ORDERED BY customer_id
. This keeps the data for one customer together inside a large, healthy Parquet file rather than splitting it into thousands of tiny, fragile ones. Summary of the "Corrupt" File The error 
couldn't deserialize thrift msg
on the file 
.../customer_id=10/00100-...parquet
means that specific file is physically malformed (likely truncated). Even if you fix the DDL, you should delete and re-write the data for customer_id=10, as that specific file's footer is unreadable by any strict Parquet-strict reader. References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/deltalake_catalog.md • faq/Others.md • data_source/External_table.md • quick_start/iceberg.md
g
@Rocky error -
Copy code
Query 1 ERROR at Line 3: : couldn't deserialize thrift msg:
TProtocolException: Invalid data: file = <s3://pattern-dl/adhoc/hourly_sales_poc/data/customer_id=2/00025-1196-de70c706-3e22-4f56-ae87-4dc2cdc3e7d5-0-00001.parquet>: BE:10101
DDL -
Copy code
) USING ICEBERG
PARTITIONED BY (customer_id)
TBLPROPERTIES (
  'write.format.default' = 'parquet',
  'write.parquet.compression-codec' = 'zstd',
  'write.target-file-size-bytes' = '134217728',
  'write.parquet.row-group-size-bytes' = '4194304',
  'write.distribution-mode' = 'range',
  'write.metadata.metrics.default' = 'truncate(16)',
  'write.metadata.metrics.column.customer_id' = 'full',
  'write.metadata.metrics.column.marketplace_id' = 'full',
  'write.metadata.metrics.column.market_product_id' = 'full',
  'write.metadata.metrics.column.lob_id' = 'full',
  'write.metadata.metrics.column.order_hour_utc' = 'full',
  'write.metadata.metrics.column.order_date_utc' = 'full'
);
query -
Copy code
SELECT
    hs.market_product_id
FROM adhoc.hourly_sales_poc hs
    WHERE hs.customer_id = 2
        AND hs.lob_id = 2
GROUP BY hs.market_product_id
limit 10;
superset is working but starRocks is giving above error
r
The errors you are seeing (
TProtocolException: Invalid data
and 
ZSTD compress failed: Src size is incorrect
) indicate that StarRocks' high-performance C++ Parquet reader is failing to parse or decompress the files. Since it works in Superset (which uses a Java-based reader), this confirms a compatibility issue between the StarRocks native reader and the specific Parquet layout produced by your DDL. The Root Cause The primary culprit is your 4MB Row Group Size (
write.parquet.row-group-size-bytes
). 1. Metadata Bloat: At 4MB, you are creating a massive amount of metadata (Thrift-encoded footers and Page Indexes) compared to actual data. StarRocks' native reader is optimized for large, standard row groups (128MB+). Tiny row groups create thousands of small compression blocks. 2. ZSTD Frame Issues: The 
Src size is incorrect
error is a low-level Zstandard failure. It happens when the StarRocks decompressor receives a data chunk whose size doesn't match the frame header. This often occurs when tiny row groups lead to non-standard block boundaries that the vectorized C++ reader struggles to align. 3. Strictness vs. Leniency: Java readers (used by Superset/Trino) are generally more lenient and have better fallback logic for non-standard Parquet files. The StarRocks C++ reader is built for extreme speed (SIMD/vectorized) and is much stricter about the Parquet specification and footer structure. ────────── Phase 1: Immediate Workarounds (Session Level) To get your query working immediately, try disabling the optimized reader paths. Run these commands in your session before the query:
Copy code
sql
-- Use the standard reader path (more stable, slightly slower)
SET enable_fast_parquet_read = false;

-- Disable vectorized decompression if the error persists
SET enable_parquet_vectorized_reader = false;

-- Disable the Page Index if it's still failing
SET enable_parquet_page_index = false;
If the query works after these settings, it confirms that the optimized reader is tripping over your file structure. ────────── Phase 2: Permanent Fix (DDL Correction) Your current DDL contains several "Data Lake Anti-Patterns" for StarRocks. You should update your table properties and rewrite the data: 1. Increase Row Group Size: Change from 4MB to 128MB. This is the single most important fix for Parquet health. 2. Stop Partitioning by `customer_id`: Partitioning physically by a high-cardinality ID creates thousands of small files and metadata objects. Instead, partition by time and use sorting for customer lookups. 3. Simplify Metrics: 
truncate(16)
on all metrics can sometimes create unexpected behavior in the footer statistics. Recommended DDL Properties:
Copy code
sql
ALTER TABLE adhoc.hourly_sales_poc SET TBLPROPERTIES (
  'write.parquet.row-group-size-bytes' = '134217728', -- 128MB
  'write.target-file-size-bytes' = '536870912',      -- 512MB
  'write.metadata.metrics.default' = 'full'
);

-- Re-structure the table to avoid the "Small File" problem
-- Replace: PARTITIONED BY (customer_id)
-- With:    PARTITIONED BY (days(order_hour_utc))
-- And:     ALTER TABLE ... WRITE ORDERED BY customer_id, lob_id;
Summary Checklist * [ ] Test 
SET enable_fast_parquet_read = false;
 to bypass the error immediately. * [ ] Check the file on S3: Verify that the file 
.../customer_id=2/00025...parquet
isn't 0 bytes or significantly smaller than expected (which would indicate a truncated file). * [ ] Re-ingest data with 128MB row groups to ensure long-term stability and performance. References • table_design/data_compression.md • developers/build-starrocks/Build_in_docker.md • knowledge/usage/best-practice.md • faq/Others.md
g
@Rocky it was working since last 1 month and giving error since morning
r
If the query was working for a month and only started failing this morning, it strongly suggests that a specific file written recently is physically malformed or truncated. While your DDL settings (4MB row groups) are inefficient, the fact that it worked previously means the reader can handle them under normal conditions. The sudden failure points to one of the following events occurring this morning: 1. A Truncated or "Zombie" File The error 
TProtocolException: Invalid data
specifically means the StarRocks reader tried to read the Parquet "footer" (the metadata at the end of the file) and found it was missing or incomplete. * Why Superset works: Superset/Trino uses a Java reader that often ignores minor footer errors or can recover from them. StarRocks' C++ reader is much stricter for performance reasons. * Action: Check the specific file mentioned in your error: 
<s3://pattern-dl/adhoc/hourly_sales_poc/data/customer_id=2/00025-1196-de70c706-3e22-4f56-ae87-4dc2cdc3e7d5-0-00001.parquet>
If this file is very small (e.g., only a few KB) or was written during a job that crashed/timed out this morning, it is likely corrupt. 2. Complexity Limit reached With a 
4MB
row-group size, as your data grows, the Parquet footer (which contains statistics for every column in every row group) grows as well. * The "Morning" trigger: You may have crossed a threshold this morning where a specific file now has so many row groups that its metadata footer exceeds the internal buffer limits of the StarRocks Thrift deserializer. * Action: Try disabling the optimized reader for this session to see if it bypasses the limit:
Copy code
sql
    SET enable_fast_parquet_read = false;
    SELECT ... -- run your query again
3. ZSTD Version Mismatch If the Spark job or the environment writing the data was updated this morning, it might be using a newer version of ZSTD or different "frame" settings that the StarRocks BE version is struggling to decompress. The 
Src size is incorrect
error is a classic sign of a decompression frame mismatch. Recommended Steps to Fix 1. Isolate the bad file: Move the file mentioned in the error out of the S3 path and see if the query works. If it does, that file was definitely corrupt. 2. Refresh Metadata: Sometimes StarRocks' metadata cache for Iceberg gets out of sync with actual file lengths on S3.
Copy code
sql
    REFRESH EXTERNAL TABLE adhoc.hourly_sales_poc;
3. Fix the DDL (Long-term): Even if you fix the corrupt file, change 
write.parquet.row-group-size-bytes
to 
134217728
(128MB). Using 4MB row groups is extremely risky for Parquet health and is likely what caused the write to fail or the footer to become too complex in the first place. 4. Check for Partial Writes: Ensure your Spark jobs are committing via the Iceberg API and not just "dropping files" into S3, which can leave partial/unreadable files behind.
g
@Rocky New error - Query 1 ERROR at Line 44: : Plan inconsistency. scan_range.partition_id = 0 not found in partition description map: BE:10079
r
The error 
Plan inconsistency. scan_range.partition_id = 0 not found in partition description map
is a classic metadata staleness error. It occurs when the StarRocks Frontend (FE) and Backend (BE) become out of sync regarding the table's structure or the specific locations of data files. In your case, because you were dealing with corrupt Parquet files and likely modified the underlying S3 data or the Iceberg table recently, the StarRocks metadata cache still holds "stale" references to partitions or files that have changed. How to Fix 1. Force a Metadata Refresh (Most Likely Fix) StarRocks caches Iceberg snapshots and partition info to improve performance. You must manually clear this cache so StarRocks can see the "new" healthy state of your table:
Copy code
sql
-- Refresh the metadata for this specific table
REFRESH EXTERNAL TABLE adhoc.hourly_sales_poc;
If you are using a Catalog (e.g., 
iceberg_catalog
), you can also refresh the entire catalog, though refreshing the table is usually enough:
Copy code
sql
REFRESH CATALOG <your_catalog_name>;
2. Check for "Ghost" Partitions The 
partition_id = 0
error often appears if you recently changed the partitioning scheme or deleted the table and recreated it with the same name. * If you dropped and recreated the table in Iceberg (Spark), StarRocks FE might still be trying to use the old "table ID" or "schema version" from its internal cache. * The 
REFRESH
command above should solve this. 3. Clear the BE Cache (If Refresh Fails) StarRocks Backends (BEs) also cache Parquet footers and file metadata. If the 
REFRESH
command doesn't work, you can force the BEs to drop their file caches by running:
Copy code
sql
-- This clears the file metadata cache on all BE nodes
ADMIN SET FRONTEND CONFIG ("clear_external_table_metadata_cache" = "true");
(Note: Use this sparingly as it will cause a slight performance dip while the cache refills.) Why this happened today Since you mentioned it was working for a month and started failing this morning: 1. Job Failure: A Spark/Iceberg job might have failed halfway through, leaving the Iceberg metadata in a state where StarRocks FE sees a partition, but the BE cannot find the corresponding file description. 2. Manual File Deletion: If any files were manually deleted from S3 without updating the Iceberg manifest, StarRocks will hit this inconsistency. 3. Schema Evolution: If you changed the partition column or added/dropped columns this morning, the FE's plan might be using an old version of the partition map. Summary: Run 
REFRESH EXTERNAL TABLE adhoc.hourly_sales_poc;
and then try your query again. If you changed any 
SET
variables in your previous session (like 
enable_fast_parquet_read
), keep them at their default values for now to see if the refresh fixed the core issue. References • administration/management/BE_configuration.md
Open in Slack
PreviousNext
Powered by