Thread
-
[PATCH 1/2] psql \dX: list extended statistics objects
Tatsuro Yamada <yamatattsu@gmail.com> — 2021-01-07T05:28:20Z
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=> </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"