Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitad600bb

Browse files
committed
psql \dX: list extended statistics objects
The new command lists extended statistics objects. All past releaseswith extended statistics are supported.This is a simplified version of commit891a1d0, which had to bereverted due to not considering pg_statistic_ext_data is not accessibleby regular users. Fields requiring access to this catalog were removed.It's possible to add them, but it'll require changes to core.Author: Tatsuro YamadaReviewed-by: Julien Rouhaud, Alvaro Herrera, Tomas Vondra, Noriyoshi ShinodaDiscussion:https://postgr.es/m/c027a541-5856-75a5-0868-341301e1624b%40nttcom.co.jp_1
1 parent9d23c15 commitad600bb

File tree

8 files changed

+267
-1
lines changed

8 files changed

+267
-1
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1919,6 +1919,27 @@ testdb=>
19191919
</listitem>
19201920
</varlistentry>
19211921

1922+
<varlistentry>
1923+
<term><literal>\dX [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
1924+
<listitem>
1925+
<para>
1926+
Lists extended statistics.
1927+
If <replaceable class="parameter">pattern</replaceable>
1928+
is specified, only those extended statistics whose names match the
1929+
pattern are listed.
1930+
</para>
1931+
1932+
<para>
1933+
The column of the kind of extended stats (e.g. Ndistinct) shows its status.
1934+
NULL means that it doesn't exists. "defined" means that it was requested
1935+
when creating the statistics.
1936+
You can use pg_stats_ext if you'd like to know whether <link linkend="sql-analyze">
1937+
<command>ANALYZE</command></link> was run and statistics are available to the
1938+
planner.
1939+
</para>
1940+
</listitem>
1941+
</varlistentry>
1942+
19221943
<varlistentry>
19231944
<term><literal>\dy[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
19241945
<listitem>

‎src/bin/psql/command.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -928,6 +928,9 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
928928
else
929929
success=listExtensions(pattern);
930930
break;
931+
case'X':/* Extended Statistics */
932+
success=listExtendedStats(pattern);
933+
break;
931934
case'y':/* Event Triggers */
932935
success=listEventTriggers(pattern,show_verbose);
933936
break;

‎src/bin/psql/describe.c

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4392,6 +4392,89 @@ listEventTriggers(const char *pattern, bool verbose)
43924392
return true;
43934393
}
43944394

4395+
/*
4396+
* \dX
4397+
*
4398+
* Describes extended statistics.
4399+
*/
4400+
bool
4401+
listExtendedStats(constchar*pattern)
4402+
{
4403+
PQExpBufferDatabuf;
4404+
PGresult*res;
4405+
printQueryOptmyopt=pset.popt;
4406+
4407+
if (pset.sversion<100000)
4408+
{
4409+
charsverbuf[32];
4410+
4411+
pg_log_error("The server (version %s) does not support extended statistics.",
4412+
formatPGVersionNumber(pset.sversion, false,
4413+
sverbuf,sizeof(sverbuf)));
4414+
return true;
4415+
}
4416+
4417+
initPQExpBuffer(&buf);
4418+
printfPQExpBuffer(&buf,
4419+
"SELECT \n"
4420+
"es.stxnamespace::pg_catalog.regnamespace::text AS \"%s\", \n"
4421+
"es.stxname AS \"%s\", \n"
4422+
"pg_catalog.format('%%s FROM %%s', \n"
4423+
" (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(a.attname),', ') \n"
4424+
" FROM pg_catalog.unnest(es.stxkeys) s(attnum) \n"
4425+
" JOIN pg_catalog.pg_attribute a \n"
4426+
" ON (es.stxrelid = a.attrelid \n"
4427+
" AND a.attnum = s.attnum \n"
4428+
" AND NOT a.attisdropped)), \n"
4429+
"es.stxrelid::regclass) AS \"%s\"",
4430+
gettext_noop("Schema"),
4431+
gettext_noop("Name"),
4432+
gettext_noop("Definition"));
4433+
4434+
appendPQExpBuffer(&buf,
4435+
",\nCASE WHEN 'd' = any(es.stxkind) THEN 'defined' \n"
4436+
"END AS \"%s\", \n"
4437+
"CASE WHEN 'f' = any(es.stxkind) THEN 'defined' \n"
4438+
"END AS \"%s\"",
4439+
gettext_noop("Ndistinct"),
4440+
gettext_noop("Dependencies"));
4441+
4442+
/*
4443+
* Include the MCV statistics kind.
4444+
*/
4445+
if (pset.sversion >=120000)
4446+
{
4447+
appendPQExpBuffer(&buf,
4448+
",\nCASE WHEN 'm' = any(es.stxkind) THEN 'defined' \n"
4449+
"END AS \"%s\" ",
4450+
gettext_noop("MCV"));
4451+
}
4452+
4453+
appendPQExpBufferStr(&buf,
4454+
" \nFROM pg_catalog.pg_statistic_ext es \n");
4455+
4456+
processSQLNamePattern(pset.db,&buf,pattern,
4457+
false, false,
4458+
"es.stxnamespace::pg_catalog.regnamespace::text","es.stxname",
4459+
NULL,NULL);
4460+
4461+
appendPQExpBufferStr(&buf,"ORDER BY 1, 2;");
4462+
4463+
res=PSQLexec(buf.data);
4464+
termPQExpBuffer(&buf);
4465+
if (!res)
4466+
return false;
4467+
4468+
myopt.nullPrint=NULL;
4469+
myopt.title=_("List of extended statistics");
4470+
myopt.translate_header= true;
4471+
4472+
printQuery(res,&myopt,pset.queryFout, false,pset.logfile);
4473+
4474+
PQclear(res);
4475+
return true;
4476+
}
4477+
43954478
/*
43964479
* \dC
43974480
*

‎src/bin/psql/describe.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -102,6 +102,9 @@ extern bool listExtensions(const char *pattern);
102102
/* \dx+ */
103103
externboollistExtensionContents(constchar*pattern);
104104

105+
/* \dX */
106+
externboollistExtendedStats(constchar*pattern);
107+
105108
/* \dy */
106109
externboollistEventTriggers(constchar*pattern,boolverbose);
107110

‎src/bin/psql/help.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -267,6 +267,7 @@ slashUsage(unsigned short int pager)
267267
fprintf(output,_(" \\du[S+] [PATTERN] list roles\n"));
268268
fprintf(output,_(" \\dv[S+] [PATTERN] list views\n"));
269269
fprintf(output,_(" \\dx[+] [PATTERN] list extensions\n"));
270+
fprintf(output,_(" \\dX [PATTERN] list extended statistics\n"));
270271
fprintf(output,_(" \\dy [PATTERN] list event triggers\n"));
271272
fprintf(output,_(" \\l[+] [PATTERN] list databases\n"));
272273
fprintf(output,_(" \\sf[+] FUNCNAME show a function's definition\n"));

‎src/bin/psql/tab-complete.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1505,7 +1505,7 @@ psql_completion(const char *text, int start, int end)
15051505
"\\dF","\\dFd","\\dFp","\\dFt","\\dg","\\di","\\dl","\\dL",
15061506
"\\dm","\\dn","\\do","\\dO","\\dp","\\dP","\\dPi","\\dPt",
15071507
"\\drds","\\dRs","\\dRp","\\ds","\\dS",
1508-
"\\dt","\\dT","\\dv","\\du","\\dx","\\dy",
1508+
"\\dt","\\dT","\\dv","\\du","\\dx","\\dX","\\dy",
15091509
"\\e","\\echo","\\ef","\\elif","\\else","\\encoding",
15101510
"\\endif","\\errverbose","\\ev",
15111511
"\\f",
@@ -3974,6 +3974,8 @@ psql_completion(const char *text, int start, int end)
39743974
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views,NULL);
39753975
elseif (TailMatchesCS("\\dx*"))
39763976
COMPLETE_WITH_QUERY(Query_for_list_of_extensions);
3977+
elseif (TailMatchesCS("\\dX*"))
3978+
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_statistics,NULL);
39773979
elseif (TailMatchesCS("\\dm*"))
39783980
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews,NULL);
39793981
elseif (TailMatchesCS("\\dE*"))

