Hi Tomas,
Thanks for your comments and also revising patches.
On 2020/11/16 3:22, Tomas Vondra wrote:
It's better to always post the whole patch series, so that cfbot can
test it properly. Sending just 0003 separately kind breaks that.
I now understand how "cfbot" works so that I'll take care of that
when I send patches. Thanks.
Also, 0003 seems to only tweak the .sql file, not the expected output,
and there actually seems to be two places that mistakenly use \dx (so
listing extensions) instead of \dX. I've fixed both issues in the
attached patches.
Oops, sorry about that.
However, I think the 0002 tests are better/sufficient - I prefer to keep
it compact, not interleaving with the tests testing various other stuff.
So I don't intend to commit 0003, unless there's something that I don't
see for some reason.
I Agreed. 0002 is easy to modify test cases and check results than 0003.
Therefore, I'll go with 0002.
The one remaining thing I'm not sure about is naming of the columns with
size of statistics - N_size, D_size and M_size does not seem very clear.
Any clearer naming will however make the tables wider, though :-/
Yeah, I think so too, but I couldn't get an idea of a suitable name for
the columns when I created the patch.
I don't prefer a long name but I'll replace the name with it to be clearer.
For example, s/N_size/Ndistinct_size/.
Please find attached patcheds:
- 0001: Replace column names
- 0002: Recreate regression test based on 0001
Regards,
Tatsuro Yamada
From 85fe05c3020cd595ae8d5c2cc6f695b39f4a6e03 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatat...@gmail.com>
Date: Tue, 17 Nov 2020 13:30:57 +0900
Subject: [PATCH 2/2] Recreate regression test
---
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 4c3edd213f..27ca54a8f3 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -1550,6 +1550,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 9781e590a3..2b90471a4b 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -833,6 +833,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
From b093c5cc9b6376809123ca90bec3f330109935b8 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatat...@gmail.com>
Date: Tue, 17 Nov 2020 13:28:50 +0900
Subject: [PATCH 1/2] Replace column names with long names.
For example, s/N_size/Ndistinct_size/.
---
doc/src/sgml/ref/psql-ref.sgml | 14 ++++++
src/bin/psql/command.c | 3 ++
src/bin/psql/describe.c | 100 +++++++++++++++++++++++++++++++++++++++++
src/bin/psql/describe.h | 3 ++
src/bin/psql/help.c | 1 +
src/bin/psql/tab-complete.c | 4 +-
6 files changed, 124 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 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..3eb3b94b14 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("Ndistinct"),
+ 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("Ndistinct_size"),
+
gettext_noop("Dependencies_size"),
+ gettext_noop("MCV_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/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5238a960f7..30a8ada7d2 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",
@@ -3851,6 +3851,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
