On 2020/08/31 1:59, Tom Lane wrote:
Tomas Vondra <tomas.von...@2ndquadrant.com> writes:
On Sun, Aug 30, 2020 at 12:33:29PM -0400, Alvaro Herrera wrote:
I wonder how to report that. Knowing that psql \-commands are not meant
for anything other than human consumption, maybe we can use a format()
string that says "built: %d bytes" when \dX+ is used (for each stat type),
and just "built" when \dX is used. What do people think about this?
Seems a little too cute to me.
I'd use the same approach as \d+, i.e. a separate column with the size.
Maybe that'd mean too many columns, though.
psql already has \d commands with so many columns that you pretty much
have to use \x mode to make them legible; \df+ for instance. I don't
mind if \dX+ is also in that territory. It'd be good though if plain
\dX can fit in a normal terminal window.
Hmm. How about these instead of "built: %d bytes"?
I added three columns (N_size, D_size, M_size) to show size. See below:
===================
postgres=# \dX
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 | not built | not built |
not built
public | stts_hoge | col1, col2, col3 FROM hoge | not built | not built |
not built
(5 rows)
postgres=# \dX+
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 |
public | stts_2 | a, b FROM t1 | built | built |
| 13 | 40 |
public | stts_3 | a, b FROM t1 | built | built |
built | 13 | 40 | 6126
public | stts_4 | b, c FROM t2 | not built | not built |
not built | | |
public | stts_hoge | col1, col2, col3 FROM hoge | not built | not built |
not built | | |
===================
I used this query to get results of "\dX+".
===================
SELECT
stxnamespace::pg_catalog.regnamespace AS "Schema",
stxname AS "Name",
format('%s FROM %s',
(SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')
FROM pg_catalog.unnest(stxkeys) s(attnum)
JOIN pg_catalog.pg_attribute a
ON (stxrelid = a.attrelid
AND a.attnum = s.attnum
AND NOT attisdropped)),
stxrelid::regclass) AS "Definition",
CASE WHEN esd.stxdndistinct IS NOT NULL THEN 'built'
WHEN 'd' = any(stxkind) THEN 'not built'
END AS "N_distinct",
CASE WHEN esd.stxddependencies IS NOT NULL THEN 'built'
WHEN 'f' = any(stxkind) THEN 'not built'
END AS "Dependencies",
CASE WHEN esd.stxdmcv IS NOT NULL THEN 'built'
WHEN 'm' = any(stxkind) THEN 'not built'
END AS "Mcv",
pg_catalog.length(stxdndistinct) AS "N_size",
pg_catalog.length(stxddependencies) AS "D_size",
pg_catalog.length(stxdmcv) AS "M_size"
FROM pg_catalog.pg_statistic_ext es
INNER JOIN pg_catalog.pg_class c
ON stxrelid = c.oid
LEFT JOIN pg_catalog.pg_statistic_ext_data esd
ON es.oid = esd.stxoid
ORDER BY 1, 2;
===================
Attached patch includes:
- Replace "Columns" and "Table" column with "Definition"
- Show the status (built/not built/null) of extended stats by
using pg_statistic_ext_data
- Add "\dX+" command to show size of extended stats
Please find the attached file! :-D
Thanks,
Tatsuro Yamada
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index fc16e6c..5664c22 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1893,6 +1893,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 9902a4a..077a585 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -929,6 +929,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 d81f157..d99e387 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4368,6 +4368,98 @@ listEventTriggers(const char *pattern, bool verbose)
}
/*
+ * \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"
+
"stxnamespace::pg_catalog.regnamespace AS \"%s\", \n"
+ "stxname AS \"%s\", \n"
+ "pg_catalog.format('%%s FROM %%s', \n"
+ " (SELECT
pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ') \n"
+ " FROM pg_catalog.unnest(stxkeys)
s(attnum) \n"
+ " JOIN pg_catalog.pg_attribute a \n"
+ " ON (stxrelid = a.attrelid \n"
+ " AND a.attnum = s.attnum \n"
+ " AND NOT attisdropped)), \n"
+ " stxrelid::regclass) AS \"%s\", \n"
+ "CASE WHEN esd.stxdndistinct IS NOT
NULL THEN 'built' \n"
+ " WHEN 'd' = any(stxkind) THEN
'not built' \n"
+ "END AS \"%s\", \n"
+ "CASE WHEN esd.stxddependencies IS
NOT NULL THEN 'built' \n"
+ " WHEN 'f' = any(stxkind) THEN
'not built' \n"
+ "END AS \"%s\", \n"
+ "CASE WHEN esd.stxdmcv IS NOT NULL
THEN 'built' \n"
+ " WHEN 'm' = any(stxkind) THEN
'not built' \n"
+ "END AS \"%s\" \n",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Definition"),
+ gettext_noop("N_distinct"),
+ gettext_noop("Dependencies"),
+ gettext_noop("Mcv"));
+
+ if (verbose)
+ {
+ appendPQExpBuffer(&buf,
+ ",\n
pg_catalog.length(stxdndistinct) AS \"%s\", \n"
+
"pg_catalog.length(stxddependencies) AS \"%s\", \n"
+ "pg_catalog.length(stxdmcv)
AS \"%s\" \n",
+ gettext_noop("N_size"),
+ gettext_noop("D_size"),
+ gettext_noop("M_size"));
+
+ }
+
+ appendPQExpBufferStr(&buf,
+ "FROM
pg_catalog.pg_statistic_ext es \n"
+ "INNER JOIN
pg_catalog.pg_class c \n"
+ "ON stxrelid = c.oid \n"
+ "LEFT JOIN
pg_catalog.pg_statistic_ext_data esd \n"
+ "ON es.oid = esd.stxoid \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
*
* Describes casts.
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index f0e3ec9..89b13c3 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 af82928..ea249bf 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/test/regress/expected/psql.out
b/src/test/regress/expected/psql.out
index 555d464..76facc4 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5012,3 +5012,71 @@ 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;
+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
+--------+-----------+----------------------------+------------+--------------+-----------
+ 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 | not built | not built |
not built
+ public | stts_hoge | col1, col2, col3 FROM hoge | not built | not built |
not built
+(5 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 | not built | not built | not built
+(4 rows)
+
+\dX *hoge
+ List of extended statistics
+ Schema | Name | Definition | N_distinct | Dependencies |
Mcv
+--------+-----------+----------------------------+------------+--------------+-----------
+ public | stts_hoge | col1, col2, col3 FROM hoge | not built | not built |
not built
+(1 row)
+
+\dX+
+ 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 |
+ public | stts_2 | a, b FROM t1 | built | built |
| 13 | 40 |
+ public | stts_3 | a, b FROM t1 | built | built |
built | 13 | 40 | 6126
+ public | stts_4 | b, c FROM t2 | not built | not built |
not built | | |
+ public | stts_hoge | col1, col2, col3 FROM hoge | not built | not built |
not built | | |
+(5 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 |
+ public | stts_2 | a, b FROM t1 | built | built | |
13 | 40 |
+ public | stts_3 | a, b FROM t1 | built | built | built |
13 | 40 | 6126
+ public | stts_4 | b, c FROM t2 | not built | not built | not built |
| |
+(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 | not built | not built |
not built | | |
+(1 row)
+
+drop table t1, t2, hoge;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 5a16080..82f49a1 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1207,3 +1207,26 @@ 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;
+
+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;
