From a72f8de58bbead18f39741dbdf397a7dc7c64adb Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sun, 22 Nov 2020 18:44:42 -0600 Subject: [PATCH 7/7] WIP: Update pg_stats_ext for expressional stats (incomplete) --- src/backend/catalog/system_views.sql | 41 +++++++ src/test/regress/expected/rules.out | 170 +++++++++++++++++++++++++++ 2 files changed, 211 insertions(+) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 2e4aa1c4b6..2d517b5878 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -264,6 +264,47 @@ CREATE VIEW pg_stats_ext WITH (security_barrier) AS JOIN pg_attribute a ON (a.attrelid = s.stxrelid AND a.attnum = k) ) AS attnames, + + (SELECT array_agg((a::pg_statistic).stanullfrac) FROM unnest(stxdexpr)a) AS expr_null_frac, + (SELECT array_agg((a::pg_statistic).stawidth) FROM unnest(stxdexpr)a) AS expr_avg_width, + (SELECT array_agg((a::pg_statistic).stadistinct) FROM unnest(stxdexpr)a) AS expr_n_distinct, + + (SELECT array_agg(CASE + WHEN stakind1 = 1 THEN stavalues1 + WHEN stakind2 = 1 THEN stavalues2 + WHEN stakind3 = 1 THEN stavalues3 + WHEN stakind4 = 1 THEN stavalues4 + WHEN stakind5 = 1 THEN stavalues5 + END::text) FROM (SELECT (a::pg_statistic).* AS a FROM unnest(stxdexpr)a)a + ) AS expr_most_common_vals, + + (SELECT array_agg(CASE + WHEN stakind1 = 1 THEN stanumbers1 + WHEN stakind2 = 1 THEN stanumbers2 + WHEN stakind3 = 1 THEN stanumbers3 + WHEN stakind4 = 1 THEN stanumbers4 + WHEN stakind5 = 1 THEN stanumbers5 + END::text) FROM (SELECT (a::pg_statistic).* AS a FROM unnest(stxdexpr)a)a + ) AS expr_most_common_freqs, + + (SELECT array_agg(CASE + WHEN stakind1 = 2 THEN stavalues1 + WHEN stakind2 = 2 THEN stavalues2 + WHEN stakind3 = 2 THEN stavalues3 + WHEN stakind4 = 2 THEN stavalues4 + WHEN stakind5 = 2 THEN stavalues5 + END::text) FROM (SELECT (a::pg_statistic).* AS a FROM unnest(stxdexpr)a)a + ) AS expr_histogram_bounds, + + (SELECT array_agg(CASE + WHEN stakind1 = 3 THEN stanumbers1[1] + WHEN stakind2 = 3 THEN stanumbers2[1] + WHEN stakind3 = 3 THEN stanumbers3[1] + WHEN stakind4 = 3 THEN stanumbers4[1] + WHEN stakind5 = 3 THEN stanumbers5[1] + END) FROM (SELECT (a::pg_statistic).* AS a FROM unnest(stxdexpr)a)a + ) AS expr_correlation, + s.stxkind AS kinds, sd.stxdndistinct AS n_distinct, sd.stxddependencies AS dependencies, diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 097ff5d111..e6e5bfef11 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2381,6 +2381,176 @@ pg_stats_ext| SELECT cn.nspname AS schemaname, ( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg FROM (unnest(s.stxkeys) k(k) JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k))))) AS attnames, + ( SELECT array_agg(a.stanullfrac) AS array_agg + FROM unnest(sd.stxdexpr) a(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)) AS expr_null_frac, + ( SELECT array_agg(a.stawidth) AS array_agg + FROM unnest(sd.stxdexpr) a(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)) AS expr_avg_width, + ( SELECT array_agg(a.stadistinct) AS array_agg + FROM unnest(sd.stxdexpr) a(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)) AS expr_n_distinct, + ( SELECT array_agg(( + CASE + WHEN (a.stakind1 = 1) THEN a.stavalues1 + WHEN (a.stakind2 = 1) THEN a.stavalues2 + WHEN (a.stakind3 = 1) THEN a.stavalues3 + WHEN (a.stakind4 = 1) THEN a.stavalues4 + WHEN (a.stakind5 = 1) THEN a.stavalues5 + ELSE NULL::anyarray + END)::text) AS array_agg + FROM ( SELECT a_1.starelid, + a_1.staattnum, + a_1.stainherit, + a_1.stanullfrac, + a_1.stawidth, + a_1.stadistinct, + a_1.stakind1, + a_1.stakind2, + a_1.stakind3, + a_1.stakind4, + a_1.stakind5, + a_1.staop1, + a_1.staop2, + a_1.staop3, + a_1.staop4, + a_1.staop5, + a_1.stacoll1, + a_1.stacoll2, + a_1.stacoll3, + a_1.stacoll4, + a_1.stacoll5, + a_1.stanumbers1, + a_1.stanumbers2, + a_1.stanumbers3, + a_1.stanumbers4, + a_1.stanumbers5, + a_1.stavalues1, + a_1.stavalues2, + a_1.stavalues3, + a_1.stavalues4, + a_1.stavalues5 + FROM unnest(sd.stxdexpr) a_1(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)) a) AS expr_most_common_vals, + ( SELECT array_agg(( + CASE + WHEN (a.stakind1 = 1) THEN a.stanumbers1 + WHEN (a.stakind2 = 1) THEN a.stanumbers2 + WHEN (a.stakind3 = 1) THEN a.stanumbers3 + WHEN (a.stakind4 = 1) THEN a.stanumbers4 + WHEN (a.stakind5 = 1) THEN a.stanumbers5 + ELSE NULL::real[] + END)::text) AS array_agg + FROM ( SELECT a_1.starelid, + a_1.staattnum, + a_1.stainherit, + a_1.stanullfrac, + a_1.stawidth, + a_1.stadistinct, + a_1.stakind1, + a_1.stakind2, + a_1.stakind3, + a_1.stakind4, + a_1.stakind5, + a_1.staop1, + a_1.staop2, + a_1.staop3, + a_1.staop4, + a_1.staop5, + a_1.stacoll1, + a_1.stacoll2, + a_1.stacoll3, + a_1.stacoll4, + a_1.stacoll5, + a_1.stanumbers1, + a_1.stanumbers2, + a_1.stanumbers3, + a_1.stanumbers4, + a_1.stanumbers5, + a_1.stavalues1, + a_1.stavalues2, + a_1.stavalues3, + a_1.stavalues4, + a_1.stavalues5 + FROM unnest(sd.stxdexpr) a_1(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)) a) AS expr_most_common_freqs, + ( SELECT array_agg(( + CASE + WHEN (a.stakind1 = 2) THEN a.stavalues1 + WHEN (a.stakind2 = 2) THEN a.stavalues2 + WHEN (a.stakind3 = 2) THEN a.stavalues3 + WHEN (a.stakind4 = 2) THEN a.stavalues4 + WHEN (a.stakind5 = 2) THEN a.stavalues5 + ELSE NULL::anyarray + END)::text) AS array_agg + FROM ( SELECT a_1.starelid, + a_1.staattnum, + a_1.stainherit, + a_1.stanullfrac, + a_1.stawidth, + a_1.stadistinct, + a_1.stakind1, + a_1.stakind2, + a_1.stakind3, + a_1.stakind4, + a_1.stakind5, + a_1.staop1, + a_1.staop2, + a_1.staop3, + a_1.staop4, + a_1.staop5, + a_1.stacoll1, + a_1.stacoll2, + a_1.stacoll3, + a_1.stacoll4, + a_1.stacoll5, + a_1.stanumbers1, + a_1.stanumbers2, + a_1.stanumbers3, + a_1.stanumbers4, + a_1.stanumbers5, + a_1.stavalues1, + a_1.stavalues2, + a_1.stavalues3, + a_1.stavalues4, + a_1.stavalues5 + FROM unnest(sd.stxdexpr) a_1(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)) a) AS expr_histogram_bounds, + ( SELECT array_agg( + CASE + WHEN (a.stakind1 = 3) THEN a.stanumbers1[1] + WHEN (a.stakind2 = 3) THEN a.stanumbers2[1] + WHEN (a.stakind3 = 3) THEN a.stanumbers3[1] + WHEN (a.stakind4 = 3) THEN a.stanumbers4[1] + WHEN (a.stakind5 = 3) THEN a.stanumbers5[1] + ELSE NULL::real + END) AS array_agg + FROM ( SELECT a_1.starelid, + a_1.staattnum, + a_1.stainherit, + a_1.stanullfrac, + a_1.stawidth, + a_1.stadistinct, + a_1.stakind1, + a_1.stakind2, + a_1.stakind3, + a_1.stakind4, + a_1.stakind5, + a_1.staop1, + a_1.staop2, + a_1.staop3, + a_1.staop4, + a_1.staop5, + a_1.stacoll1, + a_1.stacoll2, + a_1.stacoll3, + a_1.stacoll4, + a_1.stacoll5, + a_1.stanumbers1, + a_1.stanumbers2, + a_1.stanumbers3, + a_1.stanumbers4, + a_1.stanumbers5, + a_1.stavalues1, + a_1.stavalues2, + a_1.stavalues3, + a_1.stavalues4, + a_1.stavalues5 + FROM unnest(sd.stxdexpr) a_1(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)) a) AS expr_correlation, s.stxkind AS kinds, sd.stxdndistinct AS n_distinct, sd.stxddependencies AS dependencies, -- 2.17.0