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

Commite76defb

Browse files
committed
Rework output format of pg_dependencies
The existing format of pg_dependencies uses a single-object JSONstructure, with each key value embedding all the knowledge about theset attributes tracked, like:{"1 => 5": 1.000000, "5 => 1": 0.423130}While this is a very compact format, it is confusing to read and it isdifficult to manipulate the values within the object, particularly whentracking multiple attributes.The new output format introduced in this commit is a JSON array ofobjects, with:- A key named "degree", with a float value.- A key named "attributes", with an array of attribute numbers.- A key named "dependency", with an attribute number.The values use the same underlying type as previously when printed, witha new output format that shows now as follows:[{"degree": 1.000000, "attributes": [1], "dependency": 5}, {"degree": 0.423130, "attributes": [5], "dependency": 1}]This new format will become handy for a follow-up set of changes, so asit becomes possible to inject extended statistics rather than require anANALYZE, like in a dump/restore sequence or after pg_upgrade on a newcluster.This format has been suggested by Tomas Vondra. The key names aredefined in the header introduced by1f927cc, to ease theintegration of frontend-specific changes that are still underdiscussion. (Again a personal note: if anybody comes up with bettername for the keys, of course feel free.)The bulk of the changes come from the regression tests, wherejsonb_pretty() is now used to make the outputs generated easier toparse.Author: Corey Huinker <corey.huinker@gmail.com>Reviewed-by: Jian He <jian.universality@gmail.com>Reviewed-by: Michael Paquier <michael@paquier.xyz>Discussion:https://postgr.es/m/CADkLM=dpz3KFnqP-dgJ-zvRvtjsa8UZv8wDAQdqho=qN3kX0Zg@mail.gmail.com
1 parent1f927cc commite76defb

File tree

5 files changed

+142
-33
lines changed

5 files changed

+142
-33
lines changed

‎doc/src/sgml/perform.sgml‎

Lines changed: 20 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1485,12 +1485,27 @@ CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes;
14851485

14861486
ANALYZE zipcodes;
14871487

1488-
SELECTstxname,stxkeys,stxddependencies
1489-
FROM pg_statistic_extjoin pg_statistic_ext_dataon (oid = stxoid)
1488+
SELECT stxkeys AS k, jsonb_pretty(stxddependencies::text::jsonb) AS dep
1489+
FROM pg_statistic_extJOIN pg_statistic_ext_dataON (oid = stxoid)
14901490
WHERE stxname = 'stts';
1491-
stxname | stxkeys | stxddependencies
1492-
---------+---------+------------------------------------------
1493-
stts | 1 5 | {"1 => 5": 1.000000, "5 => 1": 0.423130}
1491+
-[ RECORD 1 ]--------------------
1492+
k | 1 5
1493+
dep | [ +
1494+
| { +
1495+
| "degree": 1.000000,+
1496+
| "attributes": [ +
1497+
| 1 +
1498+
| ], +
1499+
| "dependency": 5 +
1500+
| }, +
1501+
| { +
1502+
| "degree": 0.423130,+
1503+
| "attributes": [ +
1504+
| 5 +
1505+
| ], +
1506+
| "dependency": 1 +
1507+
| } +
1508+
| ]
14941509
(1 row)
14951510
</programlisting>
14961511
Here it can be seen that column 1 (zip code) fully determines column

‎src/backend/utils/adt/pg_dependencies.c‎

Lines changed: 17 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@
1616

1717
#include"lib/stringinfo.h"
1818
#include"statistics/extended_stats_internal.h"
19+
#include"statistics/statistics_format.h"
1920
#include"utils/fmgrprotos.h"
2021

