@Rocky I'm using a glue catalog, and I have a not iceberg table, specifically JSON GZIP, which is partitioned by year,month,day. When I try to use partition pruning, the query takes really long ( > minutes, or timeout) despited the partition on the table. The explain on the query show that the partitions should be used, anyhow they do not seems to really take effect, because the data for every partition is really limited.

The behavior you are describing—where

EXPLAIN

confirms partition pruning is planned but the query remains extremely slow or times out—is common with AWS Glue when the metadata retrieval phase becomes a bottleneck. Even if the data itself is small, the overhead of fetching partition lists and file locations can take minutes. Here are the most likely causes and solutions: 1. Glue Partition Metadata Bottleneck When querying Glue, StarRocks must call the Glue

GetPartitions

API. If your table has a massive total number of partitions (thousands or more), Glue can be slow to return results, and StarRocks might spend minutes just "planning" the query. * Solution: Enable Glue Partition Indexes. AWS Glue Partition Indexes allow the Glue service to filter partitions on the server side. Without an index, Glue may fetch all partition metadata and filter it locally in StarRocks, which is very slow for large catalogs. * Create a partition index in the AWS Glue Console on the columns

year, month, day

. * Ensure StarRocks is configured to use the index (this is usually automatic if the index exists, but check if your StarRocks version has specific Glue catalog properties for this). 2. Planning vs. Execution Time Check whether the delay is in Planning or Execution: * Run

SET enable_profile = true;

before your query. * After the query finishes (or if it's running long, check the

Profile

via the FE web UI or

SHOW PROFILE

). * If Query Planning takes the bulk of the time, the issue is metadata retrieval from Glue/S3. * If Scan takes the time, it’s a data reading issue (JSON/GZIP overhead). 3. S3 File Listing Overhead Even after partitions are pruned, StarRocks must list the S3 objects for the remaining partitions. If you have a "small file problem" (many small GZIP files), the metadata listing can be very slow. * Check

enable_remote_file_cache

: Ensure this is

true

in your Catalog properties (it is by default in v3.0+). This caches the file list so subsequent queries are faster. * Configuration:

sql
    ALTER CATALOG your_glue_catalog SET ("enable_remote_file_cache" = "true");

4. JSON + GZIP Performance JSON and GZIP are both performance-heavy: * GZIP is not splittable: A single

.json.gz

file cannot be read in parallel by multiple threads. If a partition contains one relatively large GZIP file, only one thread will process it. * JSON Parsing: JSON parsing is significantly slower than Parquet or ORC. If the files are complex, the CPU cost on the BE nodes can be high. * Tip: If this is a high-frequency query, consider converting the table to Parquet/Zstd or creating an Asynchronous Materialized View in StarRocks to cache the data in StarRocks' native format. 5. Known Bug in v3.3/4.0 There is a known issue in some 3.3.x and early 4.0.x versions where StarRocks fetches partition metadata one-by-one instead of in batches, causing massive pressure on the metastore. * Recommendation: If you are on these versions and see "Planning" taking forever, try upgrading to the latest patch release (e.g., 3.3.6+). Summary Checklist: 1. Add a Partition Index in AWS Glue for

year

,

month

, and

day

. 2. Verify Metadata Caching: Ensure

enable_cache_list_names = true

and

enable_remote_file_cache = true

in your catalog. 3. Check Table Stats: If StarRocks doesn't have accurate statistics, it might choose a sub-optimal plan. Try

ANALYZE EXTERNAL TABLE your_table

