@Rocky How do the histogram statistics work?

In StarRocks, Histogram statistics are a sophisticated way to represent data distribution, complementing basic statistics (like min, max, and NDV). They are particularly effective for tables with skewed data, where basic statistics might fail to provide an accurate picture for the Cost-Based Optimizer (CBO). Here is a breakdown of how they work: 1. Core Logic: Equi-height Buckets StarRocks uses equi-height histograms. Unlike equi-width histograms (where each bucket covers the same range of values), equi-height buckets are designed so that each bucket contains approximately the same number of rows. * Skew Handling: If a certain value range has a high density of data, the histogram will create more granular buckets for that range. * Buckets: You can specify the number of buckets (default is usually 64). More buckets provide higher precision but consume more memory during query optimization. 2. Most Common Values (MCV) To further improve accuracy, StarRocks extracts the Most Common Values (MCV) from the column. * Values that appear very frequently are stored separately from the buckets. * By isolating these "heavy hitters," the CBO can precisely calculate selectivity for equality predicates (e.g.,

WHERE status = 'active'

) without being misled by the average distribution in the buckets. 3. Supported Data Types Histograms can be created on columns of the following types: * Numeric (INT, BIGINT, DECIMAL, etc.) * DATE / DATETIME * String (VARCHAR, CHAR) 4. How to Collect Histograms Histograms are not collected automatically by default. You must trigger them manually using the

ANALYZE

command:

sql
-- Basic collection
ANALYZE TABLE my_table UPDATE HISTOGRAM ON col_name;

-- Collection with custom buckets and MCVs
ANALYZE TABLE my_table UPDATE HISTOGRAM ON col_name 
WITH 32 BUCKETS 
PROPERTIES(
   "histogram_mcv_size" = "50",
   "histogram_sample_ratio" = "0.1"
);

5. When to Use Them You should consider histograms if: * Your data is highly skewed (e.g., a few values account for 80% of the rows). * The CBO is choosing a sub-optimal execution plan (like an inefficient Join order) because it is overestimating or underestimating row counts. * Note: If your data is uniformly distributed, basic statistics are usually sufficient and more efficient. 6. Viewing Statistics You can check the metadata or the actual stored histogram data using: *

SHOW HISTOGRAM META;

— To see which columns have histograms and when they were updated. _

SELECT _ FROM _statistics_.histogram_statistics;

— To see the raw bucket and MCV data stored in the internal statistics database. Summary of Benefits By providing a detailed map of data density, histograms allow the CBO to: 1. Estimate the selectivity of filters more accurately. 2. Choose the optimal Join order and Join type (e.g., Broadcast vs. Shuffle). 3. Better decide whether to use specific indexes or optimizations like Runtime Filters. References • using_starrocks/Cost_based_optimizer.md

@Rocky I have a few tables with organization_uuid as the first part of the pkey (splitting data per org). the tables are very uneven - some orgs have a hundred or so records, while others may have tens of millions, so id like to see if some histogram stats improves perf. on the smaller tables, i can create a histogram just fine, but running it on the larger tables consistently gives

Getting syntax error from line 1, column 317 to line 1, column 337. Detail message: invalid percent which should in (0, 100)

regardless of if i pass in the

histogram_sample_ratio

property or not. for ref, running starrocks version 4.0.8-2090bfc. any ideas?

The error message

Getting syntax error ... invalid percent which should in (0, 100)

is a known issue that typically occurs when the internally generated sampling query for histogram collection calculates a sampling percentage that falls outside the allowed range of

(0, 100)

. This usually happens on very large tables because StarRocks tries to limit the number of rows it scans to prevent the

ANALYZE

task from overwhelming the system. Why this is happening When you run

ANALYZE ... UPDATE HISTOGRAM

, StarRocks determines the number of rows to sample using these three parameters: 1.

histogram_sample_ratio

: Default is

0.1

(10%). 2.

statistic_sample_collect_rows

: Default is

200,000

. 3.

histogram_max_sample_row_count

: Default is

10,000,000

. The system calculates the target sample size as

max(200,000, total_rows * ratio)

. It then caps this at

histogram_max_sample_row_count

(10 million). For a table with billions of rows, the required sampling percentage to get only 10 million rows becomes extremely small (e.g.,

10,000,000 / 1,000,000,000 * 100 = 1%

). If the table is large enough, or if there is a precision issue in the version you are running (4.0.8-2090bfc appears to be a development or customized build), the resulting percentage might be rounded to

0

or formatted in scientific notation (like

1e-5

) that the SQL parser fails to recognize in the internal

TABLESAMPLE

clause. Recommended Workarounds 1. Manually set a fixed (larger) sample ratio Try to bypass the automatic calculation by providing a specific ratio that ensures the percentage is a "clean" number:

sql
ANALYZE TABLE your_table UPDATE HISTOGRAM ON organization_uuid 
PROPERTIES (
    "histogram_sample_ratio" = "0.1", 
    "histogram_max_sample_row_count" = "20000000" -- Increase the cap
);

2. Increase the Max Sample Row Count The error often triggers because the system is trying to force a tiny percentage to respect the 10M row cap. Increasing this cap for the specific command can resolve the "invalid percent" error:

sql
ANALYZE TABLE your_table UPDATE HISTOGRAM ON organization_uuid 
PROPERTIES (
    "histogram_max_sample_row_count" = "100000000" -- Set to 100M
);

3. Refresh Basic Statistics First Sometimes the metadata for the table's row count is stale or incorrect, causing the math to fail. Run a basic analyze first:

sql
ANALYZE TABLE your_table; -- Collects row_count, min, max, etc.

4. Check FE Logs Since you are seeing a syntax error at a specific column (317-337), it means the SQL generated by the FE is malformed. You can find the exact faulty SQL by checking the

fe.log

on your leader FE node. Look for the

SELECT ... TABLESAMPLE ...

query that failed. If the percentage looks like

0.0

or

1e-7

, it confirms the precision/rounding bug. A Note on Versioning The version

4.0.8-2090bfc

is not a standard StarRocks GA release (current stable releases are in the 3.x branch). This version may contain experimental features or bugs related to how statistics are handled for massive datasets. If the workarounds above don't work, you may need to reach out to your distribution provider or check if a more recent build is available. References • administration/management/FE_configuration.md • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md