From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Julien Rouhaud <rjuju123@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: list of extended statistics on psql
Date: Sun, 8 Nov 2020 22:53:34 +0100
Message-ID: <9ed1643b-5c04-640b-70fa-ad7984972cd0@enterprisedb.com> (raw)
In-Reply-To: <831cda41-2299-51f7-12c3-9bf9d3328a58@nttcom.co.jp_1>
References: <20200831153053.a343pjgumahiejhw@development>
<20200831161809.GA31337@alvherre.pgsql>
<20200831163200.jp6eqbaaupb6hxac@development>
<CAFj8pRDsiGozdoeqHC6d8disaY62Zihw5hL3dUHeFaDjyZAxNw@mail.gmail.com>
<d63766ca-19fa-25a6-cb60-e61b6bbc7700@nttcom.co.jp_1>
<20200917055531.GV2873@paquier.xyz>
<20200930061947.GD1996@paquier.xyz>
<2a3173fb-8571-3779-2916-d16a170750f0@nttcom.co.jp_1>
<20201028190601.bbshe6dhp32767q7@development>
<027134cc-4c99-2878-4385-c0786283c5ea@nttcom.co.jp_1>
<831cda41-2299-51f7-12c3-9bf9d3328a58@nttcom.co.jp_1>
Hi,
I took a look at this today, and I think the code is ready, but the
regression test needs a bit more work:
1) It's probably better to use somewhat more specific names for the
objects, especially when created in public schema. It decreases the
chance of a collision with other tests (which may be hard to notice
because of timing). I suggest we use "stts_" prefix or something like
that, per the attached 0002 patch. (0001 is just the v7 patch)
2) The test is failing intermittently because it's executed in parallel
with stats_ext test, which is also creating extended statistics. So
depending on the timing the \dX may list some of the stats_ext stuff.
I'm not sure what to do about this. Either this part needs to be moved
to a separate test executed in a different group, or maybe we should
simply move it to stats_ext.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:download all (2 files, .tar.gz)0001-v7-20201108.patch (text/x-patch, 14.1K) download
From 1a7e99c931c0b3ed5560abd3ff656412f8b353f1 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas@2ndquadrant.com>
Date: Sun, 8 Nov 2020 21:53:54 +0100
Subject: [PATCH 1/2] v7
---
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 +-
src/test/regress/expected/psql.out | 77 ++++++++++++++++++++++
src/test/regress/sql/psql.sql | 29 +++++++++
8 files changed, 230 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 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.cindex 07d640021c..1807324542 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("N_distinct"),+ 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("N_size"),+ gettext_noop("D_size"),+ gettext_noop("M_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.hindex 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.cindex 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.cindex 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*"))
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.outindex daac0ff49d..701f2dbe59 100644--- a/src/test/regress/expected/psql.out+++ b/src/test/regress/expected/psql.out@@ -5072,3 +5072,80 @@ List of access methods
hash | uuid_ops | uuid | uuid | 2 | uuid_hash_extended
(5 rows)
+-- check printing info about extended statistics+create table t1 (a int, b int);+create statistics stts_1 (dependencies) on a, b from t1;+create statistics stts_2 (dependencies, ndistinct) on a, b from t1;+create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from t1;+create table t2 (a int, b int, c int);+create statistics stts_4 on b, c from t2;+create table hoge (col1 int, col2 int, col3 int);+create statistics stts_hoge on col1, col2, col3 from hoge;+create schema foo;+create schema yama;+create statistics foo.stts_foo on col1, col2 from hoge;+create statistics yama.stts_yama (ndistinct, mcv) on col1, col3 from hoge;+insert into t1 select i,i from generate_series(1,100) i;+analyze t1;+\dX+ List of extended statistics+ Schema | Name | Definition | N_distinct | Dependencies | Mcv +--------+-----------+----------------------------+------------+--------------+---------+ foo | stts_foo | col1, col2 FROM hoge | defined | defined | defined+ public | stts_1 | a, b FROM t1 | | built | + public | stts_2 | a, b FROM t1 | built | built | + public | stts_3 | a, b FROM t1 | built | built | built+ public | stts_4 | b, c FROM t2 | defined | defined | defined+ public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined+ yama | stts_yama | col1, col3 FROM hoge | defined | | defined+(7 rows)++\dX stts_?+ List of extended statistics+ Schema | Name | Definition | N_distinct | Dependencies | Mcv +--------+--------+--------------+------------+--------------+---------+ public | stts_1 | a, b FROM t1 | | built | + public | stts_2 | a, b FROM t1 | built | built | + public | stts_3 | a, b FROM t1 | built | built | built+ public | stts_4 | b, c FROM t2 | defined | defined | defined+(4 rows)++\dX *hoge+ List of extended statistics+ Schema | Name | Definition | N_distinct | Dependencies | Mcv +--------+-----------+----------------------------+------------+--------------+---------+ public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined+(1 row)++\dX++ List of extended statistics+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size +--------+-----------+----------------------------+------------+--------------+---------+----------+----------+------------+ foo | stts_foo | col1, col2 FROM hoge | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes+ public | stts_1 | a, b FROM t1 | | built | | | 40 bytes | + public | stts_2 | a, b FROM t1 | built | built | | 13 bytes | 40 bytes | + public | stts_3 | a, b FROM t1 | built | built | built | 13 bytes | 40 bytes | 6126 bytes+ public | stts_4 | b, c FROM t2 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes+ public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes+ yama | stts_yama | col1, col3 FROM hoge | defined | | defined | 0 bytes | | 0 bytes+(7 rows)++\dX+ stts_?+ List of extended statistics+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size +--------+--------+--------------+------------+--------------+---------+----------+----------+------------+ public | stts_1 | a, b FROM t1 | | built | | | 40 bytes | + public | stts_2 | a, b FROM t1 | built | built | | 13 bytes | 40 bytes | + public | stts_3 | a, b FROM t1 | built | built | built | 13 bytes | 40 bytes | 6126 bytes+ public | stts_4 | b, c FROM t2 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes+(4 rows)++\dX+ *hoge+ List of extended statistics+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size +--------+-----------+----------------------------+------------+--------------+---------+---------+---------+---------+ public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes+(1 row)++drop table t1, t2, hoge;+drop schema foo, yama;diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sqlindex 47b28d2a07..dc9f4b2159 100644--- a/src/test/regress/sql/psql.sql+++ b/src/test/regress/sql/psql.sql@@ -1225,3 +1225,32 @@ drop role regress_partitioning_role;
\dAo * pg_catalog.jsonb_path_ops
\dAp+ btree float_ops
\dAp * pg_catalog.uuid_ops
++-- check printing info about extended statistics+create table t1 (a int, b int);+create statistics stts_1 (dependencies) on a, b from t1;+create statistics stts_2 (dependencies, ndistinct) on a, b from t1;+create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from t1;++create table t2 (a int, b int, c int);+create statistics stts_4 on b, c from t2;++create table hoge (col1 int, col2 int, col3 int);+create statistics stts_hoge on col1, col2, col3 from hoge;++create schema foo;+create schema yama;+create statistics foo.stts_foo on col1, col2 from hoge;+create statistics yama.stts_yama (ndistinct, mcv) on col1, col3 from hoge;++insert into t1 select i,i from generate_series(1,100) i;+analyze t1;++\dX+\dX stts_?+\dX *hoge+\dX++\dX+ stts_?+\dX+ *hoge+drop table t1, t2, hoge;+drop schema foo, yama;--
2.26.2
0002-rename-objects-in-regression-test-20201108.patch (text/x-patch, 12.0K) download
From 9a6f15a7d531afa28f8cd8f67edbe301a8ea7ed5 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas@2ndquadrant.com>
Date: Sun, 8 Nov 2020 22:37:29 +0100
Subject: [PATCH 2/2] rename objects in regression test
---
src/test/regress/expected/psql.out | 120 ++++++++++++++---------------
src/test/regress/sql/psql.sql | 36 ++++-----
2 files changed, 78 insertions(+), 78 deletions(-)
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.outindex 701f2dbe59..27c73445d5 100644--- a/src/test/regress/expected/psql.out+++ b/src/test/regress/expected/psql.out@@ -5073,79 +5073,79 @@ List of access methods
(5 rows)
-- check printing info about extended statistics
-create table t1 (a int, b int);-create statistics stts_1 (dependencies) on a, b from t1;-create statistics stts_2 (dependencies, ndistinct) on a, b from t1;-create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from t1;-create table t2 (a int, b int, c int);-create statistics stts_4 on b, c from t2;-create table hoge (col1 int, col2 int, col3 int);-create statistics stts_hoge on col1, col2, col3 from hoge;-create schema foo;-create schema yama;-create statistics foo.stts_foo on col1, col2 from hoge;-create statistics yama.stts_yama (ndistinct, mcv) on col1, col3 from hoge;-insert into t1 select i,i from generate_series(1,100) i;-analyze t1;+create table stts_t1 (a int, b int);+create statistics stts_1 (dependencies) on a, b from stts_t1;+create statistics stts_2 (dependencies, ndistinct) on a, b from stts_t1;+create statistics stts_3 (dependencies, ndistinct, 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 (ndistinct, 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 | N_distinct | Dependencies | Mcv ---------+-----------+----------------------------+------------+--------------+---------- foo | stts_foo | col1, col2 FROM hoge | defined | defined | defined- public | stts_1 | a, b FROM t1 | | built | - public | stts_2 | a, b FROM t1 | built | built | - public | stts_3 | a, b FROM t1 | built | built | built- public | stts_4 | b, c FROM t2 | defined | defined | defined- public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined- yama | stts_yama | col1, col3 FROM hoge | defined | | defined+ List of extended statistics+ Schema | Name | Definition | N_distinct | 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_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
(7 rows)
\dX stts_?
- List of extended statistics- Schema | Name | Definition | N_distinct | Dependencies | Mcv ---------+--------+--------------+------------+--------------+---------- public | stts_1 | a, b FROM t1 | | built | - public | stts_2 | a, b FROM t1 | built | built | - public | stts_3 | a, b FROM t1 | built | built | built- public | stts_4 | b, c FROM t2 | defined | defined | defined+ List of extended statistics+ Schema | Name | Definition | N_distinct | 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 *hoge- List of extended statistics- Schema | Name | Definition | N_distinct | Dependencies | Mcv ---------+-----------+----------------------------+------------+--------------+---------- public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined+\dX *stts_hoge+ List of extended statistics+ Schema | Name | Definition | N_distinct | Dependencies | Mcv +--------+-----------+-------------------------------+------------+--------------+---------+ public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
(1 row)
\dX+
- List of extended statistics- Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size ---------+-----------+----------------------------+------------+--------------+---------+----------+----------+------------- foo | stts_foo | col1, col2 FROM hoge | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes- public | stts_1 | a, b FROM t1 | | built | | | 40 bytes | - public | stts_2 | a, b FROM t1 | built | built | | 13 bytes | 40 bytes | - public | stts_3 | a, b FROM t1 | built | built | built | 13 bytes | 40 bytes | 6126 bytes- public | stts_4 | b, c FROM t2 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes- public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes- yama | stts_yama | col1, col3 FROM hoge | defined | | defined | 0 bytes | | 0 bytes+ List of extended statistics+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size +---------+-----------+-------------------------------+------------+--------------+---------+----------+----------+------------+ public | stts_1 | a, b FROM stts_t1 | | built | | | 40 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
(7 rows)
\dX+ stts_?
- List of extended statistics- Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size ---------+--------+--------------+------------+--------------+---------+----------+----------+------------- public | stts_1 | a, b FROM t1 | | built | | | 40 bytes | - public | stts_2 | a, b FROM t1 | built | built | | 13 bytes | 40 bytes | - public | stts_3 | a, b FROM t1 | built | built | built | 13 bytes | 40 bytes | 6126 bytes- public | stts_4 | b, c FROM t2 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes+ List of extended statistics+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size +--------+--------+-------------------+------------+--------------+---------+----------+----------+------------+ public | stts_1 | a, b FROM stts_t1 | | built | | | 40 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+ *hoge- List of extended statistics- Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size ---------+-----------+----------------------------+------------+--------------+---------+---------+---------+---------- public | stts_hoge | col1, col2, col3 FROM hoge | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes+\dX+ *stts_hoge+ List of extended statistics+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size +--------+-----------+-------------------------------+------------+--------------+---------+---------+---------+---------+ public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes
(1 row)
-drop table t1, t2, hoge;-drop schema foo, yama;+drop table stts_t1, stts_t2, stts_t3;+drop schema stts_s1, stts_s2;diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sqlindex dc9f4b2159..5ebfe184a4 100644--- a/src/test/regress/sql/psql.sql+++ b/src/test/regress/sql/psql.sql@@ -1227,30 +1227,30 @@ drop role regress_partitioning_role;
\dAp * pg_catalog.uuid_ops
-- check printing info about extended statistics
-create table t1 (a int, b int);-create statistics stts_1 (dependencies) on a, b from t1;-create statistics stts_2 (dependencies, ndistinct) on a, b from t1;-create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from t1;+create table stts_t1 (a int, b int);+create statistics stts_1 (dependencies) on a, b from stts_t1;+create statistics stts_2 (dependencies, ndistinct) on a, b from stts_t1;+create statistics stts_3 (dependencies, ndistinct, mcv) on a, b from stts_t1;-create table t2 (a int, b int, c int);-create statistics stts_4 on b, c from t2;+create table stts_t2 (a int, b int, c int);+create statistics stts_4 on b, c from stts_t2;-create table hoge (col1 int, col2 int, col3 int);-create statistics stts_hoge on col1, col2, col3 from hoge;+create table stts_t3 (col1 int, col2 int, col3 int);+create statistics stts_hoge on col1, col2, col3 from stts_t3;-create schema foo;-create schema yama;-create statistics foo.stts_foo on col1, col2 from hoge;-create statistics yama.stts_yama (ndistinct, mcv) on col1, col3 from hoge;+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 (ndistinct, mcv) on col1, col3 from stts_t3;-insert into t1 select i,i from generate_series(1,100) i;-analyze t1;+insert into stts_t1 select i,i from generate_series(1,100) i;+analyze stts_t1;
\dX
\dX stts_?
-\dX *hoge+\dX *stts_hoge
\dX+
\dX+ stts_?
-\dX+ *hoge-drop table t1, t2, hoge;-drop schema foo, yama;+\dX+ *stts_hoge+drop table stts_t1, stts_t2, stts_t3;+drop schema stts_s1, stts_s2;--
2.26.2
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: tomas.vondra@enterprisedb.com, tatsuro.yamada.tf@nttcom.co.jp, tomas.vondra@2ndquadrant.com, michael@paquier.xyz, pavel.stehule@gmail.com, alvherre@2ndquadrant.com, rjuju123@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: list of extended statistics on psql
In-Reply-To: <9ed1643b-5c04-640b-70fa-ad7984972cd0@enterprisedb.com>
* 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