Hi Tomas,
On 2021/01/09 9:01, Tomas Vondra wrote:
On 1/8/21 1:14 AM, Tomas Vondra wrote:
On 1/8/21 12:52 AM, Tatsuro Yamada wrote:
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.
OK, thanks. I'll take a look and probably push tomorrow. FWIW I plan to
squash the patches into a single commit.
Attached is a patch I plan to commit - 0001 is the last submitted
version with a couple minor tweaks, mostly in docs/comments, and small
rework of branching to be more like the other functions in describe.c.
Thanks for revising the patch.
I reviewed the 0001, and the branching and comments look good to me.
However, I added an alias name in processSQLNamePattern() on the patch:
s/"stxname"/"es.stxname"/
While working on that, I realized that 'defined' might be a bit
ambiguous, I initially thought it means 'NOT NULL' (which it does not).
I propose to change it to 'requested' instead. Tatsuro, do you agree, or
do you think 'defined' is better?
Regarding the status of extended stats, I think the followings:
- "defined": it shows the extended stats defined only. We can't know
whether it needs to analyze or not. I agree this name was
ambiguous. Therefore we should replace it with a more suitable
name.
- "requested": it shows the extended stats needs something. Of course,
we know it needs to ANALYZE because we can create the patch.
However, I feel there is a little ambiguity for DBA.
To solve this, it would be better to write an explanation of
the status in the document. For example,
======
The column of the kind of extended stats (e. g. Ndistinct) shows some statuses.
"requested" means that it needs to gather data by ANALYZE. "built" means ANALYZE
was finished, and the planner can use it. NULL means that it doesn't exists.
======
What do you think? :-D
Thanks,
Tatsuro Yamada
>From 3d2f4ef2ecba9fd7987df665237add6fc4ec03c1 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatat...@gmail.com>
Date: Thu, 7 Jan 2021 14:28:20 +0900
Subject: [PATCH 1/2] psql \dX: list extended statistics objects
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,
+ "es.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
>From 1ff2e77fd2472700a4a89699d8afed8679e38c7e Mon Sep 17 00:00:00 2001
From: Tomas Vondra <to...@2ndquadrant.com>
Date: Sat, 9 Jan 2021 00:56:43 +0100
Subject: [PATCH 2/2] fixup: rename defined to requested
---
src/bin/psql/describe.c | 10 +--
src/test/regress/expected/stats_ext.out | 90 ++++++++++++-------------
2 files changed, 50 insertions(+), 50 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 46f54199fb..83084f79d0 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4440,10 +4440,10 @@ listExtendedStats(const char *pattern, bool verbose)
{
appendPQExpBuffer(&buf,
",\nCASE WHEN es.stxndistinct
IS NOT NULL THEN 'built' \n"
- " WHEN 'd' =
any(es.stxkind) THEN 'defined' \n"
+ " WHEN 'd' =
any(es.stxkind) THEN 'requested' \n"
"END AS \"%s\", \n"
"CASE WHEN es.stxdependencies
IS NOT NULL THEN 'built' \n"
- " WHEN 'f' =
any(es.stxkind) THEN 'defined' \n"
+ " WHEN 'f' =
any(es.stxkind) THEN 'requested' \n"
"END AS \"%s\"",
gettext_noop("Ndistinct"),
gettext_noop("Dependencies"));
@@ -4452,13 +4452,13 @@ listExtendedStats(const char *pattern, bool verbose)
{
appendPQExpBuffer(&buf,
",\nCASE WHEN
esd.stxdndistinct IS NOT NULL THEN 'built' \n"
- " WHEN 'd' =
any(es.stxkind) THEN 'defined' \n"
+ " WHEN 'd' =
any(es.stxkind) THEN 'requested' \n"
"END AS \"%s\", \n"
"CASE WHEN
esd.stxddependencies IS NOT NULL THEN 'built' \n"
- " WHEN 'f' =
any(es.stxkind) THEN 'defined' \n"
+ " WHEN 'f' =
any(es.stxkind) THEN 'requested' \n"
"END AS \"%s\", \n"
"CASE WHEN esd.stxdmcv IS NOT
NULL THEN 'built' \n"
- " WHEN 'm' =
any(es.stxkind) THEN 'defined' \n"
+ " WHEN 'm' =
any(es.stxkind) THEN 'requested' \n"
"END AS \"%s\"",
gettext_noop("Ndistinct"),
gettext_noop("Dependencies"),
diff --git a/src/test/regress/expected/stats_ext.out
b/src/test/regress/expected/stats_ext.out
index 8c8a0afcf6..b3ae4de185 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -1741,9 +1741,9 @@ create statistics stts_s2.stts_yama (dependencies, mcv)
on col1, col3 from stts_
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
-----------+------------------------+--------------------------------------+-----------+--------------+---------
+ 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
@@ -1751,70 +1751,70 @@ analyze stts_t1;
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
+ public | stts_4 | b, c FROM stts_t2 |
requested | requested | requested
+ public | stts_hoge | col1, col2, col3 FROM stts_t3 |
requested | requested | requested
+ stts_s1 | stts_foo | col1, col2 FROM stts_t3 |
requested | requested | requested
+ stts_s2 | stts_yama | col1, col3 FROM stts_t3 |
| requested | requested
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
---------+--------+-------------------+-----------+--------------+---------
+ 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
+ public | stts_4 | b, c FROM stts_t2 | requested | requested | requested
(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
+ List of extended statistics
+ Schema | Name | Definition | Ndistinct | Dependencies
| MCV
+--------+-----------+-------------------------------+-----------+--------------+-----------
+ public | stts_hoge | col1, col2, col3 FROM stts_t3 | requested | requested
| requested
(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
+ 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 |
requested | requested | requested | 0 bytes | 0 bytes | 0
bytes
+ public | stts_hoge | col1, col2, col3 FROM stts_t3 |
requested | requested | requested | 0 bytes | 0 bytes | 0
bytes
+ stts_s1 | stts_foo | col1, col2 FROM stts_t3 |
requested | requested | requested | 0 bytes | 0 bytes | 0
bytes
+ stts_s2 | stts_yama | col1, col3 FROM stts_t3 |
| requested | requested | | 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
+ 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 | requested | requested | requested |
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
+ 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 | requested | requested
| requested | 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
+ 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 | | requested |
requested | | 0 bytes | 0 bytes
(1 row)
drop table stts_t1, stts_t2, stts_t3;
--
2.26.2
