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

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)

Categories

DDL, Performance, Planner, SQL command

See also

ALTER STATISTICS, DROP STATISTICS, pg_stats_ext, pg_stats_ext_exprs