You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2025/09/08 07:20:41 UTC
(cloudberry) 03/04: Add gp summary system views
This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 2b1c9c306eab532635fb598a3de8d330040a6fea
Author: Andrew Repp <re...@vmware.com>
AuthorDate: Fri Apr 7 16:34:44 2023 -0500
Add gp summary system views
These summary views offer basic aggregation of the gp_stat_* views across Greenplum coordinator and
segments.
Aggregation logic applied as follows:
* Time related (last_%): use max()
* Transaction related, not innately summable (number of commits/rollbacks) : use max()
* Table specific: sum()/numsegments for replicated tables, sum() for
distributed tables
* Innately summable stats, if no particular table is involved: use sum()
* pid: use coordinator's pid (not used here, but this is the convention in other gp_%_summary views)
---
src/backend/catalog/system_views.sql | 122 --------
src/backend/catalog/system_views_gp_summary.sql | 381 ++++++++++++++++++++++++
2 files changed, 381 insertions(+), 122 deletions(-)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 4b8c9c0df43..d4d00b67771 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -692,81 +692,6 @@ CREATE VIEW pg_stat_all_tables AS
WHERE C.relkind IN ('r', 't', 'm', 'o', 'b', 'M', 'p')
GROUP BY C.oid, N.nspname, C.relname;
--- Gather data from segments on user tables, and use data on coordinator on system tables.
-
-CREATE VIEW gp_stat_all_tables_summary AS
-SELECT
- s.relid,
- s.schemaname,
- s.relname,
- m.seq_scan,
- m.seq_tup_read,
- m.idx_scan,
- m.idx_tup_fetch,
- m.n_tup_ins,
- m.n_tup_upd,
- m.n_tup_del,
- m.n_tup_hot_upd,
- m.n_live_tup,
- m.n_dead_tup,
- m.n_mod_since_analyze,
- m.n_ins_since_vacuum,
- s.last_vacuum,
- s.last_autovacuum,
- s.last_analyze,
- s.last_autoanalyze,
- s.vacuum_count,
- s.autovacuum_count,
- s.analyze_count,
- s.autoanalyze_count
-FROM
- (SELECT
- allt.relid,
- allt.schemaname,
- allt.relname,
- case when d.policytype = 'r' then (sum(seq_scan)/d.numsegments)::bigint else sum(seq_scan) end seq_scan,
- case when d.policytype = 'r' then (sum(seq_tup_read)/d.numsegments)::bigint else sum(seq_tup_read) end seq_tup_read,
- case when d.policytype = 'r' then (sum(idx_scan)/d.numsegments)::bigint else sum(idx_scan) end idx_scan,
- case when d.policytype = 'r' then (sum(idx_tup_fetch)/d.numsegments)::bigint else sum(idx_tup_fetch) end idx_tup_fetch,
- case when d.policytype = 'r' then (sum(n_tup_ins)/d.numsegments)::bigint else sum(n_tup_ins) end n_tup_ins,
- case when d.policytype = 'r' then (sum(n_tup_upd)/d.numsegments)::bigint else sum(n_tup_upd) end n_tup_upd,
- case when d.policytype = 'r' then (sum(n_tup_del)/d.numsegments)::bigint else sum(n_tup_del) end n_tup_del,
- case when d.policytype = 'r' then (sum(n_tup_hot_upd)/d.numsegments)::bigint else sum(n_tup_hot_upd) end n_tup_hot_upd,
- case when d.policytype = 'r' then (sum(n_live_tup)/d.numsegments)::bigint else sum(n_live_tup) end n_live_tup,
- case when d.policytype = 'r' then (sum(n_dead_tup)/d.numsegments)::bigint else sum(n_dead_tup) end n_dead_tup,
- case when d.policytype = 'r' then (sum(n_mod_since_analyze)/d.numsegments)::bigint else sum(n_mod_since_analyze) end n_mod_since_analyze,
- case when d.policytype = 'r' then (sum(n_ins_since_vacuum)/d.numsegments)::bigint else sum(n_ins_since_vacuum) end n_ins_since_vacuum,
- max(last_vacuum) as last_vacuum,
- max(last_autovacuum) as last_autovacuum,
- max(last_analyze) as last_analyze,
- max(last_autoanalyze) as last_autoanalyze,
- max(vacuum_count) as vacuum_count,
- max(autovacuum_count) as autovacuum_count,
- max(analyze_count) as analyze_count,
- max(autoanalyze_count) as autoanalyze_count
- FROM
- gp_dist_random('pg_stat_all_tables') allt
- inner join pg_class c
- on allt.relid = c.oid
- left outer join gp_distribution_policy d
- on allt.relid = d.localoid
- WHERE
- relid >= 16384
- and (
- d.localoid is not null
- or c.relkind in ('o', 'b', 'M')
- )
- GROUP BY allt.relid, allt.schemaname, allt.relname, d.policytype, d.numsegments
-
- UNION ALL
-
- SELECT
- *
- FROM
- pg_stat_all_tables
- WHERE
- relid < 16384) m, pg_stat_all_tables s
-WHERE m.relid = s.relid;
CREATE VIEW pg_stat_xact_all_tables AS
SELECT
@@ -819,10 +744,6 @@ CREATE VIEW pg_stat_user_tables_single_node AS
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
schemaname !~ '^pg_toast';
-CREATE VIEW gp_stat_user_tables_summary AS
- SELECT * FROM gp_stat_all_tables_summary
- WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
- schemaname !~ '^pg_toast';
CREATE VIEW pg_stat_xact_user_tables AS
SELECT * FROM pg_stat_xact_all_tables
@@ -880,44 +801,6 @@ CREATE VIEW pg_stat_all_indexes AS
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.relkind IN ('r', 't', 'm', 'o', 'b', 'M');
--- Gather data from segments on user tables, and use data on coordinator on system tables.
-
-CREATE VIEW gp_stat_all_indexes_summary AS
-SELECT
- s.relid,
- s.indexrelid,
- s.schemaname,
- s.relname,
- s.indexrelname,
- m.idx_scan,
- m.idx_tup_read,
- m.idx_tup_fetch
-FROM
- (SELECT
- relid,
- indexrelid,
- schemaname,
- relname,
- indexrelname,
- sum(idx_scan) as idx_scan,
- sum(idx_tup_read) as idx_tup_read,
- sum(idx_tup_fetch) as idx_tup_fetch
- FROM
- gp_dist_random('pg_stat_all_indexes')
- WHERE
- relid >= 16384
- GROUP BY relid, indexrelid, schemaname, relname, indexrelname
-
- UNION ALL
-
- SELECT
- *
- FROM
- pg_stat_all_indexes
- WHERE
- relid < 16384) m, pg_stat_all_indexes s
-WHERE m.relid = s.relid;
-
CREATE VIEW pg_stat_sys_indexes AS
SELECT * FROM pg_stat_all_indexes
WHERE schemaname IN ('pg_catalog', 'information_schema', 'pg_aoseg') OR
@@ -928,11 +811,6 @@ CREATE VIEW pg_stat_user_indexes AS
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
schemaname !~ '^pg_toast';
-CREATE VIEW gp_stat_user_indexes_summary AS
- SELECT * FROM gp_stat_all_indexes_summary
- WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
- schemaname !~ '^pg_toast';
-
CREATE VIEW pg_statio_all_indexes AS
SELECT
C.oid AS relid,
diff --git a/src/backend/catalog/system_views_gp_summary.sql b/src/backend/catalog/system_views_gp_summary.sql
index 58f4768c124..f83329203ab 100644
--- a/src/backend/catalog/system_views_gp_summary.sql
+++ b/src/backend/catalog/system_views_gp_summary.sql
@@ -22,6 +22,387 @@
* string literal (including a function body!) or a multiline comment.
*/
+CREATE VIEW gp_stat_archiver_summary AS
+SELECT
+ sum(gsa.archived_count) as archived_count,
+ max(gsa.last_archived_wal) as last_archived_wal,
+ max(gsa.last_archived_time) as last_archived_time,
+ sum(gsa.failed_count) as failed_count,
+ max(gsa.last_failed_wal) as last_failed_wal,
+ max(gsa.last_failed_time) as last_failed_time,
+ max(gsa.stats_reset) as stats_reset
+FROM
+ gp_stat_archiver gsa;
+
+CREATE VIEW gp_stat_bgwriter_summary AS
+SELECT
+ sum(gsb.checkpoints_timed) as checkpoints_timed,
+ sum(gsb.checkpoints_req) as checkpoints_req,
+ sum(gsb.checkpoint_write_time) as checkpoint_write_time,
+ sum(gsb.checkpoint_sync_time) as checkpoint_sync_time,
+ sum(gsb.buffers_checkpoint) as buffers_checkpoint,
+ sum(gsb.buffers_clean) as buffers_clean,
+ sum(gsb.maxwritten_clean) as maxwritten_clean,
+ sum(gsb.buffers_backend) as buffers_backend,
+ sum(gsb.buffers_backend_fsync) as buffers_backend_fsync,
+ sum(gsb.buffers_alloc) as buffers_alloc,
+ max(gsb.stats_reset) as stats_reset
+FROM
+ gp_stat_bgwriter gsb;
+
+CREATE VIEW gp_stat_wal_summary AS
+SELECT
+ sum(gsw.wal_records) as wal_records,
+ sum(gsw.wal_fpw) as wal_fpw,
+ sum(gsw.wal_bytes) as wal_bytes,
+ sum(gsw.wal_buffers_full) as wal_buffers_full,
+ sum(gsw.wal_write) as wal_write,
+ sum(gsw.wal_sync) as wal_sync,
+ sum(gsw.wal_write_time) as wal_write_time,
+ sum(gsw.wal_sync_time) as wal_sync_time,
+ max(gsw.stats_reset) as stats_reset
+from
+ gp_stat_wal gsw;
+
+CREATE VIEW gp_stat_database_summary AS
+SELECT
+ sdb.datid,
+ sdb.datname,
+ sum(sdb.numbackends) as numbackends,
+ max(sdb.xact_commit) as xact_commit,
+ max(sdb.xact_rollback) as xact_rollback,
+ sum(sdb.blks_read) as blks_read,
+ sum(sdb.blks_hit) as blks_hit,
+ sum(sdb.tup_returned) as tup_returned,
+ sum(sdb.tup_fetched) as tup_fetched,
+ sum(sdb.tup_inserted) as tup_inserted,
+ sum(sdb.tup_updated) as tup_updated,
+ sum(sdb.tup_deleted) as tup_deleted,
+ max(sdb.conflicts) as conflicts,
+ sum(sdb.temp_files) as temp_files,
+ sum(sdb.temp_bytes) as temp_bytes,
+ sum(sdb.deadlocks) as deadlocks,
+ sum(sdb.checksum_failures) as checksum_failures,
+ max(sdb.checksum_last_failure) as checksum_last_failure,
+ sum(sdb.blk_read_time) as blk_read_time,
+ sum(sdb.blk_write_time) as blk_write_time,
+ max(sdb.stats_reset) as stats_reset
+FROM
+ gp_stat_database sdb
+GROUP BY
+ sdb.datid,
+ sdb.datname;
+
+
+-- Gather data from segments on user tables, and use data on coordinator on system tables.
+CREATE VIEW gp_stat_all_tables_summary AS
+SELECT
+ s.relid,
+ s.schemaname,
+ s.relname,
+ m.seq_scan,
+ m.seq_tup_read,
+ m.idx_scan,
+ m.idx_tup_fetch,
+ m.n_tup_ins,
+ m.n_tup_upd,
+ m.n_tup_del,
+ m.n_tup_hot_upd,
+ m.n_live_tup,
+ m.n_dead_tup,
+ m.n_mod_since_analyze,
+ s.last_vacuum,
+ s.last_autovacuum,
+ s.last_analyze,
+ s.last_autoanalyze,
+ s.vacuum_count,
+ s.autovacuum_count,
+ s.analyze_count,
+ s.autoanalyze_count
+FROM
+ (SELECT
+ allt.relid,
+ allt.schemaname,
+ allt.relname,
+ case when d.policytype = 'r' then (sum(seq_scan)/d.numsegments)::bigint else sum(seq_scan) end seq_scan,
+ case when d.policytype = 'r' then (sum(seq_tup_read)/d.numsegments)::bigint else sum(seq_tup_read) end seq_tup_read,
+ case when d.policytype = 'r' then (sum(idx_scan)/d.numsegments)::bigint else sum(idx_scan) end idx_scan,
+ case when d.policytype = 'r' then (sum(idx_tup_fetch)/d.numsegments)::bigint else sum(idx_tup_fetch) end idx_tup_fetch,
+ case when d.policytype = 'r' then (sum(n_tup_ins)/d.numsegments)::bigint else sum(n_tup_ins) end n_tup_ins,
+ case when d.policytype = 'r' then (sum(n_tup_upd)/d.numsegments)::bigint else sum(n_tup_upd) end n_tup_upd,
+ case when d.policytype = 'r' then (sum(n_tup_del)/d.numsegments)::bigint else sum(n_tup_del) end n_tup_del,
+ case when d.policytype = 'r' then (sum(n_tup_hot_upd)/d.numsegments)::bigint else sum(n_tup_hot_upd) end n_tup_hot_upd,
+ case when d.policytype = 'r' then (sum(n_live_tup)/d.numsegments)::bigint else sum(n_live_tup) end n_live_tup,
+ case when d.policytype = 'r' then (sum(n_dead_tup)/d.numsegments)::bigint else sum(n_dead_tup) end n_dead_tup,
+ case when d.policytype = 'r' then (sum(n_mod_since_analyze)/d.numsegments)::bigint else sum(n_mod_since_analyze) end n_mod_since_analyze,
+ max(last_vacuum) as last_vacuum,
+ max(last_autovacuum) as last_autovacuum,
+ max(last_analyze) as last_analyze,
+ max(last_autoanalyze) as last_autoanalyze,
+ max(vacuum_count) as vacuum_count,
+ max(autovacuum_count) as autovacuum_count,
+ max(analyze_count) as analyze_count,
+ max(autoanalyze_count) as autoanalyze_count
+ FROM
+ gp_dist_random('pg_stat_all_tables') allt
+ inner join pg_class c
+ on allt.relid = c.oid
+ left outer join gp_distribution_policy d
+ on allt.relid = d.localoid
+ WHERE
+ relid >= 16384
+ and (
+ d.localoid is not null
+ or c.relkind in ('o', 'b', 'M')
+ )
+ GROUP BY allt.relid, allt.schemaname, allt.relname, d.policytype, d.numsegments
+
+ UNION ALL
+
+ SELECT
+ *
+ FROM
+ pg_stat_all_tables
+ WHERE
+ relid < 16384) m, pg_stat_all_tables s
+WHERE m.relid = s.relid;
+
+CREATE VIEW gp_stat_user_tables_summary AS
+ SELECT * FROM gp_stat_all_tables_summary
+ WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_aoseg') AND
+ schemaname !~ '^pg_toast';
+
+CREATE VIEW gp_stat_sys_tables_summary AS
+ SELECT * FROM gp_stat_all_tables_summary
+ WHERE schemaname IN ('pg_catalog', 'information_schema', 'pg_aoseg') OR
+ schemaname ~ '^pg_toast';
+
+CREATE VIEW gp_stat_xact_all_tables_summary AS
+SELECT
+ sxa.relid,
+ sxa.schemaname,
+ sxa.relname,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sxa.seq_scan)/dst.numsegments)::bigint ELSE sum(sxa.seq_scan) END AS seq_scan,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sxa.seq_tup_read)/dst.numsegments)::bigint ELSE sum(sxa.seq_tup_read) END AS seq_tup_read,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sxa.idx_scan)/dst.numsegments)::bigint ELSE sum(sxa.idx_scan) END AS idx_scan,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sxa.idx_tup_fetch)/dst.numsegments)::bigint ELSE sum(sxa.idx_tup_fetch) END AS idx_tup_fetch,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sxa.n_tup_ins)/dst.numsegments)::bigint ELSE sum(sxa.n_tup_ins) END AS n_tup_ins,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sxa.n_tup_upd)/dst.numsegments)::bigint ELSE sum(sxa.n_tup_upd) END AS n_tup_upd,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sxa.n_tup_del)/dst.numsegments)::bigint ELSE sum(sxa.n_tup_del) END AS n_tup_del,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sxa.n_tup_hot_upd)/dst.numsegments)::bigint ELSE sum(sxa.n_tup_hot_upd) END AS n_tup_hot_upd
+FROM
+ gp_stat_xact_all_tables sxa
+ LEFT OUTER JOIN gp_distribution_policy dst
+ ON sxa.relid = dst.localoid
+GROUP BY
+ sxa.relid,
+ sxa.schemaname,
+ sxa.relname,
+ dst.policytype,
+ dst.numsegments;
+
+CREATE VIEW gp_stat_xact_sys_tables_summary as
+ SELECT * FROM gp_stat_xact_all_tables_summary
+ WHERE schemaname IN ('pg_catalog', 'information_schema', 'pg_aoseg') OR
+ schemaname ~ '^pg_toast';
+
+CREATE VIEW gp_stat_xact_user_tables_summary AS
+ SELECT * FROM gp_stat_xact_all_tables_summary
+ WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_aoseg') AND
+ schemaname !~ '^pg_toast';
+
+-- Gather data from segments on user tables, and use data on coordinator on system tables.
+CREATE VIEW gp_stat_all_indexes_summary AS
+SELECT
+ s.relid,
+ s.indexrelid,
+ s.schemaname,
+ s.relname,
+ s.indexrelname,
+ m.idx_scan,
+ m.idx_tup_read,
+ m.idx_tup_fetch
+FROM
+ (SELECT
+ alli.relid,
+ alli.indexrelid,
+ alli.schemaname,
+ alli.relname,
+ alli.indexrelname,
+ case when d.policytype = 'r' then (sum(alli.idx_scan)/d.numsegments)::bigint else sum(alli.idx_scan) end idx_scan,
+ case when d.policytype = 'r' then (sum(alli.idx_tup_read)/d.numsegments)::bigint else sum(alli.idx_tup_read) end idx_tup_read,
+ case when d.policytype = 'r' then (sum(alli.idx_tup_fetch)/d.numsegments)::bigint else sum(alli.idx_tup_fetch) end idx_tup_fetch
+ FROM
+ gp_dist_random('pg_stat_all_indexes') alli
+ inner join pg_class c
+ on alli.relid = c.oid
+ left outer join gp_distribution_policy d
+ on alli.relid = d.localoid
+ WHERE
+ relid >= 16384
+ GROUP BY alli.relid, alli.indexrelid, alli.schemaname, alli.relname, alli.indexrelname, d.policytype, d.numsegments
+
+ UNION ALL
+
+ SELECT
+ *
+ FROM
+ pg_stat_all_indexes
+ WHERE
+ relid < 16384) m, pg_stat_all_indexes s
+WHERE m.relid = s.relid;
+
+CREATE VIEW gp_stat_sys_indexes_summary AS
+ SELECT * FROM gp_stat_all_indexes_summary
+ WHERE schemaname IN ('pg_catalog', 'information_schema', 'pg_aoseg') OR
+ schemaname ~ '^pg_toast';
+
+CREATE VIEW gp_stat_user_indexes_summary AS
+ SELECT * FROM gp_stat_all_indexes_summary
+ WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_aoseg') AND
+ schemaname !~ '^pg_toast';
+
+CREATE VIEW gp_statio_all_tables_summary as
+SELECT
+ sat.relid,
+ sat.schemaname,
+ sat.relname,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sat.heap_blks_read)/dst.numsegments)::bigint ELSE sum(sat.heap_blks_read) END AS heap_blks_read,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sat.heap_blks_hit)/dst.numsegments)::bigint ELSE sum(sat.heap_blks_hit) END AS heap_blks_hit,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sat.idx_blks_read)/dst.numsegments)::bigint ELSE sum(sat.idx_blks_read) END AS idx_blks_read,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sat.idx_blks_hit)/dst.numsegments)::bigint ELSE sum(sat.idx_blks_hit) END AS idx_blks_hit,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sat.toast_blks_read)/dst.numsegments)::bigint ELSE sum(sat.toast_blks_read) END AS toast_blks_read,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sat.toast_blks_hit)/dst.numsegments)::bigint ELSE sum(sat.toast_blks_hit) END AS toast_blks_hit,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sat.tidx_blks_read)/dst.numsegments)::bigint ELSE sum(sat.tidx_blks_read) END AS tidx_blks_read,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sat.tidx_blks_hit)/dst.numsegments)::bigint ELSE sum(sat.tidx_blks_hit) END AS tidx_blks_hit
+FROM
+ gp_statio_all_tables sat
+ LEFT OUTER JOIN gp_distribution_policy dst
+ ON sat.relid = dst.localoid
+GROUP BY
+ sat.relid,
+ sat.schemaname,
+ sat.relname,
+ dst.policytype,
+ dst.numsegments;
+
+CREATE VIEW gp_statio_sys_tables_summary AS
+ SELECT * FROM gp_statio_all_tables_summary
+ WHERE schemaname IN ('pg_catalog', 'information_schema', 'pg_aoseg') OR
+ schemaname ~ '^pg_toast';
+
+CREATE VIEW gp_statio_user_tables_summary AS
+ SELECT * FROM gp_stat_all_tables_summary
+ WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_aoseg') AND
+ schemaname !~ '^pg_toast';
+
+CREATE VIEW gp_statio_all_sequences_summary as
+SELECT
+ sas.relid,
+ sas.schemaname,
+ sas.relname,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sas.blks_read)/dst.numsegments)::bigint ELSE sum(sas.blks_read) END AS blks_read,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sas.blks_hit)/dst.numsegments)::bigint ELSE sum(sas.blks_hit) END AS blks_hit
+FROM
+ gp_statio_all_sequences sas
+ LEFT OUTER JOIN gp_distribution_policy dst
+ ON sas.relid = dst.localoid
+GROUP BY
+ sas.relid,
+ sas.schemaname,
+ sas.relname,
+ dst.policytype,
+ dst.numsegments;
+
+CREATE VIEW gp_statio_sys_sequences_summary AS
+ SELECT * FROM gp_statio_all_sequences_summary
+ WHERE schemaname IN ('pg_catalog', 'information_schema', 'pg_aoseg') OR
+ schemaname ~ '^pg_toast';
+
+CREATE VIEW gp_statio_user_sequences_summary AS
+ SELECT * FROM gp_statio_all_sequences_summary
+ WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_aoseg') AND
+ schemaname !~ '^pg_toast';
+
+CREATE VIEW gp_statio_all_indexes_summary AS
+SELECT
+ sai.relid,
+ sai.indexrelid,
+ sai.schemaname,
+ sai.relname,
+ sai.indexrelname,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sai.idx_blks_read)/dst.numsegments)::bigint ELSE sum(sai.idx_blks_read) END AS idx_blks_read,
+ CASE WHEN dst.policytype = 'r' THEN (sum(sai.idx_blks_hit)/dst.numsegments)::bigint ELSE sum(sai.idx_blks_hit) END AS idx_blks_hit
+FROM
+ gp_statio_all_indexes sai
+ LEFT OUTER JOIN gp_distribution_policy dst
+ ON sai.relid = dst.localoid
+GROUP BY
+ sai.relid,
+ sai.indexrelid,
+ sai.schemaname,
+ sai.relname,
+ sai.indexrelname,
+ dst.policytype,
+ dst.numsegments;
+
+CREATE VIEW gp_statio_sys_indexes_summary AS
+ SELECT * FROM gp_statio_all_indexes_summary
+ WHERE schemaname IN ('pg_catalog', 'information_schema', 'pg_aoseg') OR
+ schemaname ~ '^pg_toast';
+
+CREATE VIEW gp_statio_user_indexes_summary AS
+ SELECT * FROM gp_statio_all_indexes_summary
+ WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_aoseg') AND
+ schemaname !~ '^pg_toast';
+
+CREATE VIEW gp_stat_user_functions_summary AS
+SELECT
+ guf.funcid,
+ guf.schemaname,
+ guf.funcname,
+ sum(guf.calls) AS calls,
+ sum(guf.total_time) AS total_time,
+ sum(guf.self_time) AS self_time
+FROM
+ gp_stat_user_functions guf
+GROUP BY
+ guf.funcid,
+ guf.schemaname,
+ guf.funcname;
+
+CREATE VIEW gp_stat_xact_user_functions_summary AS
+SELECT
+ xuf.funcid,
+ xuf.schemaname,
+ xuf.funcname,
+ sum(xuf.calls) AS calls,
+ sum(xuf.total_time) AS total_time,
+ sum(xuf.self_time) AS self_time
+FROM
+ gp_stat_xact_user_functions xuf
+GROUP BY
+ xuf.funcid,
+ xuf.schemaname,
+ xuf.funcname;
+
+CREATE VIEW gp_stat_slru_summary AS
+SELECT
+ gss.name,
+ sum(gss.blks_zeroed) AS blks_zeroed,
+ sum(gss.blks_hit) AS blks_hit,
+ sum(gss.blks_read) AS blks_read,
+ sum(gss.blks_written) AS blks_written,
+ sum(gss.blks_exists) AS blks_exists,
+ sum(gss.flushes) AS flushes,
+ sum(gss.truncates) AS truncates,
+ max(gss.stats_reset) AS stats_reset
+FROM
+ gp_stat_slru gss
+GROUP BY
+ gss.name;
+
+
CREATE VIEW gp_stat_progress_vacuum_summary AS
SELECT
max(coalesce(a1.pid, 0)) as pid,
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org