Hi,
On 2021/01/08 0:56, Tomas Vondra wrote:
On 1/7/21 3:47 PM, Alvaro Herrera wrote:
On 2021-Jan-07, Tomas Vondra wrote:
On 1/7/21 1:46 AM, Tatsuro Yamada wrote:
I overlooked the check for MCV in the logic building query
because I created the patch as a new feature on PG14.
I'm not sure whether we should do back patch or not. However, I'll
add the check on the next patch because it is useful if you decide to
do the back patch on PG10, 11, 12, and 13.
BTW perhaps a quick look at the other \d commands would show if there are
precedents. I didn't have time for that.
Yes, we do promise that new psql works with older servers.
Yeah, makes sense. That means we need add the check for 12 / MCV.
Ah, I got it.
I fixed the patch to work with older servers to add the checking versions. And
I tested \dX command on older servers (PG10 - 13).
These results look fine.
0001:
Added the check code to handle pre-PG12. It has not MCV and
pg_statistic_ext_data.
0002:
This patch is the same as the previous patch (not changed).
Please find the attached files.
I wonder the column names added by \dX+ is fine? For example,
"Ndistinct_size" and "Dependencies_size". It looks like long names,
but acceptable?
Seems acceptable - I don't have a better idea.
I see, thanks!
Thanks,
Tatsuro Yamada
From c8be51a52a381a6e9c7be62022f5fc48b5915bd0 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatat...@gmail.com>
Date: Thu, 7 Jan 2021 14:28:20 +0900
Subject: [PATCH] Add \dX command on psql
This patch includes the version check in the logic building query.
---
doc/src/sgml/ref/psql-ref.sgml | 14 ++++
src/bin/psql/command.c | 3 +
src/bin/psql/describe.c | 141 +++++++++++++++++++++++++++++++++++++++++
src/bin/psql/describe.h | 3 +
src/bin/psql/help.c | 1 +
src/bin/psql/tab-complete.c | 4 +-
6 files changed, 165 insertions(+), 1 deletion(-)
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 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 52c6de51b6..0ccd9ed286 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4401,6 +4401,147 @@ 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\"",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Definition"));
+
+ 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"));
+ }
+
+ if (verbose && 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 if (verbose)
+ {
+ 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*"))
--
2.16.5
From 9f36a9df0c2803f5554b951e37ba5969c2744e3b Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatat...@gmail.com>
Date: Tue, 5 Jan 2021 12:34:16 +0900
Subject: [PATCH 2/2] Add regression test for \dX on psql
---
src/test/regress/expected/stats_ext.out | 94 +++++++++++++++++++++++++++++++++
src/test/regress/sql/stats_ext.sql | 31 +++++++++++
2 files changed, 125 insertions(+)
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.16.5
