Help Center/ Data Warehouse Service / More Documents/ SQL Syntax Reference (Kuala Lumpur Region)/ DDL Syntax/ CREATE STATISTICS
Updated on 2025-10-11 GMT+08:00
View PDF
Share

CREATE STATISTICS

Function

Creates an extended statistics object for a table in the current database. The object is owned by the user who executes the command. This syntax is supported only by clusters of version 8.2.1.200 or later.

Precautions

  • Currently, this command can only be used to create expression statistics.
  • Expression statistics cannot be created for temporary tables.
  • Expression statistics cannot be created on partitions of a table.
  • Statistics cannot be created for expressions that contain aggregate functions, subqueries, window functions, and return sets.

Syntax

1
2
3
 
CREATE STATISTICS [ IF NOT EXISTS ] statistics_name
    ON ( expression ) [, ...]
    FROM table_name

Parameter Description

Table 1 CREATE STATISTICS parameters

Parameter

Description

Value Range

statistics_name

Indicates the name of the new statistics object.

A string, which must comply with the identifier naming conventions.

expression

Indicates the expression on which statistics are collected. One or more expressions are supported. Multiple expressions are separated by commas (,).

-

table_name

Indicates the name of the table whose statistics are to be collected.

-

Examples

Creates a sample table and insert data into it.

CREATE TABLE t1_row(id int, dt timestamp) WITH (orientation = row) DISTRIBUTE BY HASH (id);
INSERT INTO t1_row values (generate_series(1, 721), generate_series('2023-01-01'::timestamp, '2023-01-31'::timestamp, '1 hour'::interval));

Create an expression statistics object for the table and run the ANALYZE command to collect statistics.

CREATE STATISTICS s1_t1_row ON EXTRACT(HOUR FROM dt), EXTRACT(DAY FROM dt) FROM t1_row;
ANALYZE t1_row;
View the query plan of the statement. 
SET enable_fast_query_shipping = OFF;
EXPLAIN VERBOSE SELECT * FROM t1_row WHERE EXTRACT(HOUR FROM dt) BETWEEN 0 AND 12;

Helpful Links

DROP STATISTICS

Parent Topic: DDL Syntax