2122
/*
@@ -46,34 +47,34 @@ pg_dependencies_out(PG_FUNCTION_ARGS)
4647
{
4748
bytea*data=PG_GETARG_BYTEA_PP(0);
4849
MVDependencies*dependencies=statext_dependencies_deserialize(data);
49-
inti,
50-
j;
5150
StringInfoDatastr;
5251

5352
initStringInfo(&str);
54-
appendStringInfoChar(&str,'{');
53+
appendStringInfoChar(&str,'[');
5554

56-
for (i=0;i<dependencies->ndeps;i++)
55+
for (inti=0;i<dependencies->ndeps;i++)
5756
{
5857
MVDependency*dependency=dependencies->deps[i];
5958

6059
if (i>0)
6160
appendStringInfoString(&str,", ");
6261

63-
appendStringInfoChar(&str,'"');
64-
for (j=0;j<dependency->nattributes;j++)
65-
{
66-
if (j==dependency->nattributes-1)
67-
appendStringInfoString(&str," => ");
68-
elseif (j>0)
69-
appendStringInfoString(&str,", ");
70-
71-
appendStringInfo(&str,"%d",dependency->attributes[j]);
72-
}
73-
appendStringInfo(&str,"\": %f",dependency->degree);
62+
if (dependency->nattributes <=1)
63+
elog(ERROR,"invalid zero-length nattributes array in MVDependencies");
64+
65+
appendStringInfo(&str,"{\""PG_DEPENDENCIES_KEY_ATTRIBUTES"\": [%d",
66+
dependency->attributes[0]);
67+
68+
for (intj=1;j<dependency->nattributes-1;j++)
69+
appendStringInfo(&str,", %d",dependency->attributes[j]);
70+
71+
appendStringInfo(&str,"], \""PG_DEPENDENCIES_KEY_DEPENDENCY"\": %d, "
72+
"\""PG_DEPENDENCIES_KEY_DEGREE"\": %f}",
73+
dependency->attributes[dependency->nattributes-1],
74+
dependency->degree);
7475
}
7576

76-
appendStringInfoChar(&str,'}');
77+
appendStringInfoChar(&str,']');
7778

7879
PG_RETURN_CSTRING(str.data);
7980
}

‎src/include/statistics/statistics_format.h‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,4 +29,19 @@
2929
#definePG_NDISTINCT_KEY_ATTRIBUTES"attributes"
3030
#definePG_NDISTINCT_KEY_NDISTINCT"ndistinct"
3131

32+
33+
/* ----------
34+
* pg_dependencies in human-readable format is a JSON array made of elements
35+
* with a predefined set of keys, like:
36+
*
37+
* [{"degree": 1.000000, "attributes": [3], "dependency": 4},
38+
* {"degree": 1.000000, "attributes": [3], "dependency": 6},
39+
* ... ]
40+
* ----------
41+
*/
42+
43+
#definePG_DEPENDENCIES_KEY_ATTRIBUTES"attributes"
44+
#definePG_DEPENDENCIES_KEY_DEPENDENCY"dependency"
45+
#definePG_DEPENDENCIES_KEY_DEGREE"degree"
46+
3247
#endif/* STATISTICS_FORMAT_H */

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

Lines changed: 86 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -196,7 +196,8 @@ Statistics objects:
196196
"public.ab1_a_b_stats" ON a, b FROM ab1; STATISTICS 0
197197

198198
ANALYZE ab1;
199-
SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct, stxddependencies, stxdmcv, stxdinherit
199+
SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct,
200+
jsonb_pretty(d.stxddependencies::text::jsonb) AS stxddependencies, stxdmcv, stxdinherit
200201
FROM pg_statistic_ext s LEFT JOIN pg_statistic_ext_data d ON (d.stxoid = s.oid)
201202
WHERE s.stxname = 'ab1_a_b_stats';
202203
stxname | stxdndistinct | stxddependencies | stxdmcv | stxdinherit
@@ -1433,10 +1434,48 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
14331434
CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
14341435
ANALYZE functional_dependencies;
14351436
-- print the detected dependencies
1436-
SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
1437-
dependencies
1438-
------------------------------------------------------------------------------------------------------------
1439-
{"3 => 4": 1.000000, "3 => 6": 1.000000, "4 => 6": 1.000000, "3, 4 => 6": 1.000000, "3, 6 => 4": 1.000000}
1437+
SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
1438+
dependencies
1439+
-----------------------------
1440+
[ +
1441+
{ +
1442+
"degree": 1.000000,+
1443+
"attributes": [ +
1444+
3 +
1445+
], +
1446+
"dependency": 4 +
1447+
}, +
1448+
{ +
1449+
"degree": 1.000000,+
1450+
"attributes": [ +
1451+
3 +
1452+
], +
1453+
"dependency": 6 +
1454+
}, +
1455+
{ +
1456+
"degree": 1.000000,+
1457+
"attributes": [ +
1458+
4 +
1459+
], +
1460+
"dependency": 6 +
1461+
}, +
1462+
{ +
1463+
"degree": 1.000000,+
1464+
"attributes": [ +
1465+
3, +
1466+
4 +
1467+
], +
1468+
"dependency": 6 +
1469+
}, +
1470+
{ +
1471+
"degree": 1.000000,+
1472+
"attributes": [ +
1473+
3, +
1474+
6 +
1475+
], +
1476+
"dependency": 4 +
1477+
} +
1478+
]
14401479
(1 row)
14411480

