Hey @Rocky I have a StarRocks cluster with 2 FE r7i.4xlarge, and 5 CN r8g.8xlarge with 450GB gp3 EBS storage for cache. I created this native table:

CREATE TABLE `transfers` (
  `network_id` varchar(64) NULL COMMENT "",
  `network_name` varchar(64) NULL COMMENT "",
  `asset_id` varchar(128) NULL COMMENT "",
  `asset` varchar(128) NULL COMMENT "",
  `transaction_id` bigint(20) NULL COMMENT "",
  `transaction_hash` varchar(128) NULL COMMENT "",
  `transaction_index` int(11) NULL COMMENT "",
  `transaction_time` datetime NULL COMMENT "",
  `sender_cluster_id` varchar(512) NULL COMMENT "",
  `sender_entity_uuid` varchar(128) NULL COMMENT "",
  `sender_entity_name` varchar(512) NULL COMMENT "",
  `sender_entity_category` varchar(256) NULL COMMENT "",
  `receiver_cluster_id` varchar(512) NULL COMMENT "",
  `receiver_entity_uuid` varchar(128) NULL COMMENT "",
  `receiver_entity_name` varchar(512) NULL COMMENT "",
  `receiver_entity_category` varchar(256) NULL COMMENT "",
  `receiver_address` varchar(512) NULL COMMENT "",
  `sent` double NULL COMMENT "",
  `sent_usd` double NULL COMMENT "",
  `deposit_address` bigint(20) NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`network_id`, `network_name`, `asset_id`, `asset`)
COMMENT "OLAP"
PARTITION BY RANGE(`transaction_time`)
(PARTITION p_pre2012 VALUES [("0000-01-01 00:00:00"), ("2012-01-01 00:00:00")),
PARTITION p2012 VALUES [("2012-01-01 00:00:00"), ("2013-01-01 00:00:00")),
PARTITION p2013 VALUES [("2013-01-01 00:00:00"), ("2014-01-01 00:00:00")),
PARTITION p2014 VALUES [("2014-01-01 00:00:00"), ("2015-01-01 00:00:00")),
PARTITION p2015 VALUES [("2015-01-01 00:00:00"), ("2016-01-01 00:00:00")),
PARTITION p2016 VALUES [("2016-01-01 00:00:00"), ("2017-01-01 00:00:00")),
PARTITION p2017 VALUES [("2017-01-01 00:00:00"), ("2018-01-01 00:00:00")),
PARTITION p2018 VALUES [("2018-01-01 00:00:00"), ("2019-01-01 00:00:00")),
PARTITION p2019 VALUES [("2019-01-01 00:00:00"), ("2020-01-01 00:00:00")),
PARTITION p2020 VALUES [("2020-01-01 00:00:00"), ("2021-01-01 00:00:00")),
PARTITION p2021 VALUES [("2021-01-01 00:00:00"), ("2022-01-01 00:00:00")),
PARTITION p2022 VALUES [("2022-01-01 00:00:00"), ("2023-01-01 00:00:00")),
PARTITION p2023 VALUES [("2023-01-01 00:00:00"), ("2024-01-01 00:00:00")),
PARTITION p2024 VALUES [("2024-01-01 00:00:00"), ("2025-01-01 00:00:00")),
PARTITION p2025 VALUES [("2025-01-01 00:00:00"), ("2026-01-01 00:00:00")),
PARTITION p2026 VALUES [("2026-01-01 00:00:00"), ("2027-01-01 00:00:00")))
DISTRIBUTED BY HASH(`transaction_hash`) BUCKETS 32
ORDER BY(`transaction_time`, `transaction_hash`, `receiver_cluster_id`)
PROPERTIES (
"bloom_filter_columns" = "receiver_address, receiver_cluster_id, sender_cluster_id, transaction_hash",
"cloud_native_fast_schema_evolution_v2" = "true",
"compression" = "LZ4",
"datacache.enable" = "true",
"enable_async_write_back" = "false",
"file_bundling" = "true",
"replication_num" = "1",
"storage_volume" = "my_s3_volume"
);