. _table;`. References • loading/loading_introduction/feature-support-loading-and-unloading.md • using_starrocks/Flat_json.md • administration/management/BE_configuration.md

@Rocky can it be that indeed the caching is the issue here? starrocks try to cache all the table and there tons of small files.

It is very likely that the metadata retrieval and caching process is the bottleneck, rather than the data scanning itself. When querying a Glue catalog table with many partitions and small files, StarRocks performs several metadata operations that can take minutes if not optimized. Here is why your query feels slow despite partition pruning being shown in the

EXPLAIN

plan: 1. The Glue "GetPartitions" Bottleneck Even if your query only needs 3 days of data, StarRocks may still be calling the Glue

GetPartitions

API to verify the list of partitions. * The Issue: By default, if a Glue table has thousands of total partitions, the Glue API can be very slow to return results. * The Solution: Enable Glue Partition Indexes. Go to the AWS Glue Console, find your table, and create a partition index on the columns

year, month, day

. This allows Glue to filter the partitions on the server-side before sending the list to StarRocks, reducing the planning time from minutes to seconds. 2. Caching "Tons of Small Files" You mentioned there are many small files. While StarRocks doesn't cache the data of the whole table, it does cache the metadata (file paths) for the partitions it touches. * The Issue: If the partitions you are querying contain thousands of small

.json.gz

files, StarRocks must list every single file via S3 API calls to build its "file-level metadata cache" (

enable_remote_file_cache

). For the first query (the "cold" query), this file listing is synchronous and can cause a timeout. * The Solution: * Background Refresh: Set the FE configuration

enable_background_refresh_connector_metadata = true

. This tells StarRocks to refresh the metadata in the background rather than making the user wait during the query. * Check the Profile: Run

SET enable_profile = true;

and check the Query Planning time versus Execution time. If "Planning" is high, it's metadata. 3. JSON + GZIP Performance Overhead JSON and GZIP are particularly "heavy" for external catalogs: * GZIP is not splittable: A single

.gz

file cannot be read in parallel. If you have many small files, the overhead of opening and closing thousands of S3 connections is massive. * JSON Parsing: Parsing JSON is significantly slower than reading columnar formats like Parquet. Recommendations to Fix This: 1. Add Partition Indexes in AWS Glue for your partition keys (

year

,

month

,

day

). 2. Verify Catalog Properties: Ensure your Glue catalog has the following (these are usually default but worth checking):

sql
    ALTER CATALOG your_glue_catalog SET (
        "enable_metastore_cache" = "true",
        "enable_remote_file_cache" = "true"
    );

3. Use an Asynchronous Materialized View (MV): If this query is frequent, create an MV in StarRocks that pulls from this Glue table. StarRocks will store the data in its native, optimized format (partitioned and indexed), which will eliminate the Glue/S3 metadata overhead entirely.

sql
    CREATE MATERIALIZED VIEW mv_fast_data
    PARTITION BY (year, month, day)
    DISTRIBUTED BY HASH(some_id)
    REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
    AS
    SELECT * FROM your_glue_catalog.db.table;

4. Check FE Version: If you are on a version earlier than 3.3.x, some Glue metadata operations were less efficient. Ensure you are on a recent stable release (3.3+ or 4.0). 4.x or later). References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/hive_catalog.md • table_design/data_distribution/feature-support-data-distribution.md

@Rocky before enabling this:

ALTER CATALOG your_glue_catalog SET (
        "enable_metastore_cache" = "true",
        "enable_remote_file_cache" = "true"
    );

how do I check the catalog properties?

To check the current configuration and properties of your catalog, use the following command:

sql
SHOW CREATE CATALOG your_glue_catalog;

This will return the SQL statement that was used to create the catalog, including all the key-value pairs in the

PROPERTIES

section. What to look for: When you run the command, look for these specific keys in the output: * `"hive.metastore.type" = "glue"`: Confirms it is a Glue catalog. * `"enable_metastore_cache"`: If this is missing or set to

false

, metadata caching is disabled. * `"enable_remote_file_cache"`: If this is missing or set to

false

, the file-level metadata (listing of S3 files) is not being cached. Important Note on Cache Persistence: In StarRocks, metadata caching consists of two parts: 1. Metastore Cache (FE): Caches partition names and table schemas. 2. File Cache (FE): Caches the list of S3 file paths within those partitions. If your table has "tons of small files," the first time you run a query after a cache expiry, StarRocks must perform a massive S3

ListObjects

operation. If this is timing out, checking the properties using the command above is the first step to ensure your settings are actually applied. References • data_source/catalog/hive_catalog.md • data_source/catalog/deltalake_catalog.md • data_source/catalog/hudi_catalog.md • data_source/catalog/jdbc_catalog.md • data_source/catalog/kudu_catalog.md • data_source/catalog/maxcompute_catalog.md • data_source/catalog/unified_catalog.md • data_source/catalog/catalog_overview.md References • data_source/catalog/kudu_catalog.md • data_source/catalog/deltalake_catalog.md • data_source/catalog/unified_catalog.md • data_source/catalog/jdbc_catalog.md • data_source/catalog/hive_catalog.md • data_source/catalog/maxcompute_catalog.md • data_source/catalog/hudi_catalog.md • data_source/catalog/catalog_overview.md