@Rocky on running order by on a table with many partitions day wise, does it not pick few latest partitions and sort the data if sorting on partition col? looks like it is doing all scan which is impacting cluster. can I do something to prevent this?

When you run a query with

ORDER BY partition_col LIMIT N

but without a

WHERE

clause, StarRocks does not automatically prune partitions. Even if you are sorting on the partition column, the query planner assumes it may need to scan all data to satisfy the sort and limit, especially since data is distributed across many tablets and nodes. Here is why this is happening and how you can prevent it: 1. Why it scans all partitions * Planner Behavior: Partition pruning in StarRocks is triggered by predicates in the

WHERE

clause. Without a

WHERE

clause, the optimizer cannot guarantee that the first

N

rows will only come from the latest partition, so it schedules a scan for all partitions. * Distributed Architecture: Because data is sharded across multiple machines, StarRocks typically starts parallel scans on all involved tablets. While the

TopN

optimization (

enable_topn_runtime_filter

) can help stop scans early once enough rows are found, it still initiates the scan process across the cluster, which causes the "all scan" impact you're seeing. 2. How to prevent this Use a Explicit

WHERE

Clause (Recommended) The most effective way to prevent a full scan is to give the optimizer a range. Even a generous range will significantly reduce the load.

sql
-- Instead of:
SELECT * FROM table ORDER BY day_col DESC LIMIT 10;

-- Use:
SELECT * FROM table 
WHERE day_col >= '2023-10-01' -- Restrict to a recent range
ORDER BY day_col DESC 
LIMIT 10;

Check

enable_topn_runtime_filter

Since StarRocks v3.3, there is a session variable

enable_topn_runtime_filter

(enabled by default). It allows the

TopN

node to push down a filter to the scan nodes at runtime. Once the system finds the "top" values in one tablet, it uses those values to skip data in other tablets. * Ensure this is enabled:

SET enable_topn_runtime_filter = true;

* Note: This helps with I/O during the scan but doesn't prevent the initial "scheduling" of all partitions. Use

EXPLAIN

to Verify Run

EXPLAIN

on your query to see exactly how many partitions are being hit:

sql
EXPLAIN SELECT * FROM table ORDER BY partition_col DESC LIMIT 10;

Look for the

OlapScanNode

section. If it says

partitions=500/500

, it means no pruning occurred. If you add a

WHERE

clause, you should see something like

partitions=1/500

. Sorting via Sort Key Ensure that the column you are ordering by is also defined as a Sort Key (the

ORDER BY

clause in the

CREATE TABLE

statement). If the partition column is also the sort key, StarRocks can use the "Zone Map" (min/max indexes) within each tablet to skip data much faster, even if the partition itself wasn't pruned. Summary StarRocks currently requires a predicate (filter) to prune partitions at the planning stage. To avoid cluster impact, always include a

WHERE

clause to restrict the time range, even when using

LIMIT

. References • knowledge/usage/best-practice.md • faq/Sql_faq.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • unloading/Spark_connector.md • best_practices/table_clustering.md

@Rocky does starrocks not maintain min/max of partition col in each partition (using expression partitiong) , version 4.0.8 shared data. ? I can see in explain it is scanning all partitions which is causing problem as one bigtable sort can impact entire cluster as well. give some way to restrict for admin

In StarRocks (including version 4.0.8 shared-data), the query planner generally performs partition pruning only when it sees a predicate (filter) in the

WHERE

clause. Even if you sort by the partition column, the engine does not automatically assume it can skip partitions unless you tell it which range to look at. Why it scans all partitions * Planner Logic: The Frontend (FE) planner triggers partition pruning by evaluating constant predicates. Without a

WHERE

clause, the planner assumes a "full table scan" is required. It doesn't perform "Top-N pruning" across partitions at the planning stage because it can't guarantee the top $N$ rows exist in the latest partition without checking metadata for all of them, which in a distributed system, usually defaults to a parallel scan of all tablets. * Impact on Cluster: In a shared-data (Cloud Native) architecture, scanning all partitions involves massive metadata requests to the object store and potentially many I/O requests, which can saturate the FE and BE resources. Administrative Restrictions (How to prevent this) If you are an admin looking to prevent users from bringing down the cluster with unfiltered

ORDER BY

queries, you have several tools: 1. Resource Groups (The "Big Query" Kill Switch) You can create a Resource Group to automatically reject or terminate queries that exceed a certain scan row count. This is the most robust way to protect the cluster.

sql
CREATE RESOURCE GROUP protect_cluster
TO (user='all') -- or specific users
WITH (
    "big_query_scan_rows_limit" = "1000000000", -- Reject if > 1B rows
    "big_query_cpu_second_limit" = "100",      -- Reject if CPU time > 100s
    "big_query_mem_limit" = "2147483648"       -- Reject if > 2GB mem
);

2. Scan Partition Limits For shared-data/lakehouse tables, you can use session variables to limit how many partitions a single query can touch. *

SET GLOBAL scan_lake_partition_num_limit = 100;

If a query tries to scan more than 100 partitions, it will be rejected. This forces users to add a

WHERE

clause. 3. Enforce Partition Filters You can set a session variable (often used for external tables but useful as a safety hint) to reject queries that lack a partition filter: *

SET GLOBAL allow_hive_without_partition_filter = false;

(Note: This specific variable applies primarily to Hive-style catalogs, but similar logic can be enforced via Resource Groups for internal tables). Practical Fixes for Users * Include a "Lazy" Filter: Users should be trained to always include a broad time range. Even

WHERE partition_col >= '2024-01-01'

will prune years of data and save the cluster. * Check `enable_topn_runtime_filter`: Ensure this is

true

(it is by default in v3.x+). It allows the scan to stop early at runtime once the Top-N rows are found, though it doesn't prevent the initial scan scheduling. * Sort Key Alignment: Ensure the partition column is the first column in the

ORDER BY

clause of the

CREATE TABLE

statement. This creates a "Zone Map" index that allows StarRocks to skip data blocks within partitions even if the partition itself wasn't pruned. Summary: StarRocks relies on predicates to prune partitions. To protect your cluster, use Resource Groups with a

big_query_scan_rows_limit

or set a

scan_lake_partition_num_limit

to force users to add filters to their queries. References • faq/Sql_faq.md • best_practices/table_clustering.md • using_starrocks/caching/query_cache.md