Thanks,
It's better to always post the whole patch series, so that cfbot can
test it properly. Sending just 0003 separately kind breaks that.
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.
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.
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 :-/
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
>From e552a17a9c74952a002e6dabe8d57c9ab787addb Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatat...@gmail.com>
Date: Mon, 9 Nov 2020 11:25:14 +0900
Subject: [PATCH 1/3] Add \dX command on psql
---
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..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.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.26.2
>From 3daa574b29b77b1d9586e32c1c2a9b27a7e3565d Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatat...@gmail.com>
Date: Tue, 10 Nov 2020 11:39:14 +0900
Subject: [PATCH 2/3] Add regression test of \dX to stats_ext.sql
---
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..0ec4e24960 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 | N_distinct | 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 | 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 *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
+----------+------------------------+--------------------------------------+------------+--------------+---------+----------+-----------+------------
+ 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 | N_distinct | Dependencies | Mcv | N_size | D_size | M_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 | 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)
+
+\dX+ stts_s2.stts_yama
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_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.26.2
>From 62ef3806a959929c37d0847cffefc8e57e56c235 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <to...@2ndquadrant.com>
Date: Sun, 15 Nov 2020 19:00:23 +0100
Subject: [PATCH 3/3] Add regression test of \dX to stats_ext.sql (another
version)
---
src/test/regress/expected/stats_ext.out | 181 ++++++++++++++++++++++++
src/test/regress/sql/stats_ext.sql | 27 +++-
2 files changed, 207 insertions(+), 1 deletion(-)
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 0ec4e24960..fb10ee2237 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -64,6 +64,13 @@ SELECT pg_get_statisticsobjdef(oid) FROM pg_statistic_ext WHERE stxname = 'ab1_a
CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1
(1 row)
+\dX
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv
+------------------+---------------+---------------+------------+--------------+---------
+ regress_schema_2 | ab1_a_b_stats | a, b FROM ab1 | defined | defined | defined
+(1 row)
+
DROP STATISTICS regress_schema_2.ab1_a_b_stats;
-- Ensure statistics are dropped when columns are
CREATE STATISTICS ab1_b_c_stats ON b, c FROM ab1;
@@ -86,12 +93,25 @@ SELECT stxname FROM pg_statistic_ext WHERE stxname LIKE 'ab1%';
ab1_b_c_stats
(1 row)
+\dX
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv
+--------+---------------+---------------+------------+--------------+---------
+ public | ab1_b_c_stats | b, c FROM ab1 | defined | defined | defined
+(1 row)
+
DROP TABLE ab1;
SELECT stxname FROM pg_statistic_ext WHERE stxname LIKE 'ab1%';
stxname
---------
(0 rows)
+\dX
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv
+--------+------+------------+------------+--------------+-----
+(0 rows)
+
-- Ensure things work sanely with SET STATISTICS 0
CREATE TABLE ab1 (a INTEGER, b INTEGER);
ALTER TABLE ab1 ALTER a SET STATISTICS 0;
@@ -111,6 +131,13 @@ ALTER STATISTICS ab1_a_b_stats SET STATISTICS 0;
Statistics objects:
"public"."ab1_a_b_stats" (ndistinct, dependencies, mcv) ON a, b FROM ab1; STATISTICS 0
+\dX+ ab1_a_b_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+---------------+---------------+------------+--------------+---------+---------+---------+---------
+ public | ab1_a_b_stats | a, b FROM ab1 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes
+(1 row)
+
ANALYZE ab1;
SELECT stxname, stxdndistinct, stxddependencies, stxdmcv
FROM pg_statistic_ext s, pg_statistic_ext_data d
@@ -121,6 +148,13 @@ SELECT stxname, stxdndistinct, stxddependencies, stxdmcv
ab1_a_b_stats | | |
(1 row)
+\dX+ ab1_a_b_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+---------------+---------------+------------+--------------+---------+---------+---------+---------
+ public | ab1_a_b_stats | a, b FROM ab1 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes
+(1 row)
+
ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1;
\d+ ab1
Table "public.ab1"
@@ -131,6 +165,13 @@ ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1;
Statistics objects:
"public"."ab1_a_b_stats" (ndistinct, dependencies, mcv) ON a, b FROM ab1
+\dX+ ab1_a_b_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+---------------+---------------+------------+--------------+---------+---------+---------+---------
+ public | ab1_a_b_stats | a, b FROM ab1 | defined | defined | defined | 0 bytes | 0 bytes | 0 bytes
+(1 row)
+
-- partial analyze doesn't build stats either
ANALYZE ab1 (a);
WARNING: statistics object "public.ab1_a_b_stats" could not be computed for relation "public.ab1"
@@ -146,6 +187,13 @@ CREATE TABLE ab1c () INHERITS (ab1);
INSERT INTO ab1 VALUES (1,1);
CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ANALYZE ab1;
+\dX+ ab1_a_b_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+---------------+---------------+------------+--------------+-------+----------+----------+-----------
+ public | ab1_a_b_stats | a, b FROM ab1 | built | built | built | 11 bytes | 40 bytes | 186 bytes
+(1 row)
+
DROP TABLE ab1 CASCADE;
NOTICE: drop cascades to table ab1c
-- Verify supported object types for extended statistics
@@ -254,6 +302,13 @@ SELECT s.stxkind, d.stxdndistinct
{d,f,m} | {"3, 4": 11, "3, 6": 11, "4, 6": 11, "3, 4, 6": 11}
(1 row)
+\dX+ s10
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+------+------------------------+------------+--------------+-------+----------+-----------+-----------
+ public | s10 | a, b, c FROM ndistinct | built | built | built | 51 bytes | 189 bytes | 988 bytes
+(1 row)
+
-- minor improvement, make sure the ctid does not break the matching
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY ctid, a, b');
estimated | actual
@@ -310,6 +365,13 @@ SELECT s.stxkind, d.stxdndistinct
{d,f,m} | {"3, 4": 2550, "3, 6": 800, "4, 6": 1632, "3, 4, 6": 5000}
(1 row)
+\dX+ s10
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+------+------------------------+------------+--------------+-------+----------+----------+------------
+ public | s10 | a, b, c FROM ndistinct | built | built | built | 58 bytes | 23 bytes | 5718 bytes
+(1 row)
+
-- correct estimates
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
estimated | actual
@@ -350,6 +412,12 @@ SELECT s.stxkind, d.stxdndistinct
---------+---------------
(0 rows)
+\dX+
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+------+------------+------------+--------------+-----+--------+--------+--------
+(0 rows)
+
-- dropping the statistics results in under-estimates
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
estimated | actual
@@ -413,6 +481,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
-- create statistics
CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
ANALYZE functional_dependencies;
+\dX+ func_deps_stat
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+----------------+--------------------------------------+------------+--------------+-----+--------+---------+--------
+ public | func_deps_stat | a, b, c FROM functional_dependencies | | defined | | | 0 bytes |
+(1 row)
+
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
estimated | actual
-----------+--------
@@ -431,6 +506,12 @@ DROP STATISTICS func_deps_stat;
INSERT INTO functional_dependencies (a, b, c, filler1)
SELECT mod(i,100), mod(i,50), mod(i,25), i FROM generate_series(1,5000) s(i);
ANALYZE functional_dependencies;
+\dX+ func_deps_stat
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+------+------------+------------+--------------+-----+--------+--------+--------
+(0 rows)
+
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
estimated | actual
-----------+--------
@@ -590,6 +671,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
-- create statistics
CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
ANALYZE functional_dependencies;
+\dX+ func_deps_stat
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+----------------+--------------------------------------+------------+--------------+-----+--------+-----------+--------
+ public | func_deps_stat | a, b, c FROM functional_dependencies | | built | | | 106 bytes |
+(1 row)
+
-- print the detected dependencies
SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
dependencies
@@ -821,6 +909,14 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi
CREATE STATISTICS functional_dependencies_multi_1 (dependencies) ON a, b FROM functional_dependencies_multi;
CREATE STATISTICS functional_dependencies_multi_2 (dependencies) ON c, d FROM functional_dependencies_multi;
ANALYZE functional_dependencies_multi;
+\dX+ functional_dependencies_multi_*
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+---------------------------------+-----------------------------------------+------------+--------------+-----+--------+----------+--------
+ public | functional_dependencies_multi_1 | a, b FROM functional_dependencies_multi | | built | | | 40 bytes |
+ public | functional_dependencies_multi_2 | c, d FROM functional_dependencies_multi | | built | | | 40 bytes |
+(2 rows)
+
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE a = 0 AND b = 0');
estimated | actual
-----------+--------
@@ -882,6 +978,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b =
-- create statistics
CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
ANALYZE mcv_lists;
+\dX+ mcv_lists_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+-----------------+------------------------+------------+--------------+-------+--------+--------+------------
+ public | mcv_lists_stats | a, b, c FROM mcv_lists | | | built | | | 5890 bytes
+(1 row)
+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
estimated | actual
-----------+--------
@@ -1041,6 +1144,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY
-- create statistics
CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
ANALYZE mcv_lists;
+\dX+ mcv_lists_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+-----------------+------------------------+------------+--------------+-------+--------+--------+------------
+ public | mcv_lists_stats | a, b, c FROM mcv_lists | | | built | | | 6754 bytes
+(1 row)
+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
estimated | actual
-----------+--------
@@ -1191,6 +1301,13 @@ SELECT d.stxdmcv IS NOT NULL
t
(1 row)
+\dX+ mcv_lists_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+-----------------+------------------------+------------+--------------+-------+--------+--------+------------
+ public | mcv_lists_stats | a, b, c FROM mcv_lists | | | built | | | 6754 bytes
+(1 row)
+
-- check change of column type resets the MCV statistics
ALTER TABLE mcv_lists ALTER COLUMN c TYPE numeric;
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
@@ -1200,6 +1317,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b =
(1 row)
ANALYZE mcv_lists;
+\dX+ mcv_lists_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+-----------------+------------------------+------------+--------------+-------+--------+--------+------------
+ public | mcv_lists_stats | a, b, c FROM mcv_lists | | | built | | | 6950 bytes
+(1 row)
+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
estimated | actual
-----------+--------
@@ -1250,6 +1374,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (0, 1) AN
-- create statistics
CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
ANALYZE mcv_lists;
+\dX+ mcv_lists_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+-----------------+------------------------+------------+--------------+-------+--------+--------+------------
+ public | mcv_lists_stats | a, b, c FROM mcv_lists | | | built | | | 6916 bytes
+(1 row)
+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NULL');
estimated | actual
-----------+--------
@@ -1294,6 +1425,13 @@ SELECT m.*
0 | {1,2,3} | {f,f,f} | 1 | 1
(1 row)
+\dX+ mcv_lists_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+-----------------+------------------------+------------+--------------+-------+--------+--------+-----------
+ public | mcv_lists_stats | a, b, c FROM mcv_lists | | | built | | | 258 bytes
+(1 row)
+
-- 2 distinct combinations with NULL values, all in the MCV list
TRUNCATE mcv_lists;
DROP STATISTICS mcv_lists_stats;
@@ -1338,6 +1476,13 @@ SELECT m.*
1 | {NULL,NULL,NULL} | {t,t,t} | 0.5 | 0.25
(2 rows)
+\dX+ mcv_lists_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+-----------------+------------------------+------------+--------------+-------+--------+--------+-----------
+ public | mcv_lists_stats | a, b, d FROM mcv_lists | | | built | | | 294 bytes
+(1 row)
+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x''');
estimated | actual
-----------+--------
@@ -1385,6 +1530,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''167
CREATE STATISTICS mcv_lists_uuid_stats (mcv) ON a, b, c
FROM mcv_lists_uuid;
ANALYZE mcv_lists_uuid;
+\dX+ mcv_lists_uuid_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+----------------------+-----------------------------+------------+--------------+-------+--------+--------+--------
+ public | mcv_lists_uuid_stats | a, b, c FROM mcv_lists_uuid | | | built | | | 11 kB
+(1 row)
+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND b = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
estimated | actual
-----------+--------
@@ -1414,6 +1566,13 @@ INSERT INTO mcv_lists_arrays (a, b, c)
CREATE STATISTICS mcv_lists_arrays_stats (mcv) ON a, b, c
FROM mcv_lists_arrays;
ANALYZE mcv_lists_arrays;
+\dX+ mcv_lists_arrays_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+------------------------+-------------------------------+------------+--------------+-------+--------+--------+--------
+ public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | built | | | 24 kB
+(1 row)
+
-- mcv with bool
CREATE TABLE mcv_lists_bool (
a BOOL,
@@ -1453,6 +1612,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE NOT a AND
CREATE STATISTICS mcv_lists_bool_stats (mcv) ON a, b, c
FROM mcv_lists_bool;
ANALYZE mcv_lists_bool;
+\dX+ mcv_lists_bool_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+----------------------+-----------------------------+------------+--------------+-------+--------+--------+-----------
+ public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | built | | | 386 bytes
+(1 row)
+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE a AND b AND c');
estimated | actual
-----------+--------
@@ -1516,6 +1682,14 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 AN
CREATE STATISTICS mcv_lists_multi_1 (mcv) ON a, b FROM mcv_lists_multi;
CREATE STATISTICS mcv_lists_multi_2 (mcv) ON c, d FROM mcv_lists_multi;
ANALYZE mcv_lists_multi;
+\dX+ mcv_lists_multi_*
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+--------+-------------------+---------------------------+------------+--------------+-------+--------+--------+-----------
+ public | mcv_lists_multi_1 | a, b FROM mcv_lists_multi | | | built | | | 426 bytes
+ public | mcv_lists_multi_2 | c, d FROM mcv_lists_multi | | | built | | | 546 bytes
+(2 rows)
+
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 AND b = 0');
estimated | actual
-----------+--------
@@ -1550,6 +1724,13 @@ 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;
+\dX+ tststats.priv_test_stats
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
+----------+-----------------+----------------------------------+------------+--------------+-------+--------+--------+-----------
+ tststats | priv_test_stats | a, b FROM tststats.priv_test_tbl | | | built | | | 686 bytes
+(1 row)
+
-- 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;
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 2b90471a4b..40da1dcbb5 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -49,6 +49,7 @@ CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1;
-- Let's also verify the pg_get_statisticsobjdef output looks sane.
SELECT pg_get_statisticsobjdef(oid) FROM pg_statistic_ext WHERE stxname = 'ab1_a_b_stats';
+\dX
DROP STATISTICS regress_schema_2.ab1_a_b_stats;
@@ -60,9 +61,10 @@ ALTER TABLE ab1 DROP COLUMN a;
\d ab1
-- Ensure statistics are dropped when table is
SELECT stxname FROM pg_statistic_ext WHERE stxname LIKE 'ab1%';
+\dX
DROP TABLE ab1;
SELECT stxname FROM pg_statistic_ext WHERE stxname LIKE 'ab1%';
-
+\dX
-- Ensure things work sanely with SET STATISTICS 0
CREATE TABLE ab1 (a INTEGER, b INTEGER);
ALTER TABLE ab1 ALTER a SET STATISTICS 0;
@@ -73,13 +75,16 @@ ALTER TABLE ab1 ALTER a SET STATISTICS -1;
-- setting statistics target 0 skips the statistics, without printing any message, so check catalog
ALTER STATISTICS ab1_a_b_stats SET STATISTICS 0;
\d ab1
+\dX+ ab1_a_b_stats
ANALYZE ab1;
SELECT stxname, stxdndistinct, stxddependencies, stxdmcv
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxname = 'ab1_a_b_stats'
AND d.stxoid = s.oid;
+\dX+ ab1_a_b_stats
ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1;
\d+ ab1
+\dX+ ab1_a_b_stats
-- partial analyze doesn't build stats either
ANALYZE ab1 (a);
ANALYZE ab1;
@@ -93,6 +98,7 @@ CREATE TABLE ab1c () INHERITS (ab1);
INSERT INTO ab1 VALUES (1,1);
CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ANALYZE ab1;
+\dX+ ab1_a_b_stats
DROP TABLE ab1 CASCADE;
-- Verify supported object types for extended statistics
@@ -171,6 +177,7 @@ SELECT s.stxkind, d.stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
+\dX+ s10
-- minor improvement, make sure the ctid does not break the matching
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY ctid, a, b');
@@ -202,6 +209,7 @@ SELECT s.stxkind, d.stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
+\dX+ s10
-- correct estimates
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
@@ -220,6 +228,7 @@ SELECT s.stxkind, d.stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
+\dX+
-- dropping the statistics results in under-estimates
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
@@ -261,6 +270,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
ANALYZE functional_dependencies;
+\dX+ func_deps_stat
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
@@ -274,6 +284,7 @@ INSERT INTO functional_dependencies (a, b, c, filler1)
SELECT mod(i,100), mod(i,50), mod(i,25), i FROM generate_series(1,5000) s(i);
ANALYZE functional_dependencies;
+\dX+ func_deps_stat
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
@@ -335,6 +346,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
ANALYZE functional_dependencies;
+\dX+ func_deps_stat
-- print the detected dependencies
SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
@@ -437,6 +449,7 @@ CREATE STATISTICS functional_dependencies_multi_1 (dependencies) ON a, b FROM fu
CREATE STATISTICS functional_dependencies_multi_2 (dependencies) ON c, d FROM functional_dependencies_multi;
ANALYZE functional_dependencies_multi;
+\dX+ functional_dependencies_multi_*
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE a = 0 AND b = 0');
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies_multi WHERE 0 = a AND 0 = b');
@@ -472,6 +485,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b =
CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
ANALYZE mcv_lists;
+\dX+ mcv_lists_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
@@ -536,6 +550,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a < ALL (ARRAY
CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
ANALYZE mcv_lists;
+\dX+ mcv_lists_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
@@ -591,6 +606,7 @@ SELECT d.stxdmcv IS NOT NULL
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxname = 'mcv_lists_stats'
AND d.stxoid = s.oid;
+\dX+ mcv_lists_stats
-- check change of column type resets the MCV statistics
ALTER TABLE mcv_lists ALTER COLUMN c TYPE numeric;
@@ -598,6 +614,7 @@ ALTER TABLE mcv_lists ALTER COLUMN c TYPE numeric;
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
ANALYZE mcv_lists;
+\dX+ mcv_lists_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a = 1 AND b = ''1''');
@@ -629,6 +646,7 @@ SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IN (0, 1) AN
CREATE STATISTICS mcv_lists_stats (mcv) ON a, b, c FROM mcv_lists;
ANALYZE mcv_lists;
+\dX+ mcv_lists_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE a IS NULL AND b IS NULL');
@@ -650,6 +668,7 @@ SELECT m.*
pg_mcv_list_items(d.stxdmcv) m
WHERE s.stxname = 'mcv_lists_stats'
AND d.stxoid = s.oid;
+\dX+ mcv_lists_stats
-- 2 distinct combinations with NULL values, all in the MCV list
TRUNCATE mcv_lists;
@@ -682,6 +701,7 @@ SELECT m.*
pg_mcv_list_items(d.stxdmcv) m
WHERE s.stxname = 'mcv_lists_stats'
AND d.stxoid = s.oid;
+\dX+ mcv_lists_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists WHERE b = ''x'' OR d = ''x''');
@@ -714,6 +734,7 @@ CREATE STATISTICS mcv_lists_uuid_stats (mcv) ON a, b, c
FROM mcv_lists_uuid;
ANALYZE mcv_lists_uuid;
+\dX+ mcv_lists_uuid_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_uuid WHERE a = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc'' AND b = ''1679091c-5a88-0faf-6fb5-e6087eb1b2dc''');
@@ -740,6 +761,7 @@ CREATE STATISTICS mcv_lists_arrays_stats (mcv) ON a, b, c
FROM mcv_lists_arrays;
ANALYZE mcv_lists_arrays;
+\dX+ mcv_lists_arrays_stats
-- mcv with bool
CREATE TABLE mcv_lists_bool (
@@ -768,6 +790,7 @@ CREATE STATISTICS mcv_lists_bool_stats (mcv) ON a, b, c
FROM mcv_lists_bool;
ANALYZE mcv_lists_bool;
+\dX+ mcv_lists_bool_stats
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_bool WHERE a AND b AND c');
@@ -806,6 +829,7 @@ CREATE STATISTICS mcv_lists_multi_1 (mcv) ON a, b FROM mcv_lists_multi;
CREATE STATISTICS mcv_lists_multi_2 (mcv) ON c, d FROM mcv_lists_multi;
ANALYZE mcv_lists_multi;
+\dX+ mcv_lists_multi_*
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE a = 0 AND b = 0');
SELECT * FROM check_estimated_rows('SELECT * FROM mcv_lists_multi WHERE c = 0 AND d = 0');
@@ -832,6 +856,7 @@ CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
FROM tststats.priv_test_tbl;
ANALYZE tststats.priv_test_tbl;
+\dX+ tststats.priv_test_stats
-- Check printing info about extended statistics by \dX
create table stts_t1 (a int, b int);
--
2.26.2
