On 11st of September 2020, Alvaro Herrera committed patch:
psql: Display stats target of extended statistics The stats target can be set since commit d06215d03, but wasn't shown by psql. Author: Justin Pryzby <firstname.lastname@example.org> Discussion: https://postgr.es/m/20200831050047.GG5450@telsasoft.com Reviewed-by: Georgios Kokolatos <email@example.com> Reviewed-by: Tatsuro Yamada <firstname.lastname@example.org>
Since PostgreSQL 10 we have so called extended statistics. It's a way to tell PostgreSQL that we expect certain connections between columns in single table.
Before that we only had statistics about data in single columns, but not, we can have multiple columns bundled. This can be helpful when, for example, one of your columns depends on value of another, or there are special sets of values that happen very often.
Let's consider table like this:
=$ CREATE TABLE test ( id int8 generated always AS IDENTITY, col_a int8, col_b int8, PRIMARY KEY (id) ); CREATE TABLE
Now, let's insert some rows to it, in a way that will generate correlation between col_a and col_b:
=$ INSERT INTO test (col_a, col_b) SELECT i, i * 100 + random() * 15 FROM generate_series(1,1000) i; INSERT 0 1000
Let's make appropriate statistic, to make planner aware of this:
=$ CREATE STATISTICS sttest (dependencies) ON col_a, col_b FROM test; CREATE STATISTICS
And now, we can immediately see, in \d output that such statistics have been added:
=$ \d test TABLE "public.test" COLUMN | TYPE | Collation | NULLABLE | DEFAULT --------+--------+-----------+----------+------------------------------ id | BIGINT | | NOT NULL | generated always AS IDENTITY col_a | BIGINT | | | col_b | BIGINT | | | Indexes: "test_pkey" PRIMARY KEY, btree (id) Statistics objects: "public"."sttest" (dependencies) ON col_a, col_b FROM test
Similar thing will happen for other types of statistics.
This, of course, doesn't bring new functionality – information about statistics was available in system catalogs, but it's good to have it easily shown in standard \d.
Thanks to all involved.