‎src/test/regress/expected/stats_ext.out

Lines changed: 116 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1727,6 +1727,122 @@ INSERT INTO tststats.priv_test_tbl
17271727
CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
17281728
FROM tststats.priv_test_tbl;
17291729
ANALYZE tststats.priv_test_tbl;
1730+
-- Check printing info about extended statistics by \dX
1731+
create table stts_t1 (a int, b int);
1732+
create statistics stts_1 (ndistinct) on a, b from stts_t1;
1733+
create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;
1734+
create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;
1735+
create table stts_t2 (a int, b int, c int);
1736+
create statistics stts_4 on b, c from stts_t2;
1737+
create table stts_t3 (col1 int, col2 int, col3 int);
1738+
create statistics stts_hoge on col1, col2, col3 from stts_t3;
1739+
create schema stts_s1;
1740+
create schema stts_s2;
1741+
create statistics stts_s1.stts_foo on col1, col2 from stts_t3;
1742+
create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from stts_t3;
1743+
insert into stts_t1 select i,i from generate_series(1,100) i;
1744+
analyze stts_t1;
1745+
\dX
1746+
List of extended statistics
1747+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
1748+
----------+------------------------+--------------------------------------+-----------+--------------+---------
1749+
public | func_deps_stat | a, b, c FROM functional_dependencies | | defined |
1750+
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
1751+
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
1752+
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
1753+
public | stts_1 | a, b FROM stts_t1 | defined | |
1754+
public | stts_2 | a, b FROM stts_t1 | defined | defined |
1755+
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
1756+
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
1757+
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
1758+
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
1759+
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
1760+
tststats | priv_test_stats | a, b FROM tststats.priv_test_tbl | | | defined
1761+
(12 rows)
1762+
1763+
\dX stts_?
1764+
List of extended statistics
1765+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
1766+
--------+--------+-------------------+-----------+--------------+---------
1767+
public | stts_1 | a, b FROM stts_t1 | defined | |
1768+
public | stts_2 | a, b FROM stts_t1 | defined | defined |
1769+
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
1770+
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
1771+
(4 rows)
1772+
1773+
\dX *stts_hoge
1774+
List of extended statistics
1775+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
1776+
--------+-----------+-------------------------------+-----------+--------------+---------
1777+
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
1778+
(1 row)
1779+
1780+
\dX+
1781+
List of extended statistics
1782+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
1783+
----------+------------------------+--------------------------------------+-----------+--------------+---------
1784+
public | func_deps_stat | a, b, c FROM functional_dependencies | | defined |
1785+
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
1786+
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
1787+
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
1788+
public | stts_1 | a, b FROM stts_t1 | defined | |
1789+
public | stts_2 | a, b FROM stts_t1 | defined | defined |
1790+
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
1791+
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
1792+
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
1793+
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
1794+
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
1795+
tststats | priv_test_stats | a, b FROM tststats.priv_test_tbl | | | defined
1796+
(12 rows)
1797+
1798+
\dX+ stts_?
1799+
List of extended statistics
1800+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
1801+
--------+--------+-------------------+-----------+--------------+---------
1802+
public | stts_1 | a, b FROM stts_t1 | defined | |
1803+
public | stts_2 | a, b FROM stts_t1 | defined | defined |
1804+
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
1805+
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
1806+
(4 rows)
1807+
1808+
\dX+ *stts_hoge
1809+
List of extended statistics
1810+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
1811+
--------+-----------+-------------------------------+-----------+--------------+---------
1812+
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
1813+
(1 row)
1814+
1815+
\dX+ stts_s2.stts_yama
1816+
List of extended statistics
1817+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
1818+
---------+-----------+-------------------------+-----------+--------------+---------
1819+
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
1820+
(1 row)
1821+
1822+
create role regress_stats_ext nosuperuser;
1823+
set role regress_stats_ext;
1824+
\dX
1825+
List of extended statistics
1826+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
1827+
----------+------------------------+--------------------------------------+-----------+--------------+---------
1828+
public | func_deps_stat | a, b, c FROM functional_dependencies | | defined |
1829+
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
1830+
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
1831+
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
1832+
public | stts_1 | a, b FROM stts_t1 | defined | |
1833+
public | stts_2 | a, b FROM stts_t1 | defined | defined |
1834+
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
1835+
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
1836+
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
1837+
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
1838+
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
1839+
tststats | priv_test_stats | a, b FROM tststats.priv_test_tbl | | | defined
1840+
(12 rows)
1841+
1842+
reset role;
1843+
drop table stts_t1, stts_t2, stts_t3;
1844+
drop schema stts_s1, stts_s2 cascade;
1845+
drop user regress_stats_ext;
17301846
-- User with no access
17311847
CREATE USER regress_stats_user1;
17321848
GRANT USAGE ON SCHEMA tststats TO regress_stats_user1;

