Hi Tomas,
On 2020/10/29 4:06, Tomas Vondra wrote:
On Wed, Oct 28, 2020 at 03:07:56PM +0900, Tatsuro Yamada wrote:
Hi Michael-san and Hackers,
On 2020/09/30 15:19, Michael Paquier wrote:
On Thu, Sep 17, 2020 at 02:55:31PM +0900, Michael Paquier wrote:
Could you provide at least a rebased version of the patch? The CF bot
is complaning here.
Not seeing this answered after two weeks, I have marked the patch as
RwF for now.
--
Michael
Sorry for the delayed reply.
I re-based the patch on the current head and did some
refactoring.
I think the size of extended stats are not useful for DBA.
Should I remove it?
I think it's an interesting / useful information, I'd keep it (in the
\dX+ output only, of course). But I think it needs to print the size
similarly to \d+, i.e. using pg_size_pretty - that'll include the unit
and make it more readable for large stats.
Thanks for your comment.
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+
Result of \dX+:
===============
Schema | Name | Definition | N_distinct | Dependencies | Mcv
| N_Size | D_Size | M_Size
-------------+------------+-----------------+------------+--------------+---------+----------+----------+------------
hoge1schema | hoge1_ext | a, b FROM hoge1 | built | built | built
| 13 bytes | 40 bytes | 6126 bytes
public | hoge1_ext1 | a, b FROM hoge1 | defined | defined |
defined | 0 bytes | 0 bytes | 0 bytes
public | hoge1_ext2 | a, b FROM hoge1 | defined | |
| 0 bytes | |
(3 rows)
Please find the attached patch.
Regards,
Tatsuro Yamada
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 221a967bfe..fd860776af 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1918,6 +1918,20 @@ testdb=>
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>\dX [ <link linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists extended statistics.
+ If <replaceable class="parameter">pattern</replaceable>
+ is specified, only those extended statistics whose names match the
pattern
+ are listed.
+ If <literal>+</literal> is appended to the command name, each extended
statistics
+ is listed with its size.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><literal>\dy[+] [ <link linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link> ]</literal></term>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index c7a83d5dfc..c6f1653cb7 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -930,6 +930,9 @@ exec_command_d(PsqlScanState scan_state, bool
active_branch, const char *cmd)
else
success = listExtensions(pattern);
break;
+ case 'X': /* Extended Statistics
*/
+ success = listExtendedStats(pattern,
show_verbose);
+ break;
case 'y': /* Event Triggers */
success = listEventTriggers(pattern,
show_verbose);
break;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 07d640021c..1807324542 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4397,6 +4397,106 @@ listEventTriggers(const char *pattern, bool verbose)
return true;
}
+/*
+ * \dX
+ *
+ * Briefly describes extended statistics.
+ */
+bool
+listExtendedStats(const char *pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ if (pset.sversion < 100000)
+ {
+ char sverbuf[32];
+
+ pg_log_error("The server (version %s) does not support extended
statistics.",
+ formatPGVersionNumber(pset.sversion,
false,
+
sverbuf, sizeof(sverbuf)));
+ return true;
+ }
+
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT \n"
+
"es.stxnamespace::pg_catalog.regnamespace::text AS \"%s\", \n"
+ "es.stxname AS \"%s\", \n"
+ "pg_catalog.format('%%s FROM %%s', \n"
+ " (SELECT
pg_catalog.string_agg(pg_catalog.quote_ident(a.attname),', ') \n"
+ " FROM
pg_catalog.unnest(es.stxkeys) s(attnum) \n"
+ " JOIN pg_catalog.pg_attribute a \n"
+ " ON (es.stxrelid = a.attrelid \n"
+ " AND a.attnum = s.attnum \n"
+ " AND NOT a.attisdropped)), \n"
+ "es.stxrelid::regclass) AS \"%s\", \n"
+ "CASE WHEN esd.stxdndistinct IS NOT
NULL THEN 'built' \n"
+ " WHEN 'd' = any(es.stxkind) THEN
'defined' \n"
+ "END AS \"%s\", \n"
+ "CASE WHEN esd.stxddependencies IS
NOT NULL THEN 'built' \n"
+ " WHEN 'f' = any(es.stxkind) THEN
'defined' \n"
+ "END AS \"%s\", \n"
+ "CASE WHEN esd.stxdmcv IS NOT NULL
THEN 'built' \n"
+ " WHEN 'm' = any(es.stxkind) THEN
'defined' \n"
+ "END AS \"%s\"",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Definition"),
+ gettext_noop("N_distinct"),
+ gettext_noop("Dependencies"),
+ gettext_noop("Mcv"));
+
+ if (verbose)
+ {
+ appendPQExpBuffer(&buf,
+ ",\nCASE WHEN
esd.stxdndistinct IS NOT NULL THEN \n"
+ "
pg_catalog.pg_size_pretty(pg_catalog.length(stxdndistinct)::bigint) \n"
+ " WHEN 'd' = any(stxkind)
THEN '0 bytes' \n"
+ "END AS \"%s\", \n"
+ "CASE WHEN
esd.stxddependencies IS NOT NULL THEN \n"
+ "
pg_catalog.pg_size_pretty(pg_catalog.length(stxddependencies)::bigint) \n"
+ " WHEN 'f' = any(stxkind)
THEN '0 bytes' \n"
+ "END AS \"%s\", \n"
+ "CASE WHEN esd.stxdmcv IS NOT
NULL THEN \n"
+ "
pg_catalog.pg_size_pretty(pg_catalog.length(stxdmcv)::bigint) \n"
+ " WHEN 'm' = any(stxkind)
THEN '0 bytes' \n"
+ "END AS \"%s\" ",
+ gettext_noop("N_size"),
+ gettext_noop("D_size"),
+ gettext_noop("M_size"));
+ }
+
+ appendPQExpBufferStr(&buf,
+ " \nFROM
pg_catalog.pg_statistic_ext es \n"
+ "LEFT JOIN
pg_catalog.pg_statistic_ext_data esd \n"
+ "ON es.oid = esd.stxoid \n"
+ "INNER JOIN
pg_catalog.pg_class c \n"
+ "ON es.stxrelid = c.oid \n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, false,
+ false, NULL,
+ "stxname", NULL,
+ NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of extended statistics");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
/*
* \dC
*
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index f0e3ec957c..89b13c3f0c 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -102,6 +102,9 @@ extern bool listExtensions(const char *pattern);
/* \dx+ */
extern bool listExtensionContents(const char *pattern);
+/* \dX */
+extern bool listExtendedStats(const char *pattern, bool verbose);
+
/* \dy */
extern bool listEventTriggers(const char *pattern, bool verbose);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index af829282e6..ea249bf96d 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -267,6 +267,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\du[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
fprintf(output, _(" \\dx[+] [PATTERN] list extensions\n"));
+ fprintf(output, _(" \\dX[+] [PATTERN] list extended
statistics\n"));
fprintf(output, _(" \\dy [PATTERN] list event triggers\n"));
fprintf(output, _(" \\l[+] [PATTERN] list databases\n"));
fprintf(output, _(" \\sf[+] FUNCNAME show a function's
definition\n"));
diff --git a/src/test/regress/expected/psql.out
b/src/test/regress/expected/psql.out
index daac0ff49d..701f2dbe59 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5072,3 +5072,80 @@ List of access methods
hash | uuid_ops | uuid | uuid |
2 | uuid_hash_extended
(5 rows)
+-- check printing info about extended statistics
+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;
+\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)
+
+\dX stts_?
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv
+--------+--------+--------------+------------+--------------+---------
+ 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
+(4 rows)
+
+\dX *hoge
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies |
Mcv
+--------+-----------+----------------------------+------------+--------------+---------
+ public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined |
defined
+(1 row)
+
+\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)
+
+\dX+ stts_?
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv |
N_size | D_size | M_size
+--------+--------+--------------+------------+--------------+---------+----------+----------+------------
+ 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
+(4 rows)
+
+\dX+ *hoge
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies |
Mcv | N_size | D_size | M_size
+--------+-----------+----------------------------+------------+--------------+---------+---------+---------+---------
+ public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined |
defined | 0 bytes | 0 bytes | 0 bytes
+(1 row)
+
+drop table t1, t2, hoge;
+drop schema foo, yama;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 47b28d2a07..dc9f4b2159 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1225,3 +1225,32 @@ drop role regress_partitioning_role;
\dAo * pg_catalog.jsonb_path_ops
\dAp+ btree float_ops
\dAp * pg_catalog.uuid_ops
+
+-- check printing info about extended statistics
+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;
+
+\dX
+\dX stts_?
+\dX *hoge
+\dX+
+\dX+ stts_?
+\dX+ *hoge
+drop table t1, t2, hoge;
+drop schema foo, yama;
