CREATE STATISTICS
An SQL command for creating an extended statistics object
CREATE STATISTICS is a DDL command for creating an extended statistics object.
CREATE STATISTICS was added in PostgreSQL 10.
Change history
- PostgreSQL 16
- the statistics name is now optional (commit 624aa2a1)
- PostgreSQL 14
- support for extended statistics on expressions added (commit a4d75c86)
- PostgreSQL 10
- added (commit 7b504eb2)
Examples
Usage example for CREATE STATISTICS using functional dependency statistics (here with max_parallel_workers_per_gather set to 0):
postgres=# CREATE TABLE foo (c1 INT, c2 INT);
CREATE TABLE
postgres=# INSERT INTO foo
SELECT i/100, i/500
FROM generate_series(1,1000000) s(i);
INSERT 0 1000000
postgres=# ANALYZE foo;
ANALYZE
postgres=# EXPLAIN ANALYZE SELECT * FROM foo WHERE (c1 = 1) AND (c2 = 0);
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..19425.00 rows=1 width=8) (actual time=83.332..83.333 rows=0 loops=1)
Filter: ((c1 = 1) AND (c2 = 0))
Rows Removed by Filter: 1000000
Planning Time: 0.162 ms
Execution Time: 83.346 ms
(5 rows)
After creating a statistics object:
postgres=# CREATE STATISTICS s1 (dependencies) ON c1, c2 FROM foo; CREATE STATISTICS postgres=# ANALYZE foo; ANALYZE
the planner arrives at a much closer rowcount estimate:
postgres=# EXPLAIN ANALYZE SELECT * FROM foo WHERE (c1 = 1) AND (c2 = 0);
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..19425.00 rows=99 width=8) (actual time=71.801..71.801 rows=0 loops=1)
Filter: ((c1 = 1) AND (c2 = 2))
Rows Removed by Filter: 1000000
Planning Time: 0.145 ms
Execution Time: 71.815 ms
(5 rows)
postgres=# SELECT count(*) FROM foo WHERE (c1 = 1) AND (c2 = 0);
count
-------
100
(1 row)
References
- PostgreSQL documentation: CREATE STATISTICS
Useful links
- PostgreSQL: CREATE STATISTICS – advanced query optimization - February 2021 blog article by Hans-Jürgen Schönig / CyberTec
- The Postgres 10 feature you didn't know about: CREATE STATISTICS - March 2018 blog article by Samay Sharma / CitusData