From 059494d82745258400a145b6aa71ce958f23347a Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Sat, 9 Dec 2023 04:59:23 -0500 Subject: [PATCH v3 5/9] Add system view pg_statistic_ext_export. This view is designed to aid in the export (and re-import) of extended statistics, mostly for upgrade/restore situations. --- src/backend/catalog/system_views.sql | 131 +++++++++++++++++++++++++++ src/test/regress/expected/rules.out | 34 +++++++ doc/src/sgml/system-views.sgml | 5 + 3 files changed, 170 insertions(+) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 7655bf7458..8dca87c061 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -356,6 +356,137 @@ CREATE VIEW pg_statistic_export WITH (security_barrier) AS WHERE relkind IN ('r', 'm', 'f', 'p', 'i') AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema'); +CREATE VIEW pg_statistic_ext_export WITH (security_barrier) AS + SELECT + n.nspname AS schemaname, + r.relname AS tablename, + e.stxname AS ext_stats_name, + (current_setting('server_version_num'::text))::integer AS server_version_num, + jsonb_object_agg( + CASE sd.stxdinherit + WHEN true THEN 'inherited' + ELSE 'regular' + END, + jsonb_build_object( + 'stxkinds', + to_jsonb(e.stxkind), + 'stxdndistinct', + ( + SELECT + jsonb_agg( + -- att1, [, att2 ...] => attN: degree + jsonb_build_object( + 'attnums', + string_to_array(nd.attnums, ', '::text), + 'ndistinct', + nd.ndistinct + ) + ORDER BY nd.ord + ) + -- jsonb does not preserve parsed order so use json + FROM json_each_text(sd.stxdndistinct::text::json) + WITH ORDINALITY AS nd(attnums, ndistinct, ord) + WHERE sd.stxdndistinct IS NOT NULL + ), + 'stxdndependencies', + ( + SELECT + jsonb_agg( + jsonb_build_object( + 'attnums', + string_to_array( + replace(dep.attrs, ' => ', ', '), ', ' + ), + 'degree', + dep.degree + ) + ORDER BY dep.ord + ) + FROM json_each_text(sd.stxddependencies::text::json) + WITH ORDINALITY AS dep(attrs, degree, ord) + WHERE sd.stxddependencies IS NOT NULL + ), + 'stxdmcv', + ( + SELECT + jsonb_agg( + jsonb_build_object( + 'index', + mcvl.index::text, + 'frequency', + mcvl.frequency::text, + 'base_frequency', + mcvl.base_frequency::text, + 'values', + mcvl.values, + 'nulls', + mcvl.nulls + ) + ) + FROM pg_mcv_list_items(sd.stxdmcv) AS mcvl + WHERE sd.stxdmcv IS NOT NULL + ), + 'stxdexprs', + ( + SELECT + jsonb_agg( + jsonb_build_object( + 'stanullfrac', + s.stanullfrac::text, + 'stawidth', + s.stawidth::text, + 'stadistinct', + s.stadistinct::text, + 'stakinds', + ( + SELECT + jsonb_agg( + CASE kind.kind + WHEN 0 THEN 'TRIVIAL' + WHEN 1 THEN 'MCV' + WHEN 2 THEN 'HISTOGRAM' + WHEN 3 THEN 'CORRELATION' + WHEN 4 THEN 'MCELEM' + WHEN 5 THEN 'DECHIST' + WHEN 6 THEN 'RANGE_LENGTH_HISTOGRAM' + WHEN 7 THEN 'BOUNDS_HISTOGRAM' + ELSE NULL + END + ORDER BY kind.ord + ) + FROM unnest(ARRAY[s.stakind1, s.stakind2, + s.stakind3, s.stakind4, + s.stakind5]) + WITH ORDINALITY kind(kind, ord) + ), + 'stanumbers', + jsonb_build_array( + s.stanumbers1::text, + s.stanumbers2::text, + s.stanumbers3::text, + s.stanumbers4::text, + s.stanumbers5::text + ), + 'stavalues', + jsonb_build_array( + s.stavalues1::text, + s.stavalues2::text, + s.stavalues3::text, + s.stavalues4::text, + s.stavalues5::text) + ) + ORDER BY s.ordinality + ) + FROM unnest(sd.stxdexpr) WITH ORDINALITY AS s + WHERE sd.stxdexpr IS NOT NULL + ) + ) + ) AS stats + FROM pg_class r + JOIN pg_namespace n ON n.oid = r.relnamespace + JOIN pg_statistic_ext e ON e.stxrelid = r.oid + JOIN pg_statistic_ext_data sd ON sd.stxoid = e.oid + GROUP BY schemaname, tablename, ext_stats_name, server_version_num; CREATE VIEW pg_stats_ext WITH (security_barrier) AS diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index f2b059af5e..66ebac6cfd 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2435,6 +2435,40 @@ pg_statistic_export| SELECT n.nspname AS schemaname, FROM (pg_class r JOIN pg_namespace n ON ((n.oid = r.relnamespace))) WHERE ((r.relkind = ANY (ARRAY['r'::"char", 'm'::"char", 'f'::"char", 'p'::"char", 'i'::"char"])) AND (n.nspname <> ALL (ARRAY['pg_catalog'::name, 'pg_toast'::name, 'information_schema'::name]))); +pg_statistic_ext_export| SELECT n.nspname AS schemaname, + r.relname AS tablename, + e.stxname AS ext_stats_name, + (current_setting('server_version_num'::text))::integer AS server_version_num, + jsonb_object_agg( + CASE sd.stxdinherit + WHEN true THEN 'inherited'::text + ELSE 'regular'::text + END, jsonb_build_object('stxkinds', to_jsonb(e.stxkind), 'stxdndistinct', ( SELECT jsonb_agg(jsonb_build_object('attnums', string_to_array(nd.attnums, ', '::text), 'ndistinct', nd.ndistinct) ORDER BY nd.ord) AS jsonb_agg + FROM json_each_text(((sd.stxdndistinct)::text)::json) WITH ORDINALITY nd(attnums, ndistinct, ord) + WHERE (sd.stxdndistinct IS NOT NULL)), 'stxdndependencies', ( SELECT jsonb_agg(jsonb_build_object('attnums', string_to_array(replace(dep.attrs, ' => '::text, ', '::text), ', '::text), 'degree', dep.degree) ORDER BY dep.ord) AS jsonb_agg + FROM json_each_text(((sd.stxddependencies)::text)::json) WITH ORDINALITY dep(attrs, degree, ord) + WHERE (sd.stxddependencies IS NOT NULL)), 'stxdmcv', ( SELECT jsonb_agg(jsonb_build_object('index', (mcvl.index)::text, 'frequency', (mcvl.frequency)::text, 'base_frequency', (mcvl.base_frequency)::text, 'values', mcvl."values", 'nulls', mcvl.nulls)) AS jsonb_agg + FROM pg_mcv_list_items(sd.stxdmcv) mcvl(index, "values", nulls, frequency, base_frequency) + WHERE (sd.stxdmcv IS NOT NULL)), 'stxdexprs', ( SELECT jsonb_agg(jsonb_build_object('stanullfrac', (s.stanullfrac)::text, 'stawidth', (s.stawidth)::text, 'stadistinct', (s.stadistinct)::text, 'stakinds', ( SELECT jsonb_agg( + CASE kind.kind + WHEN 0 THEN 'TRIVIAL'::text + WHEN 1 THEN 'MCV'::text + WHEN 2 THEN 'HISTOGRAM'::text + WHEN 3 THEN 'CORRELATION'::text + WHEN 4 THEN 'MCELEM'::text + WHEN 5 THEN 'DECHIST'::text + WHEN 6 THEN 'RANGE_LENGTH_HISTOGRAM'::text + WHEN 7 THEN 'BOUNDS_HISTOGRAM'::text + ELSE NULL::text + END ORDER BY kind.ord) AS jsonb_agg + FROM unnest(ARRAY[s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5]) WITH ORDINALITY kind(kind, ord)), 'stanumbers', jsonb_build_array((s.stanumbers1)::text, (s.stanumbers2)::text, (s.stanumbers3)::text, (s.stanumbers4)::text, (s.stanumbers5)::text), 'stavalues', jsonb_build_array((s.stavalues1)::text, (s.stavalues2)::text, (s.stavalues3)::text, (s.stavalues4)::text, (s.stavalues5)::text)) ORDER BY s.ordinality) AS jsonb_agg + FROM unnest(sd.stxdexpr) WITH ORDINALITY s(starelid, staattnum, stainherit, stanullfrac, stawidth, stadistinct, stakind1, stakind2, stakind3, stakind4, stakind5, staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, stavalues1, stavalues2, stavalues3, stavalues4, stavalues5, ordinality) + WHERE (sd.stxdexpr IS NOT NULL)))) AS stats + FROM (((pg_class r + JOIN pg_namespace n ON ((n.oid = r.relnamespace))) + JOIN pg_statistic_ext e ON ((e.stxrelid = r.oid))) + JOIN pg_statistic_ext_data sd ON ((sd.stxoid = e.oid))) + GROUP BY n.nspname, r.relname, e.stxname, (current_setting('server_version_num'::text))::integer; pg_stats| SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index 91b3ab22fb..af5dff6a74 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -196,6 +196,11 @@ planner statistics for export/upgrade purposes + + pg_stats_ext_export + extended planner statistics for export/upgrade purposes + + pg_tables tables -- 2.43.0