‎src/test/regress/sql/stats_ext.sql

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -914,6 +914,43 @@ CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
914914

915915
ANALYZEtststats.priv_test_tbl;
916916

917+
-- Check printing info about extended statistics by \dX
918+
createtablestts_t1 (aint, bint);
919+
create statistics stts_1 (ndistinct)on a, bfrom stts_t1;
920+
create statistics stts_2 (ndistinct, dependencies)on a, bfrom stts_t1;
921+
create statistics stts_3 (ndistinct, dependencies, mcv)on a, bfrom stts_t1;
922+
923+
createtablestts_t2 (aint, bint, cint);
924+
create statistics stts_4on b, cfrom stts_t2;
925+
926+
createtablestts_t3 (col1int, col2int, col3int);
927+
create statistics stts_hogeon col1, col2, col3from stts_t3;
928+
929+
createschemastts_s1;
930+
createschemastts_s2;
931+
create statisticsstts_s1.stts_fooon col1, col2from stts_t3;
932+
create statisticsstts_s2.stts_yama (dependencies, mcv)on col1, col3from stts_t3;
933+
934+
insert into stts_t1select i,ifrom generate_series(1,100) i;
935+
analyze stts_t1;
936+
937+
\dX
938+
\dX stts_?
939+
\dX*stts_hoge
940+
\dX+
941+
\dX+ stts_?
942+
\dX+*stts_hoge
943+
\dX+stts_s2.stts_yama
944+
945+
create role regress_stats_ext nosuperuser;
946+
set role regress_stats_ext;
947+
\dX
948+
reset role;
949+
950+
droptable stts_t1, stts_t2, stts_t3;
951+
dropschema stts_s1, stts_s2 cascade;
952+
dropuser regress_stats_ext;
953+
917954
-- User with no access
918955
CREATEUSERregress_stats_user1;
919956
GRANT USAGEON SCHEMA tststats TO regress_stats_user1;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp