Subject: [PATCH 1/2] psql \dX: list extended statistics objects

From: Tatsuro Yamada <[email-masked]>
Date: Thu, 7 Jan 2021 14:28:20 +0900

The new command lists extended statistics objects, possibly with their
sizes. All past releases with extended statistics are supported.

Author: Tatsuro Yamada
Reviewed-by: Julien Rouhaud, Alvaro Herrera, Tomas Vondra
Discussion: https://postgr.es/m/c027a541-5856-75a5-0868-341301e1624b%40nttcom.co.jp_1
---
 doc/src/sgml/ref/psql-ref.sgml          |  14 +++
 src/bin/psql/command.c                  |   3 +
 src/bin/psql/describe.c                 | 150 ++++++++++++++++++++++++
 src/bin/psql/describe.h                 |   3 +
 src/bin/psql/help.c                     |   1 +
 src/bin/psql/tab-complete.c             |   4 +-
 src/test/regress/expected/stats_ext.out |  94 +++++++++++++++
 src/test/regress/sql/stats_ext.sql      |  31 +++++
 8 files changed, 299 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 221a967bfe..d01acc92b8 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1918,6 +1918,20 @@ testdb=&gt;
         </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 303e7c3ad8..c5ebc1c3f4 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -928,6 +928,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 caf97563f4..46f54199fb 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4392,6 +4392,156 @@ listEventTriggers(const char *pattern, bool verbose)
 	return true;
 }
 
