CREATE_STATISTICS(7pg18)

✨ CREATE_STATISTICS(7) ✨

okay bestie, welcome to the CREATE STATISTICS page from the PostgreSQL 18.6 docs — this is the one where you teach your database to actually understand how your columns vibe with each other 💅

---

🌸 NAME 🌸

CREATE_STATISTICS — define extended statistics 🦋

---

✨ SYNOPSIS ✨

CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]
    ON ( expression )
    FROM table_name

CREATE STATISTICS [ [ IF NOT EXISTS ] statistics_name ]
    [ ( statistics_kind [, ... ] ) ]
    ON { column_name | ( expression ) }, { column_name | ( expression ) } [, ...]
    FROM table_name

---

💖 DESCRIPTION 💖

so CREATE STATISTICS builds you a brand-new extended statistics object — it's like a little tracker that keeps tabs on data about a table, a foreign table, or a materialized view you point it at. The object lives in the current database and it's owned by whoever ran the command. main character energy, honestly 👑

The command comes in two basic forms and they are NOT the same vibe:

1. The first form collects univariate statistics for a single expression. The glow-up here is that you get the same perks as an expression index, but without the whole "maintaining an index" overhead situation. This form does not let you pick the statistics kind, because the various statistics kinds only refer to multivariate statistics. iykyk.
2. The second form collects multivariate statistics across multiple columns and/or expressions, and you can optionally say which statistics kinds to include. Bonus: this form will also automatically cause univariate statistics to be collected on any expressions in the list. she does it for you, no notes 💅

If you give a schema name (like, say, CREATE STATISTICS myschema.mystat ...), the statistics object gets created in that schema. If you don't, it lands in the current schema. And if you do name it? That name has to be distinct from every other statistics object in the same schema. no duplicates allowed bestie, that's the rule 🚫

---

🎀 PARAMETERS 🎀

• IF NOT EXISTS — don't throw an error if a statistics object with that same name already exists. Instead you just get a notice. ⚠️ Heads up though: only the name of the statistics object is considered here, not the details of its definition. Also, the statistics name is required when IF NOT EXISTS is specified.
• statistics_name — the name (optionally schema-qualified) of the statistics object you're creating. If you leave the name out, PostgreSQL picks a suitable one for you based on the parent table's name plus the defined column name(s) and/or expression(s). she's thoughtful like that 🫶
• statistics_kind — a multivariate statistics kind to be computed in this statistics object. The currently supported kinds are:
  • ndistinct — enables n-distinct statistics ✨
  • dependencies — enables functional dependency statistics 🔗
  • mcv — enables most-common values lists 📊

If you skip this clause entirely, all supported statistics kinds get included. Univariate expression statistics are built automatically if the statistics definition includes any complex expressions rather than just simple column references. Want the deep lore? See Section 14.2.2 and Section 69.2.

• column_name — the name of a table column to be covered by the computed statistics. This is only allowed when you're building multivariate statistics. At least two column names or expressions must be specified, and their order is not significant. it's giving "we don't do favorites" 💜
• expression — an expression to be covered by the computed statistics. You can use this to build univariate statistics on a single expression, or as part of a list of multiple column names and/or expressions to build multivariate statistics. In that latter case, separate univariate statistics get built automatically for each expression in the list. 🧚
• table_name — the name (optionally schema-qualified) of the table containing the column(s) the statistics are computed on. For the whole explanation of how inheritance and partitions are handled, go peek at ANALYZE(7). 🍓

---

🌈 NOTES 🌈

You have to be the owner of a table to create a statistics object that reads it. But here's the plot twist: once it's created, the ownership of the statistics object is independent of the underlying table(s). she's her own woman now 💅

Expression statistics are per-expression and they're similar to creating an index on the expression — except they avoid the overhead of index maintenance. Expression statistics get built automatically for each expression in the statistics object definition. obsessed. ✨

⚠️ lowkey important: extended statistics are not currently used by the planner for selectivity estimations made for table joins. This limitation will likely be removed in a future version of PostgreSQL. so don't come for her, she's working on it 😭

---

🔥 EXAMPLES 🔥

🍓 Example the first: functional dependency besties

Create table t1 with two functionally dependent columns — meaning, knowing a value in the first column is enough to determine the value in the other column. Then functional dependency statistics get built on those columns:

CREATE TABLE t1 (
    a   int,
    b   int
);

INSERT INTO t1 SELECT i/100, i/500
                 FROM generate_series(1,1000000) s(i);

ANALYZE t1;

-- the number of matching rows will be drastically underestimated:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);

CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;

ANALYZE t1;

-- now the row count estimate is more accurate:
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);

Without functional-dependency statistics, the planner would assume the two WHERE conditions are independent and multiply their selectivities together, arriving at a much-too-small row count estimate. the audacity 😤 With such statistics, the planner recognizes the WHERE conditions are redundant and does not underestimate the row count. slay.

💜 Example the second: perfectly correlated columns + MCV

Create table t2 with two perfectly correlated columns (they contain identical data), plus an MCV list on those columns:

CREATE TABLE t2 (
    a   int,
    b   int
);

INSERT INTO t2 SELECT mod(i,100), mod(i,100)
                 FROM generate_series(1,1000000) s(i);

CREATE STATISTICS s2 (mcv) ON a, b FROM t2;

ANALYZE t2;

-- valid combination (found in MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);

-- invalid combination (not found in MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);

The MCV list hands the planner way more detailed info about the specific values that commonly show up in the table, plus an upper bound on the selectivities of combinations of values that don't appear in the table — so it can generate better estimates in both cases. ate and left no crumbs 🔥

🦋 Example the third: expressions + ndistinct

Create table t3 with a single timestamp column, then run queries using expressions on that column. Without extended statistics, the planner has no information about the data distribution for the expressions and just uses default estimates. It also doesn't realize that the value of the date truncated to the month is fully determined by the value of the date truncated to the day. 👀 Then expression and ndistinct statistics get built on those two expressions:

CREATE TABLE t3 (
    a   timestamp
);

INSERT INTO t3 SELECT i FROM generate_series('2020-01-01'::timestamp,
                                             '2020-12-31'::timestamp,
                                             '1 minute'::interval) s(i);

ANALYZE t3;

-- the number of matching rows will be drastically underestimated:
EXPLAIN ANALYZE SELECT * FROM t3
  WHERE date_trunc('month', a) = '2020-01-01'::timestamp;

EXPLAIN ANALYZE SELECT * FROM t3
  WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
                                 AND '2020-06-30'::timestamp;

EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
   FROM t3 GROUP BY 1, 2;

-- build ndistinct statistics on the pair of expressions (per-expression
-- statistics are built automatically)
CREATE STATISTICS s3 (ndistinct) ON date_trunc('month', a), date_trunc('day', a) FROM t3;

ANALYZE t3;

-- now the row count estimates are more accurate:
EXPLAIN ANALYZE SELECT * FROM t3
  WHERE date_trunc('month', a) = '2020-01-01'::timestamp;

EXPLAIN ANALYZE SELECT * FROM t3
  WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
                                 AND '2020-06-30'::timestamp;

EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
   FROM t3 GROUP BY 1, 2;

Without expression and ndistinct statistics, the planner has no info about the number of distinct values for the expressions and has to rely on default estimates. The equality and range conditions are assumed to have 0.5% selectivity, and the number of distinct values in the expression is assumed to be the same as for the column (i.e. unique). This results in a significant underestimate of the row count in the first two queries. 💀

Moreover, the planner has no information about the relationship between the expressions, so it assumes the two WHERE and GROUP BY conditions are independent and multiplies their selectivities together — arriving at a severe overestimate of the group count in the aggregate query. And it gets even worse: the lack of accurate statistics for the expressions forces the planner to use a default ndistinct estimate for the expression derived from ndistinct for the column. 😭 With such statistics, the planner recognizes the conditions are correlated and arrives at much more accurate estimates. iconic behavior, truly.

---

👑 COMPATIBILITY 👑

There is no CREATE STATISTICS command in the SQL standard. she's built different, not standardized 💅

---

🌟 SEE ALSO 🌟

• ALTER STATISTICS (ALTER_STATISTICS(7))
• DROP STATISTICS (DROP_STATISTICS(7))

---

and that's the whole page bestie!! 🎀 this was PostgreSQL 18.6, dated 2026, and it's the CREATE STATISTICS(7) manual — go forth and give your planner the info it deserves, no cap 💖✨🦋🌸🫶