@Rocky We’re querying a parquet table with billions of rows stored in S3 using an external table. When we query it via Athena we achieve five-six times better performance than using the same StarRocks CN nodes which are also significantly larger machines. How can we further improve this performance?

To improve query performance on S3 Parquet tables and match or exceed Athena's speed, you should focus on four main areas: Data Caching, Metadata Management, Query Parallelism, and Parquet-specific optimizations. 1. Enable Data Cache (Local Disk Cache) The most significant performance boost for S3 queries comes from bypassing remote I/O. StarRocks can cache data blocks from S3 onto the local NVMe/SSD storage of your CN nodes. * How it helps: Repeated queries or scans on hot data will hit local disks instead of S3, significantly reducing latency. * Action: Ensure your CN nodes have local storage configured and enable the cache in your session or globally:

sql
    SET enable_scan_datacache = true;

* Note: In versions 3.3+, this is often enabled by default if the

storage_root_path

is set in the CN configuration. Check your

cn.conf

to ensure disk space is allocated for cache. 2. Use External Catalogs instead of "External Tables" If you are using the legacy

CREATE EXTERNAL TABLE

syntax, switch to External Catalogs (e.g., Hive or Glue Catalog). * Why: Catalogs support advanced metadata caching, partition pruning, and statistics that are critical for billion-row datasets. * Metadata Cache: StarRocks caches the file list and partition locations from the Metastore. Ensure background refresh is enabled to keep this metadata fresh without slowing down the first query: * Set

enable_background_refresh_connector_metadata = true

in FE configuration. 3. Optimize Parquet Scanning StarRocks has specific optimizations for Parquet that might not be fully utilized by default: * Late Materialization: This avoids reading all columns until after filters are applied. * Check BE config:

parquet_late_materialization_enable = true

(default is usually true). * Columnar Scan Parallelism: Increase the number of I/O threads for scanning remote files if your network bandwidth allows:

sql
    -- Example session variable to increase parallelism
    SET pipeline_dop = 0; -- 0 allows StarRocks to auto-calculate based on cores

4. Verify Predicate Pushdown & Partition Pruning Athena is highly efficient at partition pruning. If StarRocks is scanning too much data, your execution plan might be suboptimal. * Check the Plan: Run

EXPLAIN VERBOSE <your_query>

and look at the

ScanNode

. * Partition Pruning: Ensure

partitions

shows a subset (e.g.,

partitions=5/1000

) rather than a full scan. * Runtime Filters: For joins, ensure StarRocks is pushing filters down to the S3 scan level. You can see this in the profile as

PushdownPredicates

. 5. Address the "Small File" Problem Athena handles massive amounts of small files better than most MPP engines because of its serverless scaling. * Optimization: If your S3 data consists of millions of tiny Parquet files (e.g., < 128MB), StarRocks (and any MPP engine) will struggle with metadata overhead and S3 API call latency. * Solution: Use a compaction process or StarRocks'

INSERT INTO ... SELECT

to rewrite the data into larger Parquet files (256MB–1GB) if possible. Summary Checklist Feature | Setting / Action Data Cache |

SET enable_scan_datacache = true;

Parallelism |

SET pipeline_dop = <number_of_cores>;

Metadata | Use

Hive/Glue Catalog

instead of legacy External Tables. Plan Check | Use

EXPLAIN

to confirm partition pruning is working. Hardware | Ensure CN nodes have fast local SSDs/NVMe for the cache. For a deep dive into your specific query, run

SET enable_profile = true;

, execute the query, and inspect the profile via the StarRocks FE web UI (port 8030) to see where the time is being spent (e.g.,

S3_SCAN_WAIT

or

METADATA_FETCH

). References • unloading/Spark_connector.md