pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>
Subject: v13: show extended stats target in \d
Date: Mon, 31 Aug 2020 00:00:47 -0500
Message-ID: <20200831050047.GG5450@telsasoft.com> (raw )
The stats target can be set since commit d06215d03, but wasn't shown by psql.
ALTER STATISISTICS .. SET STATISTICS n.
Normal (1-D) stats targets are shown in \d+ table.
Stats objects are shown in \d (no plus).
Arguably, this should be shown only in "verbose" mode (\d+).
Attachments:
0001-Show-stats-target-of-extended-statistics.patch (text/x-diff, 4.3K) download
From 60a4033a04fbaafbb01c2bb2d73bb2fbe4d1da75 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sun, 30 Aug 2020 23:38:17 -0500
Subject: [PATCH 1/1] Show stats target of extended statistics
This can be set since d06215d03, but wasn't shown by psql.
Normal (1-D) stats targets are shown in \d+ table.
---
src/bin/psql/describe.c | 8 +++++++-
src/test/regress/expected/stats_ext.out | 18 ++++++++++++++++++
src/test/regress/sql/stats_ext.sql | 2 ++
3 files changed, 27 insertions(+), 1 deletion(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d81f1575bf..73befa36ee 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2683,10 +2683,12 @@ describeOneTableDetails(const char *schemaname,
" a.attnum = s.attnum AND NOT attisdropped)) AS columns,\n"
" 'd' = any(stxkind) AS ndist_enabled,\n"
" 'f' = any(stxkind) AS deps_enabled,\n"
- " 'm' = any(stxkind) AS mcv_enabled\n"
+ " 'm' = any(stxkind) AS mcv_enabled,\n"
+ " %s"
"FROM pg_catalog.pg_statistic_ext stat "
"WHERE stxrelid = '%s'\n"
"ORDER BY 1;",
+ (pset.sversion >= 130000 ? "stxstattarget\n" : "-1\n"),
oid);
result = PSQLexec(buf.data);
@@ -2732,6 +2734,10 @@ describeOneTableDetails(const char *schemaname,
PQgetvalue(result, i, 4),
PQgetvalue(result, i, 1));
+ if (strcmp(PQgetvalue(result, i, 8), "-1") != 0)
+ appendPQExpBuffer(&buf, " STATISTICS %s",
+ PQgetvalue(result, i, 8));
+
printTableAddFooter(&cont, buf.data);
}
}
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 0ae779a3b9..4dea48a2e8 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -102,6 +102,15 @@ WARNING: statistics object "public.ab1_a_b_stats" could not be computed for rel
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
+ Table "public.ab1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+ b | integer | | | | plain | |
+Statistics objects:
+ "public"."ab1_a_b_stats" (ndistinct, dependencies, mcv) ON a, b FROM ab1 STATISTICS 0
+
ANALYZE ab1;
SELECT stxname, stxdndistinct, stxddependencies, stxdmcv
FROM pg_statistic_ext s, pg_statistic_ext_data d
@@ -113,6 +122,15 @@ SELECT stxname, stxdndistinct, stxddependencies, stxdmcv
(1 row)
ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1;
+\d+ ab1
+ Table "public.ab1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+ b | integer | | | | plain | |
+Statistics objects:
+ "public"."ab1_a_b_stats" (ndistinct, dependencies, mcv) ON a, b FROM ab1
+
-- 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"
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 2834a902a7..d1d49948b4 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -72,12 +72,14 @@ ANALYZE ab1;
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
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;
ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1;
+\d+ ab1
-- partial analyze doesn't build stats either
ANALYZE ab1 (a);
ANALYZE ab1;
--
2.17.0
view thread (8+ messages) latest in thread
Message-ID: <20200831050047.GG5450@telsasoft.com>
Permalink: ../20200831050047.GG5450@telsasoft.com/
Also on: postgresql.org/message-id/20200831050047.GG5450@telsasoft.com
copy link · copy postgr.es · copy git am
reply 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: pryzby@telsasoft.com, tomas.vondra@2ndquadrant.com, tatsuro.yamada.tf@nttcom.co.jp
Subject: Re: v13: show extended stats target in \d
In-Reply-To: <20200831050047.GG5450@telsasoft.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