Re: list of extended statistics on psql - Mailing list pgsql-hackers

Hi,

> I addressed it, so I keep the size of extended stats with the unit.
> 
> Changes:
> ========
>    - Use pg_size_pretty to show the size of extended stats by \dX+


I rebased the patch on the head and also added tab-completion.
Any feedback is welcome.


Preparing for tests:
===========
create table t1 (a int, b int);
create statistics stts_1 (dependencies) on a, b from t1;
create statistics stts_2 (dependencies, ndistinct) on a, b from t1;
create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from t1;

create table t2 (a int, b int, c int);
create statistics stts_4 on b, c from t2;

create table hoge (col1 int, col2 int, col3 int);
create statistics stts_hoge on col1, col2, col3 from hoge;

create schema foo;
create schema yama;
create statistics foo.stts_foo on col1, col2 from hoge;
create statistics yama.stts_yama (ndistinct, mcv) on col1, col3 from hoge;

insert into t1 select i,i from generate_series(1,100) i;
analyze t1;

Result of \dX:
==============
postgres=# \dX
                               List of extended statistics
  Schema |   Name    |         Definition         | N_distinct | Dependencies |   Mcv
--------+-----------+----------------------------+------------+--------------+---------
  foo    | stts_foo  | col1, col2 FROM hoge       | defined    | defined      | defined
  public | stts_1    | a, b FROM t1               |            | built        |
  public | stts_2    | a, b FROM t1               | built      | built        |
  public | stts_3    | a, b FROM t1               | built      | built        | built
  public | stts_4    | b, c FROM t2               | defined    | defined      | defined
  public | stts_hoge | col1, col2, col3 FROM hoge | defined    | defined      | defined
  yama   | stts_yama | col1, col3 FROM hoge       | defined    |              | defined
(7 rows)

Result of \dX+:
===============
postgres=# \dX+
                                                List of extended statistics
  Schema |   Name    |         Definition         | N_distinct | Dependencies |   Mcv   |  N_size  |  D_size  |
M_size

--------+-----------+----------------------------+------------+--------------+---------+----------+----------+------------
  foo    | stts_foo  | col1, col2 FROM hoge       | defined    | defined      | defined | 0 bytes  | 0 bytes  | 0
bytes
  public | stts_1    | a, b FROM t1               |            | built        |         |          | 40 bytes |
  public | stts_2    | a, b FROM t1               | built      | built        |         | 13 bytes | 40 bytes |
  public | stts_3    | a, b FROM t1               | built      | built        | built   | 13 bytes | 40 bytes | 6126
bytes
  public | stts_4    | b, c FROM t2               | defined    | defined      | defined | 0 bytes  | 0 bytes  | 0
bytes
  public | stts_hoge | col1, col2, col3 FROM hoge | defined    | defined      | defined | 0 bytes  | 0 bytes  | 0
bytes
  yama   | stts_yama | col1, col3 FROM hoge       | defined    |              | defined | 0 bytes  |          | 0
bytes
(7 rows)

Results of Tab-completion:
===============
postgres=# \dX <Tab>
foo.                 pg_toast.            stts_2               stts_hoge
information_schema.  public.              stts_3               yama.
pg_catalog.          stts_1               stts_4

postgres=# \dX+ <Tab>
foo.                 pg_toast.            stts_2               stts_hoge
information_schema.  public.              stts_3               yama.
pg_catalog.          stts_1               stts_4


Regards,
Tatsuro Yamada