after loaded it has | Metric | Value | |---|---| | Size | 445.786 GB | | Row Count | ~4.23 billion rows | | Replica Count | 512 | I am running a query like:

SELECT DISTINCT sender_cluster_id, receiver_cluster_id
FROM datacloud_transfers.transfers_3re_btc
WHERE sender_cluster_id IN ("1HY...","1GH...","3Mr...")
  AND receiver_cluster_id IS NOT NULL
  AND receiver_entity_category IS NULL

The explain shows it running a full table scan

+-----------------------------------------------------------------------------------------+
|     partitionsRatio=16/16, tabletsRatio=512/512                                         |
|     tabletList=954602,954603,954604,954605,954606,954607,954608,954609,954610,954611 ...|
|     actualRows=4229462647, avgRowSize=46.028316                                         |
|     cardinality: 13                                                                     |
+-----------------------------------------------------------------------------------------+

How should I organise the table to improve the query performance?

Hi there! I'm Rocky. Looking at your table schema and the query profile, it's clear why you're seeing a full table scan. The

partitionsRatio=16/16

and

tabletsRatio=512/512

in your explain plan confirm that no pruning is happening. To improve performance, we need to align how the data is stored with how you are querying it. Why the query is slow: 1. No Partition Pruning: You partition by

transaction_time

, but your query doesn't filter on it. StarRocks must check every partition. 2. No Bucket Pruning: You distribute by

transaction_hash

, but filter by

sender_cluster_id

. StarRocks must check every tablet (bucket). 3. Prefix Index Miss: Your

ORDER BY

(Sort Key) starts with

transaction_time

. Because your query doesn't filter on the first column of the sort key, StarRocks cannot use the Prefix Index to skip data blocks within files. Recommendations to improve performance: 1. Change the Distribution Key (Enable Bucket Pruning) Change the distribution key to

sender_cluster_id

. This will allow StarRocks to target only the specific buckets containing the IDs in your

IN

clause, potentially reducing the data scanned from 512 tablets to just a few. * Action: Since StarRocks 3.2, you can modify bucketing columns via

ALTER TABLE

. * SQL:

ALTER TABLE transfers DISTRIBUTED BY HASH(sender_cluster_id);

2. Change the Sort Key (Enable Prefix Index Pruning) The Sort Key (defined by

ORDER BY

) is the most powerful tool for internal data filtering. You should move

sender_cluster_id

to the first position. * Action: Since StarRocks 3.3, Duplicate Key tables support modifying the sort key. * SQL:

ALTER TABLE transfers ORDER BY (sender_cluster_id, receiver_cluster_id, transaction_time);

_ _Note:* This is an asynchronous operation. You can check progress with

SHOW ALTER TABLE COLUMN;

. 3. Add a Bitmap Index Since you are performing a

DISTINCT

and filtering on several columns, a Bitmap Index on

sender_cluster_id

and

receiver_cluster_id

can further accelerate the filtering process, especially for the

IS NOT NULL

and

IS NULL

conditions. * SQL:

sql
    CREATE INDEX idx_sender ON transfers (sender_cluster_id) USING BITMAP;
    CREATE INDEX idx_receiver ON transfers (receiver_cluster_id) USING BITMAP;

4. Query-side Optimization If your business logic allows, always include a

transaction_time

range (e.g.,

AND transaction_time > '2024-01-01'

) in your queries. This would enable Partition Pruning, allowing StarRocks to skip entire years of data instantly. Summary of optimized DDL (Simplified): If you were to recreate the table (or after the

ALTER

operations finish), the configuration should look more like this:

sql
CREATE TABLE `transfers` (
  ...
) ENGINE=OLAP
DUPLICATE KEY(`sender_cluster_id`, `receiver_cluster_id`) -- Defines the sort key
PARTITION BY RANGE(`transaction_time`) (...)
DISTRIBUTED BY HASH(`sender_cluster_id`) BUCKETS 32 -- Matches your frequent filter
ORDER BY(`sender_cluster_id`, `receiver_cluster_id`, `transaction_time`) -- Critical for Prefix Index
PROPERTIES (
  "bloom_filter_columns" = "sender_cluster_id, receiver_cluster_id",
  ...
);