+/*
+ * \dX
+ *
+ * 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\"",
+					  gettext_noop("Schema"),
+					  gettext_noop("Name"),
+					  gettext_noop("Definition"));
+
+	/*
+	 * Since 12 there are two catalogs - one for the definition, one for the
+	 * data built by ANALYZE. Older releases use a single catalog. Also, 12
+	 * adds the MCV statistics kind.
+	 */
+	if (pset.sversion < 120000)
+	{
+		appendPQExpBuffer(&buf,
+						  ",\nCASE WHEN es.stxndistinct IS NOT NULL THEN 'built' \n"
+						  "     WHEN 'd' = any(es.stxkind) THEN 'defined' \n"
+						  "END AS \"%s\", \n"
+						  "CASE WHEN es.stxdependencies IS NOT NULL THEN 'built' \n"
+						  "     WHEN 'f' = any(es.stxkind) THEN 'defined' \n"
+						  "END AS \"%s\"",
+						  gettext_noop("Ndistinct"),
+						  gettext_noop("Dependencies"));
+	}
+	else
+	{
+		appendPQExpBuffer(&buf,
+						  ",\nCASE 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("Ndistinct"),
+						  gettext_noop("Dependencies"),
+						  gettext_noop("MCV"));
+	}
+
+	/* In verbose mode, print sizes of the extended statistics objects. */
+	if (verbose)
+	{
+		if (pset.sversion < 120000)
+		{
+			appendPQExpBuffer(&buf,
+							  ",\nCASE WHEN es.stxndistinct IS NOT NULL THEN \n"
+							  "          pg_catalog.pg_size_pretty(pg_catalog.length(es.stxndistinct)::bigint) \n"
+							  "     WHEN 'd' = any(es.stxkind) THEN '0 bytes' \n"
+							  "END AS \"%s\", \n"
+							  "CASE WHEN es.stxdependencies IS NOT NULL THEN \n"
+							  "          pg_catalog.pg_size_pretty(pg_catalog.length(es.stxdependencies)::bigint) \n"
+							  "     WHEN 'f' = any(es.stxkind) THEN '0 bytes' \n"
+							  "END AS \"%s\"",
+							  gettext_noop("Ndistinct_size"),
+							  gettext_noop("Dependencies_size"));
+		}
+		else
+		{
+			appendPQExpBuffer(&buf,
+							  ",\nCASE WHEN esd.stxdndistinct IS NOT NULL THEN \n"
+							  "          pg_catalog.pg_size_pretty(pg_catalog.length(esd.stxdndistinct)::bigint) \n"
+							  "     WHEN 'd' = any(es.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(esd.stxddependencies)::bigint) \n"
+							  "     WHEN 'f' = any(es.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(esd.stxdmcv)::bigint) \n"
+							  "     WHEN 'm' = any(es.stxkind) THEN '0 bytes' \n"
+							  "END AS \"%s\"",
+							  gettext_noop("Ndistinct_size"),
+							  gettext_noop("Dependencies_size"),
+							  gettext_noop("MCV_size"));
+		}
+	}
+
+	if (pset.sversion < 120000)
+	{
+		appendPQExpBufferStr(&buf,
+							 " \nFROM pg_catalog.pg_statistic_ext es \n"
+							 "INNER JOIN pg_catalog.pg_class c \n"
+							 "ON es.stxrelid = c.oid \n");
+	}
+	else
+	{
+		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 6044e3a082..867e57d851 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 9ec1c4e810..e42bc8c54e 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/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 9dcab0d2fa..611f1efb15 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1500,7 +1500,7 @@ psql_completion(const char *text, int start, int end)
 		"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
 		"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
 		"\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS",
-		"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
+		"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
 		"\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding",
 		"\\endif", "\\errverbose", "\\ev",
 		"\\f",
@@ -3910,6 +3910,8 @@ psql_completion(const char *text, int start, int end)
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
 	else if (TailMatchesCS("\\dx*"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_extensions);
+	else if (TailMatchesCS("\\dX*"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_statistics, NULL);
 	else if (TailMatchesCS("\\dm*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
 	else if (TailMatchesCS("\\dE*"))
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 7bfeaf85f0..8c8a0afcf6 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -1725,6 +1725,100 @@ INSERT INTO tststats.priv_test_tbl
 CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
   FROM tststats.priv_test_tbl;
 ANALYZE tststats.priv_test_tbl;
+-- Check printing info about extended statistics by \dX
+create table stts_t1 (a int, b int);
+create statistics stts_1 (ndistinct) on a, b from stts_t1;
+create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;
+create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;
+create table stts_t2 (a int, b int, c int);
+create statistics stts_4 on b, c from stts_t2;
+create table stts_t3 (col1 int, col2 int, col3 int);
+create statistics stts_hoge on col1, col2, col3 from stts_t3;
+create schema stts_s1;
+create schema stts_s2;
+create statistics stts_s1.stts_foo on col1, col2 from stts_t3;
+create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from stts_t3;
+insert into stts_t1 select i,i from generate_series(1,100) i;
+analyze stts_t1;
+\dX
+                                          List of extended statistics
+  Schema  |          Name          |              Definition              | Ndistinct | Dependencies |   MCV   
+----------+------------------------+--------------------------------------+-----------+--------------+---------
+ public   | func_deps_stat         | a, b, c FROM functional_dependencies |           | built        | 
+ public   | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays        |           |              | built
+ public   | mcv_lists_bool_stats   | a, b, c FROM mcv_lists_bool          |           |              | built
+ public   | mcv_lists_stats        | a, b, d FROM mcv_lists               |           |              | built
+ public   | stts_1                 | a, b FROM stts_t1                    | built     |              | 
+ public   | stts_2                 | a, b FROM stts_t1                    | built     | built        | 
+ public   | stts_3                 | a, b FROM stts_t1                    | built     | built        | built
+ public   | stts_4                 | b, c FROM stts_t2                    | defined   | defined      | defined
+ public   | stts_hoge              | col1, col2, col3 FROM stts_t3        | defined   | defined      | defined
+ stts_s1  | stts_foo               | col1, col2 FROM stts_t3              | defined   | defined      | defined
+ stts_s2  | stts_yama              | col1, col3 FROM stts_t3              |           | defined      | defined
+ tststats | priv_test_stats        | a, b FROM tststats.priv_test_tbl     |           |              | built
+(12 rows)
+
+\dX stts_?
+                       List of extended statistics
+ Schema |  Name  |    Definition     | Ndistinct | Dependencies |   MCV   
+--------+--------+-------------------+-----------+--------------+---------
+ public | stts_1 | a, b FROM stts_t1 | built     |              | 
+ public | stts_2 | a, b FROM stts_t1 | built     | built        | 
+ public | stts_3 | a, b FROM stts_t1 | built     | built        | built
+ public | stts_4 | b, c FROM stts_t2 | defined   | defined      | defined
+(4 rows)
+
+\dX *stts_hoge
+                               List of extended statistics
+ Schema |   Name    |          Definition           | Ndistinct | Dependencies |   MCV   
+--------+-----------+-------------------------------+-----------+--------------+---------
+ public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined   | defined      | defined
+(1 row)
+
+\dX+
+                                                                   List of extended statistics
+  Schema  |          Name          |              Definition              | Ndistinct | Dependencies |   MCV   | Ndistinct_size | Dependencies_size |  MCV_size  
+----------+------------------------+--------------------------------------+-----------+--------------+---------+----------------+-------------------+------------
+ public   | func_deps_stat         | a, b, c FROM functional_dependencies |           | built        |         |                | 106 bytes         | 
+ public   | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays        |           |              | built   |                |                   | 24 kB
+ public   | mcv_lists_bool_stats   | a, b, c FROM mcv_lists_bool          |           |              | built   |                |                   | 386 bytes
+ public   | mcv_lists_stats        | a, b, d FROM mcv_lists               |           |              | built   |                |                   | 294 bytes
+ public   | stts_1                 | a, b FROM stts_t1                    | built     |              |         | 13 bytes       |                   | 
+ public   | stts_2                 | a, b FROM stts_t1                    | built     | built        |         | 13 bytes       | 40 bytes          | 
+ public   | stts_3                 | a, b FROM stts_t1                    | built     | built        | built   | 13 bytes       | 40 bytes          | 6126 bytes
+ public   | stts_4                 | b, c FROM stts_t2                    | defined   | defined      | defined | 0 bytes        | 0 bytes           | 0 bytes
+ public   | stts_hoge              | col1, col2, col3 FROM stts_t3        | defined   | defined      | defined | 0 bytes        | 0 bytes           | 0 bytes
+ stts_s1  | stts_foo               | col1, col2 FROM stts_t3              | defined   | defined      | defined | 0 bytes        | 0 bytes           | 0 bytes
+ stts_s2  | stts_yama              | col1, col3 FROM stts_t3              |           | defined      | defined |                | 0 bytes           | 0 bytes
+ tststats | priv_test_stats        | a, b FROM tststats.priv_test_tbl     |           |              | built   |                |                   | 686 bytes
+(12 rows)
+
+\dX+ stts_?
+                                                List of extended statistics
+ Schema |  Name  |    Definition     | Ndistinct | Dependencies |   MCV   | Ndistinct_size | Dependencies_size |  MCV_size  
+--------+--------+-------------------+-----------+--------------+---------+----------------+-------------------+------------
+ public | stts_1 | a, b FROM stts_t1 | built     |              |         | 13 bytes       |                   | 
+ public | stts_2 | a, b FROM stts_t1 | built     | built        |         | 13 bytes       | 40 bytes          | 
+ public | stts_3 | a, b FROM stts_t1 | built     | built        | built   | 13 bytes       | 40 bytes          | 6126 bytes
+ public | stts_4 | b, c FROM stts_t2 | defined   | defined      | defined | 0 bytes        | 0 bytes           | 0 bytes
+(4 rows)
+
+\dX+ *stts_hoge
+                                                       List of extended statistics
+ Schema |   Name    |          Definition           | Ndistinct | Dependencies |   MCV   | Ndistinct_size | Dependencies_size | MCV_size 
+--------+-----------+-------------------------------+-----------+--------------+---------+----------------+-------------------+----------
+ public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined   | defined      | defined | 0 bytes        | 0 bytes           | 0 bytes
+(1 row)
+
+\dX+ stts_s2.stts_yama
+                                                    List of extended statistics
+ Schema  |   Name    |       Definition        | Ndistinct | Dependencies |   MCV   | Ndistinct_size | Dependencies_size | MCV_size 
+---------+-----------+-------------------------+-----------+--------------+---------+----------------+-------------------+----------
+ stts_s2 | stts_yama | col1, col3 FROM stts_t3 |           | defined      | defined |                | 0 bytes           | 0 bytes
+(1 row)
+
+drop table stts_t1, stts_t2, stts_t3;
+drop schema stts_s1, stts_s2 cascade;
 -- User with no access
 CREATE USER regress_stats_user1;
 GRANT USAGE ON SCHEMA tststats TO regress_stats_user1;
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 7912e733ae..db6e3e1ba3 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -912,6 +912,37 @@ CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
 
 ANALYZE tststats.priv_test_tbl;
 
+-- Check printing info about extended statistics by \dX
+create table stts_t1 (a int, b int);
+create statistics stts_1 (ndistinct) on a, b from stts_t1;
+create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;
+create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;
+
+create table stts_t2 (a int, b int, c int);
+create statistics stts_4 on b, c from stts_t2;
+
+create table stts_t3 (col1 int, col2 int, col3 int);
+create statistics stts_hoge on col1, col2, col3 from stts_t3;
+
+create schema stts_s1;
+create schema stts_s2;
+create statistics stts_s1.stts_foo on col1, col2 from stts_t3;
+create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from stts_t3;
+
+insert into stts_t1 select i,i from generate_series(1,100) i;
+analyze stts_t1;
+
+\dX
+\dX stts_?
+\dX *stts_hoge
+\dX+
+\dX+ stts_?
+\dX+ *stts_hoge
+\dX+ stts_s2.stts_yama
+
+drop table stts_t1, stts_t2, stts_t3;
+drop schema stts_s1, stts_s2 cascade;
+
 -- User with no access
 CREATE USER regress_stats_user1;
 GRANT USAGE ON SCHEMA tststats TO regress_stats_user1;
-- 
2.26.2

--------------B0278A9E952F6668D2DBEBA1
Content-Type: text/x-patch; charset=UTF-8;
 name="0002-fixup-rename-defined-to-requested-20210109.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="0002-fixup-rename-defined-to-requested-20210109.patch"