From e76defbcf09e22941d8cea462e2deef36d43fa04 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Mon, 17 Nov 2025 10:44:26 +0900 Subject: Rework output format of pg_dependencies The existing format of pg_dependencies uses a single-object JSON structure, with each key value embedding all the knowledge about the set 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 is difficult to manipulate the values within the object, particularly when tracking multiple attributes. The new output format introduced in this commit is a JSON array of objects, 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, with a 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 as it becomes possible to inject extended statistics rather than require an ANALYZE, like in a dump/restore sequence or after pg_upgrade on a new cluster. This format has been suggested by Tomas Vondra. The key names are defined in the header introduced by 1f927cce4498, to ease the integration of frontend-specific changes that are still under discussion. (Again a personal note: if anybody comes up with better name for the keys, of course feel free.) The bulk of the changes come from the regression tests, where jsonb_pretty() is now used to make the outputs generated easier to parse. Author: Corey Huinker Reviewed-by: Jian He Reviewed-by: Michael Paquier Discussion: https://postgr.es/m/CADkLM=dpz3KFnqP-dgJ-zvRvtjsa8UZv8wDAQdqho=qN3kX0Zg@mail.gmail.com --- doc/src/sgml/perform.sgml | 25 ++++++++++++++++++++----- 1 file changed, 20 insertions(+), 5 deletions(-) (limited to 'doc') diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index a88690150a8..5f6f1db0467 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -1485,12 +1485,27 @@ CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes; ANALYZE zipcodes; -SELECT stxname, stxkeys, stxddependencies - FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) +SELECT stxkeys AS k, jsonb_pretty(stxddependencies::text::jsonb) AS dep + FROM pg_statistic_ext JOIN pg_statistic_ext_data ON (oid = stxoid) WHERE stxname = 'stts'; - stxname | stxkeys | stxddependencies ----------+---------+------------------------------------------ - stts | 1 5 | {"1 => 5": 1.000000, "5 => 1": 0.423130} +-[ RECORD 1 ]-------------------- +k | 1 5 +dep | [ + + | { + + | "degree": 1.000000,+ + | "attributes": [ + + | 1 + + | ], + + | "dependency": 5 + + | }, + + | { + + | "degree": 0.423130,+ + | "attributes": [ + + | 5 + + | ], + + | "dependency": 1 + + | } + + | ] (1 row) Here it can be seen that column 1 (zip code) fully determines column -- cgit v1.2.3