By making

sender_cluster_id

the distribution key and the first column in your sort key, your query will go from a "Full Table Scan" to a "Targeted Index Lookup," which should result in a massive performance boost for a 4-billion-row dataset. References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • table_design/StarRocks_table_design.md • best_practices/partitioning.md

@Rocky With my original table, I ran the benchmark and got this outcome

[1/20] R1: sender/receiver cluster + time filter... 1.17s
  [2/20] R3: transaction hash IN list... 1.23s
  [3/20] R4: distinct cluster pairs by time... 1.25s
  [4/20] R5: multi-column by tx hash IN... 0.83s
  [5/20] R6: transfer ratio with cluster_summary join... 18.25s
  [6/20] R8: distinct pairs, no receiver category... ERR
  [7/20] R9: distinct by tx hash + asset_id... 0.88s
  [8/20] R10: sender cluster + time range + order... 1.12s
  [9/20] R11: monthly received aggregation... 6.78s
  [10/20] R13: single tx hash full detail... 0.87s
  [11/20] R14: tx detail by hash list + asset + order... 0.84s
  [12/20] R15: distinct tx by sender cluster from address... 45.42s
  [13/20] R16: aggregated flow summary by cluster pairs... ERR
  [14/20] R17: distinct tx by receiver address + asset... 6.82s
  [15/20] R21: monthly received + active receiver count... 2.40s
  [16/20] R22: amount by sender+receiver+asset... 0.83s
  [17/20] R23: sender outflows ordered by time... 0.81s
  [18/20] R56: country flow with observation join... 1.30s
  [19/20] R60: receiver cluster + asset ordered... ERR
  [20/20] R67: SELECT * LIMIT scan... 1.78s

Then I’ve changed created another table with your advise

CREATE TABLE transfers_2 (
  `sender_cluster_id` varchar(512) NULL COMMENT "",
  `receiver_cluster_id` varchar(512) NULL COMMENT "",
  `network_id` varchar(64) NULL COMMENT "",
  `network_name` varchar(64) NULL COMMENT "",
  `asset_id` varchar(128) NULL COMMENT "",
  `asset` varchar(128) NULL COMMENT "",
  `transaction_id` bigint(20) NULL COMMENT "",
  `transaction_hash` varchar(128) NULL COMMENT "",
  `transaction_index` int(11) NULL COMMENT "",
  `transaction_time` datetime NULL COMMENT "",
  `sender_entity_uuid` varchar(128) NULL COMMENT "",
  `sender_entity_name` varchar(512) NULL COMMENT "",
  `sender_entity_category` varchar(256) NULL COMMENT "",
  `receiver_entity_uuid` varchar(128) NULL COMMENT "",
  `receiver_entity_name` varchar(512) NULL COMMENT "",
  `receiver_entity_category` varchar(256) NULL COMMENT "",
  `receiver_address` varchar(512) NULL COMMENT "",
  `sent` double NULL COMMENT "",
  `sent_usd` double NULL COMMENT "",
  `deposit_address` bigint(20) NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`sender_cluster_id`, `receiver_cluster_id`)
