summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/perform.sgml25
-rw-r--r--src/backend/utils/adt/pg_dependencies.c33
-rw-r--r--src/include/statistics/statistics_format.h15
-rw-r--r--src/test/regress/expected/stats_ext.out95
-rw-r--r--src/test/regress/sql/stats_ext.sql7
5 files changed, 142 insertions, 33 deletions
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)
</programlisting>
Here it can be seen that column 1 (zip code) fully determines column
diff --git a/src/backend/utils/adt/pg_dependencies.c b/src/backend/utils/adt/pg_dependencies.c
index a0a9440fd5c..87181aa00e9 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, ", ");
-
- appendStringInfo(&str, "%d", dependency->attributes[j]);
- }
- appendStringInfo(&str, "\": %f", dependency->degree);
+ if (dependency->nattributes <= 1)
+ elog(ERROR, "invalid zero-length nattributes array in MVDependencies");
+
+ 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/include/statistics/statistics_format.h b/src/include/statistics/statistics_format.h
index fb0ae58ff3b..0d3ce72d081 100644
--- a/src/include/statistics/statistics_format.h
+++ b/src/include/statistics/statistics_format.h
@@ -29,4 +29,19 @@
#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/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index e9379afe39e..5a4077f8ed5 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 fc4aee6d839..94e2139c504 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''');
generated by cgit v1.2.3 (git 2.39.1) at 2025-11-17 03:09:59 +0000