Hi!
I created a POC patch that allows showing a list of extended statistics by
"\dz" command on psql. I believe this feature helps DBA and users who
would like to know all extended statistics easily. :-D
I have not a strong opinion to assign "\dz". I prefer "\dx" or "\de*"
than "\dz" but they were already assigned. Therefore I used "\dz"
instead of them.
Please find the attached patch.
Any comments are welcome!
For Example:
=======================
CREATE TABLE t1 (a INT, b INT);
CREATE STATISTICS stts1 (dependencies) ON a, b FROM t1;
CREATE STATISTICS stts2 (dependencies, ndistinct) ON a, b FROM t1;
CREATE STATISTICS stts3 (dependencies, ndistinct, mcv) ON a, b FROM t1;
ANALYZE t1;
CREATE TABLE t2 (a INT, b INT, c INT);
CREATE STATISTICS stts4 ON b, c FROM t2;
ANALYZE t2;
postgres=# \dz
List of extended statistics
Schema | Table | Name | Columns | Ndistinct | Dependencies | MCV
--------+-------+-------+---------+-----------+--------------+-----
public | t1 | stts1 | a, b | f | t | f
public | t1 | stts2 | a, b | t | t | f
public | t1 | stts3 | a, b | t | t | t
public | t2 | stts4 | b, c | t | t | t
(4 rows)
postgres=# \?
...
\dy [PATTERN] list event triggers
\dz [PATTERN] list extended statistics
\l[+] [PATTERN] list databases
...
=======================
For now, I haven't written a document and regression test for that.
I'll create it later.
Thanks,
Tatsuro Yamada
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 9902a4a..dc36c98 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -932,6 +932,9 @@ exec_command_d(PsqlScanState scan_state, bool
active_branch, const char *cmd)
case 'y': /* Event Triggers */
success = listEventTriggers(pattern,
show_verbose);
break;
+ case 'z': /* Extended Statistics
*/
+ success = listExtendedStats(pattern);
+ break;
default:
status = PSQL_CMD_UNKNOWN;
}
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d81f157..8128b1c 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4368,6 +4368,67 @@ listEventTriggers(const char *pattern, bool verbose)
}
/*
+ * \dz
+ *
+ * Briefly describes extended statistics.
+ */
+bool
+listExtendedStats(const char *pattern)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ if (pset.sversion < 10000)
+ {
+ 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 "
+
"stxnamespace::pg_catalog.regnamespace AS \"%s\", "
+ "stxrelid::pg_catalog.regclass AS
\"%s\", "
+ "stxname AS \"%s\", "
+ "(SELECT
pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ') "
+ "FROM pg_catalog.unnest(stxkeys)
s(attnum) "
+ "JOIN pg_catalog.pg_attribute a ON
(stxrelid = a.attrelid AND "
+ "a.attnum = s.attnum AND NOT
attisdropped)) AS \"%s\", "
+ "'d' = any(stxkind) AS \"%s\", "
+ "'f' = any(stxkind) AS \"%s\", "
+ "'m' = any(stxkind) AS \"%s\" "
+ "FROM pg_catalog.pg_statistic_ext
stat ",
+ gettext_noop("Schema"),
+ gettext_noop("Table"),
+ gettext_noop("Name"),
+ gettext_noop("Columns"),
+ gettext_noop("Ndistinct"),
+ gettext_noop("Dependencies"),
+ gettext_noop("MCV"));
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3, 4;");
+
+ 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
*
* Describes casts.
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index f0e3ec9..6c16947 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -105,6 +105,9 @@ extern bool listExtensionContents(const char *pattern);
/* \dy */
extern bool listEventTriggers(const char *pattern, bool verbose);
+/* \dz */
+extern bool listExtendedStats(const char *pattern);
+
/* \dRp */
bool listPublications(const char *pattern);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index af82928..78b30f2 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -268,6 +268,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
fprintf(output, _(" \\dx[+] [PATTERN] list extensions\n"));
fprintf(output, _(" \\dy [PATTERN] list event triggers\n"));
+ fprintf(output, _(" \\dz [PATTERN] list extended
statistics\n"));
fprintf(output, _(" \\l[+] [PATTERN] list databases\n"));
fprintf(output, _(" \\sf[+] FUNCNAME show a function's
definition\n"));
fprintf(output, _(" \\sv[+] VIEWNAME show a view's
definition\n"));