COMMENT "OLAP"
PARTITION BY RANGE(`transaction_time`)
(PARTITION p_pre2012 VALUES [("0000-01-01 00:00:00"), ("2012-01-01 00:00:00")),
PARTITION p2012 VALUES [("2012-01-01 00:00:00"), ("2013-01-01 00:00:00")),
PARTITION p2013 VALUES [("2013-01-01 00:00:00"), ("2014-01-01 00:00:00")),
PARTITION p2014 VALUES [("2014-01-01 00:00:00"), ("2015-01-01 00:00:00")),
PARTITION p2015 VALUES [("2015-01-01 00:00:00"), ("2016-01-01 00:00:00")),
PARTITION p2016 VALUES [("2016-01-01 00:00:00"), ("2017-01-01 00:00:00")),
PARTITION p2017 VALUES [("2017-01-01 00:00:00"), ("2018-01-01 00:00:00")),
PARTITION p2018 VALUES [("2018-01-01 00:00:00"), ("2019-01-01 00:00:00")),
PARTITION p2019 VALUES [("2019-01-01 00:00:00"), ("2020-01-01 00:00:00")),
PARTITION p2020 VALUES [("2020-01-01 00:00:00"), ("2021-01-01 00:00:00")),
PARTITION p2021 VALUES [("2021-01-01 00:00:00"), ("2022-01-01 00:00:00")),
PARTITION p2022 VALUES [("2022-01-01 00:00:00"), ("2023-01-01 00:00:00")),
PARTITION p2023 VALUES [("2023-01-01 00:00:00"), ("2024-01-01 00:00:00")),
PARTITION p2024 VALUES [("2024-01-01 00:00:00"), ("2025-01-01 00:00:00")),
PARTITION p2025 VALUES [("2025-01-01 00:00:00"), ("2026-01-01 00:00:00")),
PARTITION p2026 VALUES [("2026-01-01 00:00:00"), ("2027-01-01 00:00:00")))
DISTRIBUTED BY HASH(`sender_cluster_id`) BUCKETS 32
ORDER BY(`sender_cluster_id`, `receiver_cluster_id`, `transaction_time`)
PROPERTIES (
"bloom_filter_columns" = "receiver_address, receiver_cluster_id, sender_cluster_id, transaction_hash",
"cloud_native_fast_schema_evolution_v2" = "true",
"compression" = "LZ4",
"datacache.enable" = "true",
"enable_async_write_back" = "false",
"file_bundling" = "true",
"replication_num" = "1",
"storage_volume" = "my_s3_volume"
);
CREATE INDEX idx_sender ON transfers_2 (sender_cluster_id) USING BITMAP;
CREATE INDEX idx_receiver ON transfers_2 (receiver_cluster_id) USING BITMAP;

And the benchmark result seems worse

============================================================
SERIAL BASELINE
============================================================
  [1/20] R1: sender/receiver cluster + time filter... 1.95s
  [2/20] R3: transaction hash IN list... 6.19s
  [3/20] R4: distinct cluster pairs by time... 0.83s
  [4/20] R5: multi-column by tx hash IN... 0.89s
  [5/20] R6: transfer ratio with cluster_summary join... 13.69s
  [6/20] R8: distinct pairs, no receiver category... ERR
  [7/20] R9: distinct by tx hash + asset_id... 1.19s
  [8/20] R10: sender cluster + time range + order... 0.87s
  [9/20] R11: monthly received aggregation... 8.77s
  [10/20] R13: single tx hash full detail... 0.97s
  [11/20] R14: tx detail by hash list + asset + order... 1.23s
  [12/20] R15: distinct tx by sender cluster from address... 68.14s
  [13/20] R16: aggregated flow summary by cluster pairs... ERR
  [14/20] R17: distinct tx by receiver address + asset... 5.04s
  [15/20] R21: monthly received + active receiver count... 1.64s
  [16/20] R22: amount by sender+receiver+asset... 0.79s
  [17/20] R23: sender outflows ordered by time... 0.75s
  [18/20] R56: country flow with observation join... 6.59s
  [19/20] R60: receiver cluster + asset ordered... ERR
  [20/20] R67: SELECT * LIMIT scan... 2.05s

The new explain for the query r8 shows less tablets but the first run is still failing on first run after warming the cache

+---------------------------------------------------------------------------------------------------+
|     partitionsRatio=16/16, tabletsRatio=48/512                                                    |
|     tabletList=1031017,1031011,1031024,1030985,1030979,1030992,1031465,1031459,1031472,1031433 ...|
|     actualRows=564519228, avgRowSize=3.0                                                          |
|     cardinality: 190325819                                                                        |
+---------------------------------------------------------------------------------------------------+

