From a13f1574a98dab7abd853fde521a83afd06df02a Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Tue, 11 Nov 2025 14:15:27 +0900 Subject: [PATCH v10 4/9] Refactor output format of pg_dependencies. The existing format of pg_dependencies uses a single-object JSON structure where each key is itself a comma-separated list of attnums. While this is a very compact format, it's confusing to read and is difficult to manipulate values within the object. This wasn't a concern until statistics import functions were introduced, enabling users to inject hypothetical statistics into an object to observe their effect on the query planner. The new format is an array of objects, each object must have the keys "attributes", which must contain an array of attnums, "dependency", which must be an integer, and "degree", which must be a float. The change in format is adequately described from the changes to src/test/regress/expected/stats_ext.out so description here is redundant. --- src/include/statistics/statistics_format.h | 20 ++++- src/backend/utils/adt/pg_dependencies.c | 31 +++---- src/test/regress/expected/stats_ext.out | 95 ++++++++++++++++++++-- src/test/regress/sql/stats_ext.sql | 7 +- 4 files changed, 124 insertions(+), 29 deletions(-) diff --git a/src/include/statistics/statistics_format.h b/src/include/statistics/statistics_format.h index ba97c0880be1..40655b9ec3b1 100644 --- a/src/include/statistics/statistics_format.h +++ b/src/include/statistics/statistics_format.h @@ -20,11 +20,27 @@ * * [{"ndistinct": 11, "attributes": [3,4]}, * {"ndistinct": 11, "attributes": [3,6]}, - * {"ndistinct": 11, "attributes": [4,6]}, - * {"ndistinct": 11, "attributes": [3,4,6]}] + * ... ] + * * ---------- */ #define PG_NDISTINCT_KEY_ATTRIBUTES "attributes" #define PG_NDISTINCT_KEY_NDISTINCT "ndistinct" + +/* ---------- + * pg_dependencies in human-readable format is a JSON array made of elements + * with a predefined set of keys, like: + * + * [{"degree": 1.000000, "attributes": [3], "dependency": 4}, + * {"degree": 1.000000, "attributes": [3], "dependency": 6}, + * ... ] + * + * ---------- + */ + +#define PG_DEPENDENCIES_KEY_ATTRIBUTES "attributes" +#define PG_DEPENDENCIES_KEY_DEPENDENCY "dependency" +#define PG_DEPENDENCIES_KEY_DEGREE "degree" + #endif /* STATISTICS_FORMAT_H */ diff --git a/src/backend/utils/adt/pg_dependencies.c b/src/backend/utils/adt/pg_dependencies.c index a4f7c48683f5..05d0102c09cc 100644 --- a/src/backend/utils/adt/pg_dependencies.c +++ b/src/backend/utils/adt/pg_dependencies.c @@ -16,6 +16,7 @@ #include "lib/stringinfo.h" #include "statistics/extended_stats_internal.h" +#include "statistics/statistics_format.h" #include "utils/fmgrprotos.h" /* @@ -46,34 +47,34 @@ pg_dependencies_out(PG_FUNCTION_ARGS) { bytea *data = PG_GETARG_BYTEA_PP(0); MVDependencies *dependencies = statext_dependencies_deserialize(data); - int i, - j; StringInfoData str; initStringInfo(&str); - appendStringInfoChar(&str, '{'); + appendStringInfoChar(&str, '['); - for (i = 0; i < dependencies->ndeps; i++) + for (int i = 0; i < dependencies->ndeps; i++) { MVDependency *dependency = dependencies->deps[i]; if (i > 0) appendStringInfoString(&str, ", "); - appendStringInfoChar(&str, '"'); - for (j = 0; j < dependency->nattributes; j++) - { - if (j == dependency->nattributes - 1) - appendStringInfoString(&str, " => "); - else if (j > 0) - appendStringInfoString(&str, ", "); + if (dependency->nattributes <= 1) + elog(ERROR, "invalid zero-length nattributes array in MVDependencies"); - appendStringInfo(&str, "%d", dependency->attributes[j]); - } - appendStringInfo(&str, "\": %f", dependency->degree); + appendStringInfo(&str, "{\"" PG_DEPENDENCIES_KEY_ATTRIBUTES "\": [%d", + dependency->attributes[0]); + + for (int j = 1; j < dependency->nattributes - 1; j++) + appendStringInfo(&str, ", %d", dependency->attributes[j]); + + appendStringInfo(&str, "], \"" PG_DEPENDENCIES_KEY_DEPENDENCY "\": %d, " + "\"" PG_DEPENDENCIES_KEY_DEGREE "\": %f}", + dependency->attributes[dependency->nattributes - 1], + dependency->degree); } - appendStringInfoChar(&str, '}'); + appendStringInfoChar(&str, ']'); PG_RETURN_CSTRING(str.data); } diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 2dc771369e52..3bf55353fe8e 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -196,7 +196,8 @@ Statistics objects: "public.ab1_a_b_stats" ON a, b FROM ab1; STATISTICS 0 ANALYZE ab1; -SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct, stxddependencies, stxdmcv, stxdinherit +SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct, + jsonb_pretty(d.stxddependencies::text::jsonb) AS stxddependencies, stxdmcv, stxdinherit FROM pg_statistic_ext s LEFT JOIN pg_statistic_ext_data d ON (d.stxoid = s.oid) WHERE s.stxname = 'ab1_a_b_stats'; stxname | stxdndistinct | stxddependencies | stxdmcv | stxdinherit @@ -1433,10 +1434,48 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies; ANALYZE functional_dependencies; -- print the detected dependencies -SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; - dependencies ------------------------------------------------------------------------------------------------------------- - {"3 => 4": 1.000000, "3 => 6": 1.000000, "4 => 6": 1.000000, "3, 4 => 6": 1.000000, "3, 6 => 4": 1.000000} +SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; + dependencies +----------------------------- + [ + + { + + "degree": 1.000000,+ + "attributes": [ + + 3 + + ], + + "dependency": 4 + + }, + + { + + "degree": 1.000000,+ + "attributes": [ + + 3 + + ], + + "dependency": 6 + + }, + + { + + "degree": 1.000000,+ + "attributes": [ + + 4 + + ], + + "dependency": 6 + + }, + + { + + "degree": 1.000000,+ + "attributes": [ + + 3, + + 4 + + ], + + "dependency": 6 + + }, + + { + + "degree": 1.000000,+ + "attributes": [ + + 3, + + 6 + + ], + + "dependency": 4 + + } + + ] (1 row) 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 CREATE STATISTICS func_deps_stat (dependencies) ON (a * 2), upper(b), (c + 1) FROM functional_dependencies; ANALYZE functional_dependencies; -- print the detected dependencies -SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; - dependencies ------------------------------------------------------------------------------------------------------------------------- - {"-1 => -2": 1.000000, "-1 => -3": 1.000000, "-2 => -3": 1.000000, "-1, -2 => -3": 1.000000, "-1, -3 => -2": 1.000000} +SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; + dependencies +----------------------------- + [ + + { + + "degree": 1.000000,+ + "attributes": [ + + -1 + + ], + + "dependency": -2 + + }, + + { + + "degree": 1.000000,+ + "attributes": [ + + -1 + + ], + + "dependency": -3 + + }, + + { + + "degree": 1.000000,+ + "attributes": [ + + -2 + + ], + + "dependency": -3 + + }, + + { + + "degree": 1.000000,+ + "attributes": [ + + -1, + + -2 + + ], + + "dependency": -3 + + }, + + { + + "degree": 1.000000,+ + "attributes": [ + + -1, + + -3 + + ], + + "dependency": -2 + + } + + ] (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = 2 AND upper(b) = ''1'''); diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 207c431e68c9..a47cfc80eef2 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -125,7 +125,8 @@ ALTER TABLE ab1 ALTER a SET STATISTICS -1; ALTER STATISTICS ab1_a_b_stats SET STATISTICS 0; \d ab1 ANALYZE ab1; -SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct, stxddependencies, stxdmcv, stxdinherit +SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct, + jsonb_pretty(d.stxddependencies::text::jsonb) AS stxddependencies, stxdmcv, stxdinherit FROM pg_statistic_ext s LEFT JOIN pg_statistic_ext_data d ON (d.stxoid = s.oid) WHERE s.stxname = 'ab1_a_b_stats'; ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1; @@ -708,7 +709,7 @@ CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_depen ANALYZE functional_dependencies; -- print the detected dependencies -SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; +SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'''); @@ -844,7 +845,7 @@ CREATE STATISTICS func_deps_stat (dependencies) ON (a * 2), upper(b), (c + 1) FR ANALYZE functional_dependencies; -- print the detected dependencies -SELECT dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; +SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = 2 AND upper(b) = ''1'''); -- 2.51.0