From 920d546032a330fe1ec4c8f6a35c48a47ccd08b2 Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Tue, 31 Oct 2023 02:27:17 -0400 Subject: [PATCH v2 2/4] Add system view pg_statistic_export. This view is designed to aid in the export (and re-import) of table statistics and extended statistics, mostly for upgrade/restore situations. --- src/backend/catalog/system_views.sql | 215 +++++++++++++++++++++++++++ src/test/regress/expected/rules.out | 71 +++++++++ doc/src/sgml/system-views.sgml | 5 + 3 files changed, 291 insertions(+) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index b65f6b5249..11a1037ceb 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -253,6 +253,221 @@ CREATE VIEW pg_stats WITH (security_barrier) AS REVOKE ALL ON pg_statistic FROM public; + + + +CREATE VIEW pg_statistic_export WITH (security_barrier) AS + SELECT + n.nspname AS schemaname, + r.relname AS relname, + current_setting('server_version_num')::integer AS server_version_num, + r.reltuples::float4 AS n_tuples, + r.relpages::integer AS n_pages, + ( + WITH per_column_stats AS + ( + SELECT + s.stainherit, + a.attname, + 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' + END::text + ORDER BY kind.ord) + FROM unnest(ARRAY[s.stakind1, s.stakind2, + s.stakind3, stakind4, + s.stakind5]) + WITH ORDINALITY AS kind(kind, ord) + ), + 'stanumbers', + jsonb_build_array( + s.stanumbers1::text::text[], + s.stanumbers2::text::text[], + s.stanumbers3::text::text[], + s.stanumbers4::text::text[], + s.stanumbers5::text::text[]), + 'stavalues', + jsonb_build_array( + s.stavalues1::text::text[], + s.stavalues2::text::text[], + s.stavalues3::text::text[], + s.stavalues4::text::text[], + s.stavalues5::text::text[]) + ) AS stats + FROM pg_attribute AS a + JOIN pg_statistic AS s + ON s.starelid = a.attrelid + AND s.staattnum = a.attnum + WHERE a.attrelid = r.oid + AND NOT a.attisdropped + AND a.attnum > 0 + AND has_column_privilege(a.attrelid, a.attnum, 'SELECT') + ), + attagg AS + ( + SELECT + pcs.stainherit, + jsonb_build_object( + 'columns', + jsonb_object_agg( + pcs.attname, + pcs.stats + ) + ) AS stats + FROM per_column_stats AS pcs + GROUP BY pcs.stainherit + ), + extended_object_stats AS + ( + SELECT + sd.stxdinherit, + e.stxname, + jsonb_build_object( + 'stxkinds', + to_jsonb(e.stxkind), + 'stxdndistinct', + ndist.stxdndistinct, + 'stxdndependencies', + ndep.stxdndependencies, + 'stxdmcv', + mcv.stxdmcv, + 'stxdexprs', + x.stdxdexprs + ) AS stats + FROM pg_statistic_ext AS e + JOIN pg_statistic_ext_data AS sd + ON sd.stxoid = e.oid + LEFT JOIN LATERAL + ( + -- att1 [, att2..]: ndistinct + SELECT + jsonb_agg( + jsonb_build_object( + 'attnums', string_to_array(nd.attnums, ', '), + '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) + ) AS ndist(stxdndistinct) ON sd.stxdndistinct IS NOT NULL + LEFT JOIN LATERAL + ( + -- att1, [, att2 ...] => attN: degree + SELECT + jsonb_agg( + jsonb_build_object( + 'attnums', + string_to_array( replace(dep.attrs, ' => ', ', '), ', '), + 'degree', + dep.degree + ) + ORDER BY dep.ord + ) + -- jsonb does not preserve parsed order so use json + FROM json_each_text(sd.stxddependencies::text::json) + WITH ORDINALITY AS dep(attrs, degree, ord) + ) AS ndep(stxdndependencies) ON sd.stxddependencies IS NOT NULL + LEFT JOIN LATERAL + ( + -- TODO SELECT sd.stxdmcv + SELECT NULL AS stxdmcv + ) AS mcv(stxdmcv) ON sd.stxdmcv IS NOT NULL + LEFT JOIN LATERAL + ( + 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' + END::text + ORDER BY kind.ord) + FROM unnest(ARRAY[s.stakind1, s.stakind2, + s.stakind3, stakind4, + s.stakind5]) WITH ORDINALITY AS kind(kind, ord) + ), + 'stanumbers', + jsonb_build_array( + s.stanumbers1::text::text[], + s.stanumbers2::text::text[], + s.stanumbers3::text::text[], + s.stanumbers4::text::text[], + s.stanumbers5::text::text[]), + 'stavalues', + jsonb_build_array( + s.stavalues1::text::text[], + s.stavalues2::text::text[], + s.stavalues3::text::text[], + s.stavalues4::text::text[], + s.stavalues5::text::text[]) + ) + ORDER BY s.ordinality + ) + FROM unnest(sd.stxdexpr) WITH ORDINALITY AS s + ) AS x(stdxdexprs) ON sd.stxdexpr IS NOT NULL + WHERE e.stxrelid = r.oid + ), + extagg AS + ( + SELECT + eos.stxdinherit, + jsonb_build_object( + 'extended', + jsonb_object_agg( + eos.stxname, + eos.stats + ) + ) AS stats + FROM extended_object_stats AS eos + GROUP BY eos.stxdinherit + ) + SELECT + jsonb_object_agg( + CASE coalesce(a.stainherit, e.stxdinherit) + WHEN TRUE THEN 'inherited' + ELSE 'regular' + END, + coalesce(a.stats, '{}'::jsonb) || coalesce(e.stats, '{}'::jsonb) + ) + FROM attagg AS a + FULL OUTER JOIN extagg e ON a.stainherit = e.stxdinherit + ) AS stats + FROM pg_class AS r + JOIN pg_namespace AS n + ON n.oid = r.relnamespace + WHERE relkind IN ('r', 'm', 'f', 'p') + AND n.nspname NOT IN ('pg_catalog', 'information_schema'); + + CREATE VIEW pg_stats_ext WITH (security_barrier) AS SELECT cn.nspname AS schemaname, c.relname AS tablename, diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 1442c43d9c..20ab4da385 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2404,6 +2404,77 @@ pg_statio_user_tables| SELECT relid, tidx_blks_hit FROM pg_statio_all_tables WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); +pg_statistic_export| SELECT n.nspname AS schemaname, + r.relname, + (current_setting('server_version_num'::text))::integer AS server_version_num, + r.reltuples AS n_tuples, + r.relpages AS n_pages, + ( WITH per_column_stats AS ( + SELECT s.stainherit, + a_1.attname, + 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)::text[], ((s.stanumbers2)::text)::text[], ((s.stanumbers3)::text)::text[], ((s.stanumbers4)::text)::text[], ((s.stanumbers5)::text)::text[]), 'stavalues', jsonb_build_array(((s.stavalues1)::text)::text[], ((s.stavalues2)::text)::text[], ((s.stavalues3)::text)::text[], ((s.stavalues4)::text)::text[], ((s.stavalues5)::text)::text[])) AS stats + FROM (pg_attribute a_1 + JOIN pg_statistic s ON (((s.starelid = a_1.attrelid) AND (s.staattnum = a_1.attnum)))) + WHERE ((a_1.attrelid = r.oid) AND (NOT a_1.attisdropped) AND (a_1.attnum > 0) AND has_column_privilege(a_1.attrelid, a_1.attnum, 'SELECT'::text)) + ), attagg AS ( + SELECT pcs.stainherit, + jsonb_build_object('columns', jsonb_object_agg(pcs.attname, pcs.stats)) AS stats + FROM per_column_stats pcs + GROUP BY pcs.stainherit + ), extended_object_stats AS ( + SELECT sd.stxdinherit, + e_1.stxname, + jsonb_build_object('stxkinds', to_jsonb(e_1.stxkind), 'stxdndistinct', ndist.stxdndistinct, 'stxdndependencies', ndep.stxdndependencies, 'stxdmcv', mcv.stxdmcv, 'stxdexprs', x.stdxdexprs) AS stats + FROM (((((pg_statistic_ext e_1 + JOIN pg_statistic_ext_data sd ON ((sd.stxoid = e_1.oid))) + LEFT JOIN LATERAL ( 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)) ndist(stxdndistinct) ON ((sd.stxdndistinct IS NOT NULL))) + LEFT JOIN LATERAL ( 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)) ndep(stxdndependencies) ON ((sd.stxddependencies IS NOT NULL))) + LEFT JOIN LATERAL ( SELECT NULL::text AS stxdmcv) mcv(stxdmcv) ON ((sd.stxdmcv IS NOT NULL))) + LEFT JOIN LATERAL ( 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)::text[], ((s.stanumbers2)::text)::text[], ((s.stanumbers3)::text)::text[], ((s.stanumbers4)::text)::text[], ((s.stanumbers5)::text)::text[]), 'stavalues', jsonb_build_array(((s.stavalues1)::text)::text[], ((s.stavalues2)::text)::text[], ((s.stavalues3)::text)::text[], ((s.stavalues4)::text)::text[], ((s.stavalues5)::text)::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)) x(stdxdexprs) ON ((sd.stxdexpr IS NOT NULL))) + WHERE (e_1.stxrelid = r.oid) + ), extagg AS ( + SELECT eos.stxdinherit, + jsonb_build_object('extended', jsonb_object_agg(eos.stxname, eos.stats)) AS stats + FROM extended_object_stats eos + GROUP BY eos.stxdinherit + ) + SELECT jsonb_object_agg( + CASE COALESCE(a.stainherit, e.stxdinherit) + WHEN true THEN 'inherited'::text + ELSE 'regular'::text + END, (COALESCE(a.stats, '{}'::jsonb) || COALESCE(e.stats, '{}'::jsonb))) AS jsonb_object_agg + FROM (attagg a + FULL JOIN extagg e ON ((a.stainherit = e.stxdinherit)))) AS stats + 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"])) AND (n.nspname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name]))); 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 7078491c4c..24b44ab388 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -191,6 +191,11 @@ extended planner statistics for expressions + + pg_stats_export + planner statistics for export/upgrade purposes + + pg_tables tables -- 2.41.0