What can be the problem?

The benchmark results and the "ERR" on query R8 reveal a classic trade-off in database design: Optimizing for one access pattern often penalizes another. Here is the analysis of why the performance changed and how to achieve a "best of both worlds" setup. 1. Why the "ERR" and the regression in R3, R9, R13, R14? * The Distribution Key Trade-off: In your original table, you distributed by

transaction_hash

. Queries like R3 (transaction hash IN list) were lightning-fast because StarRocks knew exactly which 1 or 2 tablets to look in. By changing distribution to

sender_cluster_id

, a query for a

transaction_hash

now has to scan all 512 tablets across the cluster. Even with Bloom Filters, the overhead of opening 512 files is much higher than opening 2. * The "ERR" on R8 (Memory/Result Set): Your

EXPLAIN

shows

actualRows=564M

and

cardinality: 190M

. Performing a

DISTINCT

on 190 million pairs of

VARCHAR(512)

is extremely memory-intensive. * Reason 1: You are likely hitting the

query_mem_limit

. * Reason 2: If this query is meant to return the data to a client, transmitting 190 million rows of long strings will likely time out or crash the client/driver. 2. Strategic Recommendations A. The "Best of Both Worlds" (Materialized Views) Instead of forcing one table to do everything, keep the base table optimized for "Point Lookups" (by hash) and create a Synchronous Materialized View for the "Analytical" queries (by cluster). * Base Table: Distribute by

transaction_hash

(fixes R3, R9, R13, R14). * Materialized View: This MV will store the distinct pairs pre-aggregated and distributed by

sender_cluster_id

.

sql
CREATE MATERIALIZED VIEW mv_cluster_pairs AS
SELECT sender_cluster_id, receiver_cluster_id, transaction_time, receiver_entity_category
FROM transfers
GROUP BY sender_cluster_id, receiver_cluster_id, transaction_time, receiver_entity_category;

StarRocks will automatically route query R8 to this MV, which is much smaller and sorted/distributed correctly. B. Fix the "ERR" (Memory Tuning) To handle the high-cardinality

DISTINCT

in R8, increase the session memory limit:

sql
SET user_variable:query_mem_limit = 17179869184; -- Increase to 16GB or higher
-- If the error is "Result set too large", check:
SET GLOBAL max_chunk_size = 4096;

C. Optimize the Bucket Count With 5 CNs (r8g.8xlarge has 32 vCPUs), you have 160 total cores. * Current: 32 buckets. * Problem: A query hitting one partition only uses 32 cores (20% of your cluster). * Recommendation: Increase buckets to 80 or 128. This ensures that even a single-partition query can utilize all CPU cores across your 5 nodes. * SQL:

ALTER TABLE transfers DISTRIBUTED BY HASH(transaction_hash) BUCKETS 128;

D. Replace Bitmap Indexes with Bloom Filters Bitmap indexes are inefficient for high-cardinality

VARCHAR(512)

(long unique IDs). They are best for low-cardinality columns (e.g.,

network_name

,

category

). * Action: Drop the Bitmaps on

sender_cluster_id

and

receiver_cluster_id

. * Action: Ensure they are in the

bloom_filter_columns

property instead (which you already have). Summary of the "Golden" Config: If you want one table to handle most of this, I recommend returning to

transaction_hash

distribution but refining the Sort Key and Bucketing: 1. Distribution:

HASH(transaction_hash)

2. Buckets:

128

3. Sort Key (ORDER BY): Move the most frequent filter to the front. If you query by

sender_cluster_id

more than

time

, put it first. *

ORDER BY(sender_cluster_id, transaction_time)

4. Bloom Filters: Keep on

sender_cluster_id

and

receiver_cluster_id

. 5. Memory: Run

SET GLOBAL query_mem_limit = 34359738368;

(32GB) to handle the 190M row distinct operations. Would you like me to help you refine the specific Materialized View DDL to cover more of your benchmark queries? References