From: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Julien Rouhaud <rjuju123@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: list of extended statistics on psql
Date: Fri, 08 Jan 2021 08:52:02 +0900
Message-ID: <1045d2cb-7ecd-e6bf-cb50-8eeea89cc4b0@nttcom.co.jp_1> (raw)
In-Reply-To: <61cba5af-5a58-6bda-610c-420077b780c3@enterprisedb.com>
References: <20210107144713.GA8872@alvherre.pgsql>
<61cba5af-5a58-6bda-610c-420077b780c3@enterprisedb.com>
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
Attachments:download all (2 files, .tar.gz)0001-Add-dX-command-on-psql-with-version-check.patch (text/plain, 9.3K) download
From c8be51a52a381a6e9c7be62022f5fc48b5915bd0 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatattsu@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.sgmlindex 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.cindex 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.cindex 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.hindex 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.cindex 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.cindex 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
0002-Add-regression-test-for-dX-on-psql.patch (text/plain, 9.7K) download
From 9f36a9df0c2803f5554b951e37ba5969c2744e3b Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatattsu@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.outindex 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.sqlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tatsuro.yamada.tf@nttcom.co.jp, tomas.vondra@enterprisedb.com, alvherre@2ndquadrant.com, tomas.vondra@2ndquadrant.com, michael@paquier.xyz, pavel.stehule@gmail.com, rjuju123@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: list of extended statistics on psql
In-Reply-To: <1045d2cb-7ecd-e6bf-cb50-8eeea89cc4b0@nttcom.co.jp_1>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox