postgres_monitoring.sql

create view index_dupes as 
SELECT pg_size_pretty(sum(pg_relation_size(sub.idx))::bigint) AS size,
    (array_agg(sub.idx))[1] AS idx1,
    (array_agg(sub.idx))[2] AS idx2,
    (array_agg(sub.idx))[3] AS idx3,
    (array_agg(sub.idx))[4] AS idx4
FROM ( SELECT pg_index.indexrelid::regclass AS idx,
           (((((((pg_index.indrelid::text || '
'::text) || pg_index.indclass::text) || '
'::text) || pg_index.indkey::text) || '
'::text) || COALESCE(pg_index.indexprs::text, ''::text)) || '
'::text) || COALESCE(pg_index.indpred::text, ''::text) AS key
       FROM pg_index) sub
GROUP BY sub.key
HAVING count(*) > 1
ORDER BY (sum(pg_relation_size(sub.idx))) DESC;

create view index_stats as
SELECT t.schemaname,
    t.tablename,
    foo.indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(((quote_ident(t.schemaname::text) || '.'::text) || quote_ident(t.tablename::text))::regclass)) AS table_size,
    pg_size_pretty(pg_relation_size(((quote_ident(t.schemaname::text) || '.'::text) || quote_ident(foo.indexrelname::text))::regclass)) AS index_size,
    CASE
        WHEN foo.indisunique THEN 'Y'::text
        ELSE 'N'::text
        END AS "unique",
    foo.number_of_scans,
    foo.tuples_read,
    foo.tuples_fetched
FROM pg_tables t
         LEFT JOIN pg_class c ON t.tablename = c.relname
         LEFT JOIN ( SELECT c_1.relname AS ctablename,
                         ipg.relname AS indexname,
                         x.indnatts AS number_of_columns,
                         psai.idx_scan AS number_of_scans,
                         psai.idx_tup_read AS tuples_read,
                         psai.idx_tup_fetch AS tuples_fetched,
                         psai.indexrelname,
                         x.indisunique,
                         psai.schemaname
                     FROM pg_index x
                              JOIN pg_class c_1 ON c_1.oid = x.indrelid
                              JOIN pg_class ipg ON ipg.oid = x.indexrelid
                              JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid) foo ON t.tablename = foo.ctablename AND t.schemaname = foo.schemaname
WHERE t.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])
ORDER BY t.schemaname, t.tablename;

create view indexes as 
SELECT pg_class.relname,
    pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes,
    pg_class.reltuples AS num_rows,
    count(foo.indexname) AS number_of_indexes,
    CASE
        WHEN x.is_unique = 1 THEN 'Y'::text
        ELSE 'N'::text
        END AS "unique",
    sum(
            CASE
                WHEN foo.number_of_columns = 1 THEN 1
                ELSE 0
                END) AS single_column,
    sum(
            CASE
                WHEN foo.number_of_columns IS NULL THEN 0
                WHEN foo.number_of_columns = 1 THEN 0
                ELSE 1
                END) AS multi_column
FROM pg_namespace
         LEFT JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
         LEFT JOIN ( SELECT pg_index.indrelid,
                         max(pg_index.indisunique::integer) AS is_unique
                     FROM pg_index
                     GROUP BY pg_index.indrelid) x ON pg_class.oid = x.indrelid
         LEFT JOIN ( SELECT c.relname AS ctablename,
                         ipg.relname AS indexname,
                         x_1.indnatts AS number_of_columns
                     FROM pg_index x_1
                              JOIN pg_class c ON c.oid = x_1.indrelid
                              JOIN pg_class ipg ON ipg.oid = x_1.indexrelid) foo ON pg_class.relname = foo.ctablename
WHERE pg_namespace.nspname = 'public'::name AND pg_class.relkind = 'r'::"char"
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY pg_class.relname;

create view locaks_active as 
SELECT age(now(), a.query_start) AS age,
    l.relation::regclass AS relation,
    l.mode,
    l.locktype,
    l.page,
    l.tuple,
    l.granted,
    a.query,
    l.transactionid,
    a.pid
FROM pg_stat_activity a
         JOIN pg_locks l ON l.pid = a.pid
ORDER BY l.granted, a.query_start;

create view queries_active as 
SELECT pg_stat_activity.pid,
    pg_stat_activity.usename,
    pg_stat_activity.client_addr,
    age(clock_timestamp(), pg_stat_activity.query_start)::text AS query_time,
    age(clock_timestamp(), pg_stat_activity.xact_start)::text AS xact_time,
    pg_stat_activity.wait_event_type,
    pg_stat_activity.wait_event,
    pg_stat_activity.state,
    pg_stat_activity.query
FROM pg_stat_activity
WHERE pg_stat_activity.state = 'active'::text
ORDER BY (age(clock_timestamp(), pg_stat_activity.query_start)) DESC;

create view queries_all as 
SELECT pg_stat_activity.pid,
    pg_stat_activity.usename,
    pg_stat_activity.client_addr,
    age(clock_timestamp(), pg_stat_activity.query_start)::text AS query_time,
    age(clock_timestamp(), pg_stat_activity.xact_start)::text AS xact_time,
    pg_stat_activity.wait_event_type,
    pg_stat_activity.wait_event,
    pg_stat_activity.state,
    pg_stat_activity.query
FROM pg_stat_activity
ORDER BY (age(clock_timestamp(), pg_stat_activity.query_start)) DESC;

create view table_sizes as 
SELECT a.oid,
    a.table_schema,
    a.table_name,
    a.row_estimate,
    a.total_bytes,
    a.index_bytes,
    a.toast_bytes,
    a.table_bytes,
    pg_size_pretty(a.total_bytes) AS total,
    pg_size_pretty(a.index_bytes) AS index,
    pg_size_pretty(a.toast_bytes) AS toast,
    pg_size_pretty(a.table_bytes) AS "table"
FROM ( SELECT a_1.oid,
           a_1.table_schema,
           a_1.table_name,
           a_1.row_estimate,
           a_1.total_bytes,
           a_1.index_bytes,
           a_1.toast_bytes,
           a_1.total_bytes - a_1.index_bytes - COALESCE(a_1.toast_bytes, 0::bigint) AS table_bytes
       FROM ( SELECT c.oid,
                  n.nspname AS table_schema,
                  c.relname AS table_name,
                  c.reltuples AS row_estimate,
                  pg_total_relation_size(c.oid::regclass) AS total_bytes,
                  pg_indexes_size(c.oid::regclass) AS index_bytes,
                  pg_total_relation_size(c.reltoastrelid::regclass) AS toast_bytes
              FROM pg_class c
                       LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
              WHERE c.relkind = 'r'::"char") a_1) a
WHERE a.table_schema <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])
ORDER BY a.total_bytes DESC;

create view table_statio as 
SELECT pg_statio_user_tables.relname,
    pg_statio_user_tables.heap_blks_read / 1000000 AS heap_blks_read,
    pg_statio_user_tables.heap_blks_hit / 1000000 AS heap_blks_hit,
    pg_statio_user_tables.heap_blks_hit / (pg_statio_user_tables.heap_blks_read + 1) AS heap_hit_rat,
    pg_statio_user_tables.idx_blks_read / 1000000 AS idx_blks_read,
    pg_statio_user_tables.idx_blks_hit / 1000000 AS idx_blks_hit,
    pg_statio_user_tables.idx_blks_hit / (pg_statio_user_tables.idx_blks_read + 1) AS idx_hit_rat,
    pg_statio_user_tables.toast_blks_read / 1000000 AS toast_blks_read,
    pg_statio_user_tables.toast_blks_hit / 1000000 AS toast_blks_hit,
    pg_statio_user_tables.toast_blks_hit / (pg_statio_user_tables.toast_blks_read + 1) AS toast_hit_rat,
    pg_statio_user_tables.tidx_blks_read / 1000000 AS tidx_blks_read,
    pg_statio_user_tables.tidx_blks_hit / 1000000 AS tidx_blks_hit,
    pg_statio_user_tables.tidx_blks_hit / (pg_statio_user_tables.tidx_blks_read + 1) AS tidx_hit_rat
FROM pg_statio_user_tables
WHERE pg_statio_user_tables.schemaname = 'public'::name
ORDER BY pg_statio_user_tables.relname;