postgresql utils

debug-process_activity.sql

/*
 * Current activity of all server processes
 */
SELECT
    pid
  , query
  , application_name
  , usename AS user_name
  , state
  , wait_event_type
  , wait_event
  , pg_blocking_pids(pid) AS blocking_pids
  , cardinality(pg_blocking_pids(pid)) > 0 AS is_blocked
  , backend_start
  , query_start
  , state_change AS last_state_change
FROM pg_stat_activity;

stats-agg_index_usage.sql

/*
 * Aggregate stats for indexes of user tables grouped by usage
 */
SELECT
    count(*) AS count_indexes
  , index_stats.idx_scan AS index_scans
  , pg_index.indisunique AS is_unique
  , sum(pg_relation_size(index_stats.indexrelid)) AS sum_index_size
  , pg_size_pretty(sum(pg_relation_size(index_stats.indexrelid))) AS sum_index_size_pretty
FROM pg_catalog.pg_stat_user_indexes index_stats
INNER JOIN pg_catalog.pg_index
  ON pg_index.indexrelid = index_stats.indexrelid
INNER JOIN pg_indexes
  ON index_stats.schemaname = pg_indexes.schemaname
  AND index_stats.relname = pg_indexes.tablename
  AND index_stats.indexrelname = pg_indexes.indexname
GROUP BY index_stats.idx_scan, pg_index.indisunique
ORDER BY index_scans ASC, sum_index_size DESC;

stats-index_usage.sql

/*
 * Usage stats for all indexes of user tables
 */
WITH table_stats AS (
  SELECT
      pi.inhparent AS parent_oid
    , pi.inhparent::regclass AS parent_name
    , psut.relid AS table_oid
    , psut.relname AS table_name
    , psut.idx_scan AS idx_scans
    , psut.seq_scan AS seq_scans
    , psut.idx_scan + psut.seq_scan AS all_scans
    , pg_total_relation_size(psut.relid) AS total_size
    , pg_relation_size(psut.relid) AS data_size
    , pg_total_relation_size(psut.relid) - pg_relation_size(psut.relid) AS external_size
    , pg_size_pretty(pg_total_relation_size(psut.relid)) AS total_size_pretty
    , pg_size_pretty(pg_relation_size(psut.relid)) AS data_size_pretty
    , pg_size_pretty(pg_total_relation_size(psut.relid) - pg_relation_size(psut.relid)) AS external_size_pretty
  FROM pg_stat_user_tables psut
  LEFT JOIN pg_inherits pi
    ON pi.inhrelid = psut.relid
) SELECT
    table_stats.parent_name
  , index_stats.relname AS table_name
  , index_stats.indexrelname AS index_name
  , pg_relation_size(index_stats.indexrelid) AS index_size
  , pg_size_pretty(pg_relation_size(index_stats.indexrelid)) AS index_size_pretty
  , round(100.0 * pg_relation_size(index_stats.indexrelid) / table_stats.total_size, 2) AS pct_table_size
  , index_stats.idx_scan AS index_scans
  , pg_index.indisunique AS is_unique
  , pg_constraint IS NOT NULL AS is_constraint
  , pg_indexes.indexdef ~* 'USING btree' AS is_btree
  , 0 = ANY(pg_index.indkey) AS uses_expression -- e.g. sort()
  , table_stats.total_size AS table_total_size
  , table_stats.total_size_pretty AS table_total_size_pretty
  , table_stats.data_size AS table_data_size
  , table_stats.data_size_pretty AS table_data_size_pretty
  , table_stats.idx_scans AS table_index_scans
  , table_stats.seq_scans AS table_seq_scans
  , table_stats.all_scans AS table_total_scans
FROM pg_stat_user_indexes index_stats
INNER JOIN table_stats
  ON table_stats.table_oid = index_stats.relid
  AND table_stats.total_size > 0
INNER JOIN pg_index
  ON pg_index.indexrelid = index_stats.indexrelid
INNER JOIN pg_indexes
  ON index_stats.schemaname = pg_indexes.schemaname
  AND index_stats.relname = pg_indexes.tablename
  AND index_stats.indexrelname = pg_indexes.indexname
LEFT JOIN pg_constraint
  ON pg_constraint.conindid = index_stats.indexrelid;

stats-table_usage.sql

/*
 * High-level usage stats for all user tables
 */
SELECT
    pi.inhparent AS parent_oid
  , pi.inhparent::regclass AS parent_name
  , psut.relid AS table_oid
  , psut.relname AS table_name
  , psut.idx_scan AS idx_scans
  , psut.seq_scan AS seq_scans
  , psut.idx_scan + psut.seq_scan AS all_scans
  , pg_total_relation_size(psut.relid) AS total_size
  , pg_relation_size(psut.relid) AS data_size
  , pg_total_relation_size(psut.relid) - pg_relation_size(psut.relid) AS external_size
  , pg_size_pretty(pg_total_relation_size(psut.relid)) AS total_size_pretty
  , pg_size_pretty(pg_relation_size(psut.relid)) AS data_size_pretty
  , pg_size_pretty(pg_total_relation_size(psut.relid) - pg_relation_size(psut.relid)) AS external_size_pretty
FROM pg_stat_user_tables psut
LEFT JOIN pg_inherits pi
  ON pi.inhrelid = psut.relid