Waiting for PostgreSQL 13 – psql: Display stats target of extended statistics

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
Author: Justin Pryzby <justin@telsasoft.com>
Discussion: https://postgr.es/m/20200831050047.GG5450@telsasoft.com
Reviewed-by: Georgios Kokolatos <gkokolatos@protonmail.com>
Reviewed-by: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>

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)

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;

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 |           |          | 
    "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.

One thought on “Waiting for PostgreSQL 13 – psql: Display stats target of extended statistics”

  1. Your example is not complete, you must change default statistic to display “STATISTICS XXX” 😉

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.