14421481
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
@@ -1775,10 +1814,48 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
17751814
CREATE STATISTICS func_deps_stat (dependencies) ON (a * 2), upper(b), (c + 1) FROM functional_dependencies;
17761815
ANALYZE functional_dependencies;
17771816
-- print the detected dependencies
1778-
SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
1779-
dependencies
1780-
------------------------------------------------------------------------------------------------------------------------
1781-
{"-1 => -2": 1.000000, "-1 => -3": 1.000000, "-2 => -3": 1.000000, "-1, -2 => -3": 1.000000, "-1, -3 => -2": 1.000000}
1817+
SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
1818+
dependencies
1819+
-----------------------------
1820+
[ +
1821+
{ +
1822+
"degree": 1.000000,+
1823+
"attributes": [ +
1824+
-1 +
1825+
], +
1826+
"dependency": -2 +
1827+
}, +
1828+
{ +
1829+
"degree": 1.000000,+
1830+
"attributes": [ +
1831+
-1 +
1832+
], +
1833+
"dependency": -3 +
1834+
}, +
1835+
{ +
1836+
"degree": 1.000000,+
1837+
"attributes": [ +
1838+
-2 +
1839+
], +
1840+
"dependency": -3 +
1841+
}, +
1842+
{ +
1843+
"degree": 1.000000,+
1844+
"attributes": [ +
1845+
-1, +
1846+
-2 +
1847+
], +
1848+
"dependency": -3 +
1849+
}, +
1850+
{ +
1851+
"degree": 1.000000,+
1852+
"attributes": [ +
1853+
-1, +
1854+
-3 +
1855+
], +
1856+
"dependency": -2 +
1857+
} +
1858+
]
17821859
(1 row)
17831860

17841861
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = 2 AND upper(b) = ''1''');

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

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -125,7 +125,8 @@ ALTER TABLE ab1 ALTER a SET STATISTICS -1;
125125
ALTER STATISTICS ab1_a_b_statsSET STATISTICS0;
126126
\d ab1
127127
ANALYZE ab1;
128-
SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb)AS stxdndistinct, stxddependencies, stxdmcv, stxdinherit
128+
SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb)AS stxdndistinct,
129+
jsonb_pretty(d.stxddependencies::text::jsonb)AS stxddependencies, stxdmcv, stxdinherit
129130
FROM pg_statistic_ext sLEFT JOIN pg_statistic_ext_data dON (d.stxoid=s.oid)
130131
WHEREs.stxname='ab1_a_b_stats';
131132
ALTER STATISTICS ab1_a_b_statsSET STATISTICS-1;
@@ -708,7 +709,7 @@ CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_depen
708709
ANALYZE functional_dependencies;
709710

710711
-- print the detected dependencies
711-
SELECT dependenciesFROM pg_stats_extWHERE statistics_name='func_deps_stat';
712+
SELECTjsonb_pretty(dependencies::text::jsonb)ASdependenciesFROM pg_stats_extWHERE statistics_name='func_deps_stat';
712713

713714
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b =''1''');
714715

@@ -844,7 +845,7 @@ CREATE STATISTICS func_deps_stat (dependencies) ON (a * 2), upper(b), (c + 1) FR
844845
ANALYZE functional_dependencies;
845846

846847
-- print the detected dependencies
847-
SELECT dependenciesFROM pg_stats_extWHERE statistics_name='func_deps_stat';
848+
SELECTjsonb_pretty(dependencies::text::jsonb)ASdependenciesFROM pg_stats_extWHERE statistics_name='func_deps_stat';
848849

849850
SELECT*FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = 2 AND upper(b) =''1''');
850851

0 commit comments

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