From 4ae4ac484bcaf4b74fef1284d63e6e6d4d6a236f Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Tue, 12 Dec 2023 20:48:42 -0500 Subject: [PATCH v3 2/9] Add system view pg_statistic_export. This view is designed to aid in the export (and re-import) of table statistics, mostly for upgrade/restore situations. --- src/backend/catalog/system_views.sql | 84 ++++++++++++++++++++++++++++ src/test/regress/expected/rules.out | 31 ++++++++++ doc/src/sgml/system-views.sgml | 5 ++ 3 files changed, 120 insertions(+) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 11d18ed9dd..7655bf7458 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -274,6 +274,90 @@ 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, + ( + SELECT + jsonb_object_agg( + CASE + WHEN a.stainherit THEN 'inherited' + ELSE 'regular' + END, + a.stats + ) + FROM + ( + SELECT + s.stainherit, + jsonb_object_agg( + 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, + s.stanumbers2::text, + s.stanumbers3::text, + s.stanumbers4::text, + s.stanumbers5::text), + 'stavalues', + jsonb_build_array( + -- casting to text makes it easier to import using array_in() + s.stavalues1::text, + s.stavalues2::text, + s.stavalues3::text, + s.stavalues4::text, + s.stavalues5::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') + GROUP BY s.stainherit + ) AS a + ) AS stats + FROM pg_class AS r + JOIN pg_namespace AS n + ON n.oid = r.relnamespace + WHERE relkind IN ('r', 'm', 'f', 'p', 'i') + AND n.nspname NOT IN ('pg_catalog', 'pg_toast', '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 05070393b9..f2b059af5e 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2404,6 +2404,37 @@ 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, + ( SELECT jsonb_object_agg( + CASE + WHEN a.stainherit THEN 'inherited'::text + ELSE 'regular'::text + END, a.stats) AS jsonb_object_agg + FROM ( SELECT s.stainherit, + jsonb_object_agg(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, (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))) 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)) + GROUP BY s.stainherit) a) 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", 'i'::"char"])) AND (n.nspname <> ALL (ARRAY['pg_catalog'::name, 'pg_toast'::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 0ef1745631..91b3ab22fb 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.43.0