From 2f04110225ab30fbd9889d6f5d73fd6b3e1b308b Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Fri, 5 Dec 2025 14:15:21 +0900 Subject: Improve test output of extended statistics for ndistinct and dependencies Corey Huinker has come up with a recipe that is more compact and more pleasant to the eye for extended stats because we know that all of them are 1-dimension JSON arrays. This commit switches the extended stats tests to use replace() instead of jsonb_pretty(), splitting the data so as one line is used for each item in the extended stats object. This results in the removal of a good chunk of test output, that is now easier to debug with one line used for each item in a stats object. This patch has not been provided by Corey. This is some post-commit cleanup work that I have noticed as good enough to do on its own while reviewing the rest of the patch set Corey has posted. Discussion: https://postgr.es/m/CADkLM=csMd52i39Ye8-PUUHyzBb3546eSCUTh-FBQ7bzT2uZ4Q@mail.gmail.com --- src/test/regress/expected/stats_ext.out | 272 ++++++-------------------------- src/test/regress/sql/stats_ext.sql | 20 +-- 2 files changed, 60 insertions(+), 232 deletions(-) (limited to 'src') diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index eb70ea5a2ee..b2a06579135 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -196,8 +196,10 @@ 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, - jsonb_pretty(d.stxddependencies::text::jsonb) AS stxddependencies, stxdmcv, stxdinherit +SELECT stxname, + replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct, + replace(d.stxddependencies, '}, ', E'},\n') 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 @@ -477,43 +479,16 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ( -- correct command CREATE STATISTICS s10 ON a, b, c FROM ndistinct; ANALYZE ndistinct; -SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct +SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct FROM pg_statistic_ext s, pg_statistic_ext_data d WHERE s.stxrelid = 'ndistinct'::regclass AND d.stxoid = s.oid; - stxkind | stxdndistinct ----------+-------------------------- - {d,f,m} | [ + - | { + - | "ndistinct": 11,+ - | "attributes": [ + - | 3, + - | 4 + - | ] + - | }, + - | { + - | "ndistinct": 11,+ - | "attributes": [ + - | 3, + - | 6 + - | ] + - | }, + - | { + - | "ndistinct": 11,+ - | "attributes": [ + - | 4, + - | 6 + - | ] + - | }, + - | { + - | "ndistinct": 11,+ - | "attributes": [ + - | 3, + - | 4, + - | 6 + - | ] + - | } + - | ] + stxkind | stxdndistinct +---------+--------------------------------------------- + {d,f,m} | [{"attributes": [3, 4], "ndistinct": 11}, + + | {"attributes": [3, 6], "ndistinct": 11}, + + | {"attributes": [4, 6], "ndistinct": 11}, + + | {"attributes": [3, 4, 6], "ndistinct": 11}] (1 row) -- minor improvement, make sure the ctid does not break the matching @@ -589,43 +564,16 @@ INSERT INTO ndistinct (a, b, c, filler1) mod(i,23) || ' dollars and zero cents' FROM generate_series(1,1000) s(i); ANALYZE ndistinct; -SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct +SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct FROM pg_statistic_ext s, pg_statistic_ext_data d WHERE s.stxrelid = 'ndistinct'::regclass AND d.stxoid = s.oid; - stxkind | stxdndistinct ----------+---------------------------- - {d,f,m} | [ + - | { + - | "ndistinct": 221, + - | "attributes": [ + - | 3, + - | 4 + - | ] + - | }, + - | { + - | "ndistinct": 247, + - | "attributes": [ + - | 3, + - | 6 + - | ] + - | }, + - | { + - | "ndistinct": 323, + - | "attributes": [ + - | 4, + - | 6 + - | ] + - | }, + - | { + - | "ndistinct": 1000,+ - | "attributes": [ + - | 3, + - | 4, + - | 6 + - | ] + - | } + - | ] + stxkind | stxdndistinct +---------+----------------------------------------------- + {d,f,m} | [{"attributes": [3, 4], "ndistinct": 221}, + + | {"attributes": [3, 6], "ndistinct": 247}, + + | {"attributes": [4, 6], "ndistinct": 323}, + + | {"attributes": [3, 4, 6], "ndistinct": 1000}] (1 row) -- correct estimates @@ -684,7 +632,7 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ( (1 row) DROP STATISTICS s10; -SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct +SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct FROM pg_statistic_ext s, pg_statistic_ext_data d WHERE s.stxrelid = 'ndistinct'::regclass AND d.stxoid = s.oid; @@ -768,43 +716,16 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ( CREATE STATISTICS s10 (ndistinct) ON (a+1), (b+100), (2*c) FROM ndistinct; ANALYZE ndistinct; -SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct +SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct FROM pg_statistic_ext s, pg_statistic_ext_data d WHERE s.stxrelid = 'ndistinct'::regclass AND d.stxoid = s.oid; - stxkind | stxdndistinct ----------+---------------------------- - {d,e} | [ + - | { + - | "ndistinct": 221, + - | "attributes": [ + - | -1, + - | -2 + - | ] + - | }, + - | { + - | "ndistinct": 247, + - | "attributes": [ + - | -1, + - | -3 + - | ] + - | }, + - | { + - | "ndistinct": 323, + - | "attributes": [ + - | -2, + - | -3 + - | ] + - | }, + - | { + - | "ndistinct": 1000,+ - | "attributes": [ + - | -1, + - | -2, + - | -3 + - | ] + - | } + - | ] + stxkind | stxdndistinct +---------+-------------------------------------------------- + {d,e} | [{"attributes": [-1, -2], "ndistinct": 221}, + + | {"attributes": [-1, -3], "ndistinct": 247}, + + | {"attributes": [-2, -3], "ndistinct": 323}, + + | {"attributes": [-1, -2, -3], "ndistinct": 1000}] (1 row) SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100)'); @@ -847,43 +768,16 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b CREATE STATISTICS s10 (ndistinct) ON a, b, (2*c) FROM ndistinct; ANALYZE ndistinct; -SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct +SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct FROM pg_statistic_ext s, pg_statistic_ext_data d WHERE s.stxrelid = 'ndistinct'::regclass AND d.stxoid = s.oid; - stxkind | stxdndistinct ----------+---------------------------- - {d,e} | [ + - | { + - | "ndistinct": 221, + - | "attributes": [ + - | 3, + - | 4 + - | ] + - | }, + - | { + - | "ndistinct": 247, + - | "attributes": [ + - | 3, + - | -1 + - | ] + - | }, + - | { + - | "ndistinct": 323, + - | "attributes": [ + - | 4, + - | -1 + - | ] + - | }, + - | { + - | "ndistinct": 1000,+ - | "attributes": [ + - | 3, + - | 4, + - | -1 + - | ] + - | } + - | ] + stxkind | stxdndistinct +---------+------------------------------------------------ + {d,e} | [{"attributes": [3, 4], "ndistinct": 221}, + + | {"attributes": [3, -1], "ndistinct": 247}, + + | {"attributes": [4, -1], "ndistinct": 323}, + + | {"attributes": [3, 4, -1], "ndistinct": 1000}] (1 row) SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b'); @@ -1434,48 +1328,14 @@ 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 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 + - } + - ] +SELECT replace(dependencies, '}, ', E'},\n') AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; + dependencies +-------------------------------------------------------------- + [{"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, "degree": 1.000000}] (1 row) SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1'''); @@ -1814,48 +1674,14 @@ 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 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 + - } + - ] +SELECT replace(dependencies, '}, ', E'},\n') AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; + dependencies +----------------------------------------------------------------- + [{"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, "degree": 1.000000}] (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 3f8e03f28a0..76ee9d29c08 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -125,8 +125,10 @@ 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, - jsonb_pretty(d.stxddependencies::text::jsonb) AS stxddependencies, stxdmcv, stxdinherit +SELECT stxname, + replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct, + replace(d.stxddependencies, '}, ', E'},\n') 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; @@ -298,7 +300,7 @@ CREATE STATISTICS s10 ON a, b, c FROM ndistinct; ANALYZE ndistinct; -SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct +SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct FROM pg_statistic_ext s, pg_statistic_ext_data d WHERE s.stxrelid = 'ndistinct'::regclass AND d.stxoid = s.oid; @@ -339,7 +341,7 @@ INSERT INTO ndistinct (a, b, c, filler1) ANALYZE ndistinct; -SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct +SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct FROM pg_statistic_ext s, pg_statistic_ext_data d WHERE s.stxrelid = 'ndistinct'::regclass AND d.stxoid = s.oid; @@ -365,7 +367,7 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ( DROP STATISTICS s10; -SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct +SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct FROM pg_statistic_ext s, pg_statistic_ext_data d WHERE s.stxrelid = 'ndistinct'::regclass AND d.stxoid = s.oid; @@ -400,7 +402,7 @@ CREATE STATISTICS s10 (ndistinct) ON (a+1), (b+100), (2*c) FROM ndistinct; ANALYZE ndistinct; -SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct +SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct FROM pg_statistic_ext s, pg_statistic_ext_data d WHERE s.stxrelid = 'ndistinct'::regclass AND d.stxoid = s.oid; @@ -424,7 +426,7 @@ CREATE STATISTICS s10 (ndistinct) ON a, b, (2*c) FROM ndistinct; ANALYZE ndistinct; -SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct +SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct FROM pg_statistic_ext s, pg_statistic_ext_data d WHERE s.stxrelid = 'ndistinct'::regclass AND d.stxoid = s.oid; @@ -709,7 +711,7 @@ CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_depen ANALYZE functional_dependencies; -- print the detected dependencies -SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; +SELECT replace(dependencies, '}, ', E'},\n') 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'''); @@ -845,7 +847,7 @@ CREATE STATISTICS func_deps_stat (dependencies) ON (a * 2), upper(b), (c + 1) FR ANALYZE functional_dependencies; -- print the detected dependencies -SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat'; +SELECT replace(dependencies, '}, ', E'},\n') 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'''); -- cgit v1.2.3