Automated Column Statistics (ACS)

​

Overview

• Better query plans - The planner can choose more optimal join orders, aggregation strategies, and execution paths
• Improved performance - Statistical information helps estimate cardinalities more accurately
• Minimal impact on query planning times - Statistics are cached in memory for fast access to reduce impact on query planning times to a minimum (usually only a few microseconds)

​

Configuration

-- Enable automated column statistics for the current session
SET enable_automated_column_statistics = true;

​

How it works

​

Statistical Information Extraction

1. Identifies relevant aggregating indexes - Scans for aggregating indexes on tables referenced in the query that provide statistical information
2. Extracts statistics - Reads the precomputed statistics from the aggregating indexes into the in-memory cache
3. Caches statistics in memory - Caches the extracted statistics in memory for fast subsequent access (within microseconds)
4. Serves statistics to query planning – Statistics stored in the in-memory cache are served to the query planner, so it can make more informed decisions
5. Maintains consistency - Automatically refreshes cached statistics when the underlying data changes

​

Performance Characteristics

• Initial read: Reading statistics from aggregating indexes typically takes tens of milliseconds
• Cached access: Subsequent queries access cached statistics within microseconds
• Automatic refresh: Cache is invalidated and refreshed after DML operations on the origin table (the table that the aggregating index is defined on)

​

Example Usage

CREATE TABLE employees
(
    name TEXT,
    date_of_birth DATE,
    gender TEXT,
    title TEXT,
    salary REAL,
    dept_id INT
);

CREATE TABLE departments
(
    dept_id INT,
    dept_name TEXT,
    location TEXT,
    budget REAL,
    manager_id INT
);

​

Creating an Aggregating Index for Statistics

CREATE AGGREGATING INDEX employees_statistics
ON employees
(
    APPROX_COUNT_DISTINCT(gender),      -- for filtering by gender
    APPROX_COUNT_DISTINCT(title),       -- for filtering by title
    APPROX_COUNT_DISTINCT(dept_id)      -- for joining on department id
);

CREATE AGGREGATING INDEX departments_statistics
ON departments
(
    APPROX_COUNT_DISTINCT(dept_id),     -- for joining on department id
    APPROX_COUNT_DISTINCT(dept_name),   -- for grouping by department name
    APPROX_COUNT_DISTINCT(location)     -- for filtering by location
);

​

Using Automated Column Statistics

-- Enable the feature
SET enable_automated_column_statistics = true;

-- Example query
SELECT
    d.dept_name,
    e.gender,
    COUNT(*) as employee_count,
    AVG(e.salary) as avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.location = 'New York'
  AND e.title = 'Senior Developer'
GROUP BY d.dept_name, e.gender
ORDER BY employee_count DESC;

• The filter [6] [Filter] (employees.title = 'Senior Developer') is estimated to produce 6 rows because ACS provides an appropriate distinct count of 27 for employees.title. This estimation can be far off if data is skewed, but it often improves significantly over default estimations.
• The filter [9] [Filter] (departments.location = 'New York') is estimated to produce only 3 rows because ACS provides an appropriate distinct count of 3 for departments.location. This estimation can be far off if data is skewed, but it often improves significantly over default estimations.
• The join [4] [Join] Mode: Inner [(employees.dept_id = departments.dept_id)] is estimated to produce 5 rows. The query planner is able to infer from the distinct count statistics on departments.dept_id that this column is unique (and likely a primary key). Consequently, the join cannot produce more rows than are coming from the foreign key side. Because some rows from departments have been filtered out, the planner cannot guarantee that every row from the foreign key side will find a join partner and be produced. It performs a conservative estimation that one row will not be produced and the other 5 rows will be produced.
• The aggregation [2] [Aggregate] GroupBy: [employees.gender, departments.dept_name] Aggregates: [count_0: count(*), avg2_0: avg2(employees.salary)] is estimated to produce 5 groups. While ACS suggests 2 distinct genders and 3 distinct department names, the product 2 * 3 = 6 is bounded by the expected row count of 5. Hence, the planner predicts 5 groups.

​

Current Limitations

• Minimum and maximum value statistics for better range estimation
• Additional aggregate statistics from aggregating indexes

CREATE TABLE employees
(
    name TEXT,
    date_of_birth DATE,
    gender TEXT STATISTICS,
    title TEXT STATISTICS,
    salary REAL,
    dept_id INT STATISTICS
);

​

Best Practices

​

Index Design

1. Identify key columns - Maintain statistics with aggregating indexes for columns frequently used in:
   • GROUP BY clauses
   • WHERE predicates
   • JOIN conditions
2. Balance overhead - While aggregating indexes provide benefits, they also have maintenance overhead. Always measure the effect on query performance, data ingestion, and storage cost.

​

Query Optimization

1. Enable selectively - Use enable_automated_column_statistics = true for queries that can benefit from better cardinality estimation
2. Monitor performance - Compare query plans and execution times with and without the feature enabled
3. Combine with other optimizations - Use alongside other Firebolt optimization features for maximum benefit

​

Monitoring and Troubleshooting

​

Inspecting Statistics Usage

SET enable_automated_column_statistics = true;

EXPLAIN (logical, statistics)
SELECT gender, COUNT(*)
FROM employees
GROUP BY gender;

​

Cache Behavior

• Cache invalidation - Statistics cache is automatically invalidated after DML statements
• Memory usage - Cached statistics consume memory; monitor engine memory usage (however, the amount of memory required should be negligible)
• Refresh timing - First query after data modification will refresh the cache

​

Related Topics

• Aggregating Index - Learn about creating and managing aggregating indexes
• Cardinality Estimation - Understand how statistics affect query planning
• Inspecting Query Plans - Learn to analyze query execution plans
• System Settings - Complete reference of Firebolt system settings