gistfile1.txt

# There are also several alias types for oid: regproc, regprocedure, regoper, regoperator, regclass, regtype, regrole, regnamespace, regconfig, and regdictionary.

DROP TABLE IF EXISTS pgpro_audit_heap;
CREATE TABLE pgpro_audit_heap AS
SELECT
  pg_catalog.current_database() as dbname,
  nspace.nspname AS schema,
  class.relname AS relname,
  CASE WHEN tblspace.spcname is NULL THEN 'pg_default' ELSE tblspace.spcname END as tablespace,
  pg_catalog.pg_total_relation_size(class.oid) AS total_size,
  pg_catalog.pg_relation_size(class.oid) AS heap_size,
  CASE class.reltoastrelid WHEN 0 THEN NULL ELSE pg_catalog.pg_total_relation_size(class.reltoastrelid) END AS toast_size,
  pg_catalog.pg_indexes_size(class.oid) AS idx_size,
  stat_t.seq_scan AS seqscan,
  stat_t.idx_scan AS idxScan,
  CASE (stat_t.n_live_tup+stat_t.n_dead_tup) WHEN 0 THEN 0 ELSE (stat_t.n_tup_upd/(stat_t.n_live_tup+stat_t.n_dead_tup)::float4 * 100)::bigint  END AS changed,
  CASE (stat_t.n_live_tup+stat_t.n_dead_tup) WHEN 0 THEN 0 ELSE (stat_t.n_dead_tup/(stat_t.n_live_tup+stat_t.n_dead_tup)::float4 * 100)::bigint END AS dead,
  stat_t.last_autovacuum AS autovac_ts,
  stat_t.last_vacuum AS vac_ts,
  stat_t.last_autoanalyze AS autoanalyze_ts,
  class.reloptions as relopt,
  CASE (stat_t_io.heap_blks_hit+stat_t_io.heap_blks_read) WHEN 0 THEN 0 ELSE (stat_t_io.heap_blks_hit/(stat_t_io.heap_blks_hit+stat_t_io.heap_blks_read)::float4 * 100)::bigint END AS heap_bufhit,
  CASE (stat_t_io.idx_blks_hit+stat_t_io.idx_blks_read) WHEN 0 THEN 0 ELSE (stat_t_io.idx_blks_hit/(stat_t_io.idx_blks_hit+stat_t_io.idx_blks_read)::float4 * 100)::bigint END AS idx_bufhit
FROM pg_class class
    LEFT JOIN pg_catalog.pg_namespace nspace ON class.relnamespace = nspace.oid
    LEFT JOIN pg_catalog.pg_stat_all_tables stat_t ON class.oid = stat_t.relid
    LEFT JOIN pg_catalog.pg_statio_all_tables stat_t_io ON class.oid = stat_t_io.relid 
    LEFT JOIN pg_catalog.pg_stat_all_indexes stat_i ON class.oid = stat_i.indexrelid
    LEFT JOIN pg_catalog.pg_tablespace tblspace ON class.reltablespace = tblspace.oid
WHERE class.relkind IN ('r', 'p', 'm')
    AND class.relpersistence = 'p'
    AND nspace.nspname <> 'information_schema'
ORDER BY pg_catalog.pg_total_relation_size(class.oid) DESC;

SELECT
  dbname,
  pg_size_pretty(sum(total_size)) AS total,
  pg_size_pretty(sum(heap_size)) AS heap,
  pg_size_pretty(sum(toast_size)) AS toast,
  pg_size_pretty(sum(idx_size)) AS index
FROM pgpro_audit_heap
GROUP BY dbname;

SELECT
  dbname,
  schema,
  relname,
  tablespace,
  pg_size_pretty(total_size) AS total,
  pg_size_pretty(heap_size) AS heap,
  pg_size_pretty(toast_size) AS toast,
  pg_size_pretty(idx_size) AS index,
  changed,
  dead,
  seqscan,
  idxScan
FROM pgpro_audit_heap
ORDER BY total_size DESC;

SELECT
  dbname,
  schema,
  relname,
  pg_size_pretty(total_size) AS total,
  idxScan,
  seqscan,
  changed,
  dead,
  heap_bufhit,
  idx_bufhit
FROM pgpro_audit_heap
ORDER BY total_size DESC;

SELECT
  dbname,
  schema,
  pg_size_pretty(sum(total_size)) AS total
FROM pgpro_audit_heap
GROUP BY db_name, schema
ORDER BY sum(total_size) DESC;



-----------------------------------------------------------

# Index information
DROP TABLE IF EXISTS pgpro_audit_idx;
CREATE TABLE pgpro_audit_idx AS
WITH settings AS (
  SELECT setting::bigint AS block_size FROM pg_settings WHERE name = 'block_size'
  )
SELECT
  pg_catalog.current_database() as db_name,
  nspace.nspname AS schema,
  class.relname AS index,
  CASE WHEN tblspace.spcname is NULL THEN 'pg_default' ELSE tblspace.spcname END as tablespace,
  am.amname AS type,
  CASE index.indisvalid WHEN 'f' THEN 'INVALID' ELSE NULL END AS valid,
  stat_i.relname::text AS table_name,
  pg_catalog.pg_relation_size(class.oid) AS size,
  stat_i.idx_scan AS idxscan,
  CASE (stat_i_io.idx_blks_hit+stat_i_io.idx_blks_read) WHEN 0 THEN 0 ELSE (stat_i_io.idx_blks_hit/(stat_i_io.idx_blks_hit+stat_i_io.idx_blks_read)::float4 * 100)::bigint END AS idx_bufhit,
--  constr.conname IS NOT NULL as is_in_constr,
  EXISTS(SELECT 1 from pg_catalog.pg_constraint constr WHERE class.oid = constr.conindid) as is_in_constr
FROM settings, pg_catalog.pg_class class
    LEFT JOIN pg_catalog.pg_index index ON class.oid = index.indexrelid
    LEFT JOIN pg_catalog.pg_am am ON class.relam = am.oid
    LEFT JOIN pg_catalog.pg_namespace nspace ON class.relnamespace = nspace.oid
    LEFT JOIN pg_catalog.pg_stat_all_indexes stat_i ON class.oid = stat_i.indexrelid
    LEFT JOIN pg_catalog.pg_statio_all_indexes stat_i_io ON class.oid = stat_i_io.indexrelid
    LEFT JOIN pg_catalog.pg_tablespace tblspace ON class.reltablespace = tblspace.oid
--    LEFT JOIN LATERAL (SELECT conname from pg_catalog.pg_constraint constr WHERE class.oid = constr.conindid LIMIT 1) constr ON TRUE
WHERE class.relkind = 'i'
    AND class.relpersistence = 'p'
    AND nspace.nspname <> 'information_schema'
--    AND EXISTS(SELECT 1 FROM pg_catalog.pg_constraint constr WHERE class.oid = constr.conindid)
ORDER BY pg_catalog.pg_relation_size(class.oid) DESC;
---------------------------------------------

WITH constr_indexes AS ( SELECT DISTINCT conindid FROM pg_constraint )

SELECT schemaname || '.' || relname AS TABLE,
       indexrelname AS INDEX,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
       idx_scan AS index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
LEFT JOIN constr_indexes ON ui.indexrelid = constr_indexes.conindid
WHERE NOT indisunique
  AND idx_scan < 50
  AND pg_relation_size(relid) > 5 * 8192
  AND constr_indexes.conindid is NULL
ORDER BY                                             
    pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
    pg_relation_size(i.indexrelid) DESC;


WITH constr_indexes AS ( SELECT DISTINCT conindid FROM pg_constraint )

SELECT pg_size_pretty(sum(pg_relation_size(i.indexrelid))) AS unused_indexes_size
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
LEFT JOIN constr_indexes ON ui.indexrelid = constr_indexes.conindid
WHERE NOT indisunique
  AND idx_scan = 0
  AND pg_relation_size(relid) > 5 * 8192
  AND constr_indexes.conindid is NULL;


SELECT
  db_name,
  schema,
  index,
  type,
  table_name,
  pg_size_pretty(size) AS idx_size,
  idxscan,
  idx_bufhit
FROM pgpro_audit_idx
ORDER BY size DESC;


SELECT
  schema,
  index,
  type,
  table_name,
  pg_size_pretty(size) AS size,
  idxscan,
  idx_bufhit
FROM pgpro_audit_idx
WHERE not is_in_constr AND idxscan < 100;

SELECT
  pg_size_pretty(sum(size)) AS size
FROM pgpro_audit_idx
WHERE not is_in_constr AND idxscan = 0;

------------------------------------------


------------------------------------------------------------

# Toast information !!!
DROP TABLE IF EXISTS pgpro_audit_toast;
CREATE TABLE pgpro_audit_toast AS
SELECT
  pg_catalog.current_database() as db_name,
  class.relname AS toast,
  CASE WHEN tblspace.spcname is NULL THEN 'pg_default' ELSE tblspace.spcname END as tablespace,
  pg_catalog.pg_total_relation_size(class.oid) AS total_size,
  pg_catalog.pg_relation_size(class.oid) AS heap_size,
  pg_catalog.pg_size_pretty(pg_catalog.pg_indexes_size(class.oid)) AS idx_size,
  stat_t.seq_scan AS SeqScan,
  stat_t.idx_scan AS IdxScan,
  CASE (stat_t.n_live_tup+stat_t.n_dead_tup) WHEN 0 THEN 0 ELSE (stat_t.n_tup_upd/(stat_t.n_live_tup+stat_t.n_dead_tup)::float4 * 100)::bigint  END AS changed,
  CASE (stat_t.n_live_tup+stat_t.n_dead_tup) WHEN 0 THEN 0 ELSE (stat_t.n_dead_tup/(stat_t.n_live_tup+stat_t.n_dead_tup)::float4 * 100)::bigint END AS dead,
  stat_t.last_autovacuum AS autovac_ts,
  stat_t.last_autoanalyze AS autoanalyze_ts,
  CASE (stat_t_io.heap_blks_hit+stat_t_io.heap_blks_read) WHEN 0 THEN 0 ELSE (stat_t_io.heap_blks_hit/(stat_t_io.heap_blks_hit+stat_t_io.heap_blks_read)::float4 * 100)::bigint END AS heap_bufhit,
  CASE (stat_t_io.idx_blks_hit+stat_t_io.idx_blks_read) WHEN 0 THEN 0 ELSE (stat_t_io.idx_blks_hit/(stat_t_io.idx_blks_hit+stat_t_io.idx_blks_read)::float4 * 100)::bigint END AS idx_bufhit
FROM pg_class class
    LEFT JOIN pg_catalog.pg_stat_all_tables stat_t ON class.oid = stat_t.relid
    LEFT JOIN pg_catalog.pg_statio_all_tables stat_t_io ON class.oid = stat_t_io.relid 
    LEFT JOIN pg_catalog.pg_stat_all_indexes stat_i ON class.oid = stat_i.indexrelid
    LEFT JOIN pg_catalog.pg_tablespace tblspace ON class.reltablespace = tblspace.oid
WHERE class.relkind = 't'
    AND class.relpersistence = 'p'
ORDER BY pg_catalog.pg_total_relation_size(class.oid) DESC;

SELECT
  toast,
  pg_size_pretty(total_size) AS total,
  pg_size_pretty(heap_size) as heap,
  seqscan,
  idxscan,
  changed,
  dead,
  autovac_ts,
  heap_bufhit,
  idx_bufhit
from pgpro_audit_toast;

---------------------------------------------------------------

# HEAP FRAGMENTATION
CREATE EXTENSION IF NOT EXISTS pg_freespacemap;

SET default_transaction_isolation to 'repeatable read';

DROP TABLE IF EXISTS pgpro_audit_heap_fragmentation;
CREATE TABLE pgpro_audit_heap_fragmentation AS

WITH tables AS (
  SELECT nspace.nspname AS schema,
         class.relname AS relname,
         class.oid,
         CASE WHEN tblspace.spcname is NULL THEN 'pg_default' ELSE tblspace.spcname END as tablespace,
         CASE (stat_t.n_live_tup+stat_t.n_dead_tup)
          WHEN 0 THEN 0
          ELSE (stat_t.n_dead_tup/(stat_t.n_live_tup+stat_t.n_dead_tup)::float4 * 100)::int
          END AS dead
  FROM pg_catalog.pg_class class
  LEFT JOIN pg_catalog.pg_namespace nspace ON class.relnamespace = nspace.oid
  LEFT JOIN pg_catalog.pg_tablespace tblspace ON class.reltablespace = tblspace.oid
  LEFT JOIN pg_catalog.pg_stat_all_tables stat_t ON class.oid = stat_t.relid
  WHERE class.relkind IN ('r', 'm', 'p', 't')
  AND class.relpersistence = 'p'
  AND nspace.nspname <> 'information_schema'),

    current AS (
      SELECT pg_catalog.current_database() as dbname,
      setting::int as block_size from pg_settings where name = 'block_size')

SELECT
   current.dbname,
   tables.schema,
   tables.relname,
   tables.tablespace,
   count(blkno) * current.block_size AS total_space,
   (count(blkno) * current.block_size) - sum(avail) AS live_space,
   sum(avail) AS free_space,
   tables.dead,
   ((sum(avail)/(count(blkno) * current.block_size)::float) * 100)::int AS fragmentation
FROM tables, current, pg_freespace(tables.oid) as frag
GROUP BY current.dbname, current.block_size, tables.schema, tables.relname, tables.tablespace, tables.dead
ORDER BY total_space DESC;
------------------------------------------------------------------------------------

SELECT
      dbname,
      schema,
      relname,
      pg_size_pretty(total_space) AS total,
      pg_size_pretty(live_space) as live,
      pg_size_pretty((live_space * (dead::float/100))::bigint) as dead_size,
      pg_size_pretty(free_space) as wasted,
      dead,
      fragmentation
FROM pgpro_audit_heap_fragmentation
ORDER BY total_space DESC;

SELECT
      pg_size_pretty(sum(total_space)) AS total,
      pg_size_pretty(sum(live_space)) as live,
      pg_size_pretty(sum(live_space * (dead::float/100))::numeric) as dead,
      pg_size_pretty(sum(free_space)) as wasted
FROM pgpro_audit_heap_fragmentation;

SELECT
      dbname,
      schema,
      relname,
      pg_size_pretty(total_space) AS total,
      pg_size_pretty(live_space) as live,
      pg_size_pretty((live_space * (dead::float/100))::bigint) as dead_size,
      pg_size_pretty(free_space) as wasted,
      dead,
      fragmentation
FROM pgpro_audit_heap_fragmentation WHERE free_space is not NULL
ORDER BY free_space DESC;
--------------------------------------------------------------
--------------------------------------------------------------

# Index fragmentation information
DO
$$
BEGIN
DROP TABLE IF EXISTS pgpro_audit_idx_fragmentation;
CREATE TABLE pgpro_audit_idx_fragmentation AS

WITH settings AS (
  SELECT setting::bigint AS block_size FROM pg_settings WHERE name = 'block_size'
  ),
btree_indexes AS (
  SELECT
    class.oid,
    nspace.nspname AS schema,
    class.relname AS index,
    class.reltuples
  FROM pg_class class
  LEFT JOIN pg_catalog.pg_am am ON class.relam = am.oid
  LEFT JOIN pg_catalog.pg_namespace nspace ON class.relnamespace = nspace.oid
  WHERE am.amname = 'btree'
    AND class.relpersistence = 'p'
    AND nspace.nspname <> 'information_schema'
)

SELECT
  btree_indexes.schema,
  btree_indexes.index,
  btree_indexes.reltuples,
  frag.leaf_pages,
  frag.avg_leaf_density,
  frag.index_size AS total_space,
  frag.empty_pages * settings.block_size + frag.deleted_pages * settings.block_size AS dead_space,
  CASE frag.leaf_pages WHEN 0 THEN 0
    ELSE (frag.leaf_pages * settings.block_size * (100-frag.avg_leaf_density)/100)::bigint
    END AS wasted_leaf_space
FROM settings, btree_indexes, pgstatindex(btree_indexes.oid) as frag;

EXCEPTION
  WHEN undefined_table THEN NULL;
  WHEN OTHERS THEN RAISE;
END$$;
---------------------------------------------------------------------------

select 
  dbname,
  schema,
  index,
  pg_size_pretty(total_space) as total,
  pg_size_pretty(dead_space) as dead,
  pg_size_pretty(wasted_leaf_space) as wasted,
  round((wasted_leaf_space + dead_space) / total_space::numeric * 100,2) as frag
from pgpro_audit_idx_fragmentation
order by total_space DESC;

SELECT
      pg_size_pretty(sum(total_space)) AS total,
      pg_size_pretty(sum(total_space) - sum(dead_space) - sum(wasted_leaf_space)) as live,
      pg_size_pretty(sum(dead_space)) as dead,
      pg_size_pretty(sum(wasted_leaf_space)) as wasted
FROM pgpro_audit_idx_fragmentation;
----------------------------------------------------------------------
--------------------------------------------------------------------------





#autovacuum

WITH 
table_opts AS( SELECT pg_class.oid,relname, nspname, array_to_string(reloptions, '') AS relopts FROM pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid), 
vacuum_settings AS ( SELECT oid,relname,nspname, CASE WHEN relopts LIKE '%autovacuum_vacuum_threshold%' THEN regexp_replace(relopts, '.*autovacuum_vacuum_threshold=([0-9.]+).*', E'\\\\\\1')::integer ELSE current_setting('autovacuum_vacuum_threshold')::integer END AS autovacuum_vacuum_threshold, CASE WHEN relopts LIKE '%autovacuum_vacuum_scale_factor%' THEN regexp_replace(relopts, '.*autovacuum_vacuum_scale_factor=([0-9.]+).*', E'\\\\\\1')::real ELSE current_setting('autovacuum_vacuum_scale_factor')::real END AS autovacuum_vacuum_scale_factor FROM table_opts )

SELECT
vacuum_settings.nspname AS schema,
vacuum_settings.relname AS table,
to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') AS last_vacuum,
to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum, to_char(pg_class.reltuples, '9G999G999G999') AS rowcount, to_char(psut.n_dead_tup, '9G999G999G999') AS dead_rowcount,
to_char(autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples), '9G999G999G999') AS autovacuum_threshold, CASE WHEN autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup THEN 'yes' END AS expect_autovacuum
FROM
pg_stat_user_tables psut
INNER JOIN pg_class ON psut.relid = pg_class.oid
INNER JOIN vacuum_settings ON pg_class.oid = vacuum_settings.oid ORDER BY 1; 






# Script to determine orphanized files in all tablespaces





--------------------------------------------------------------
# pg_stat_statements

--------------------------------------------------------------

# get settings
SELECT name, setting from pg_file_settings where applied;

# get autovacuum settings for tables:
SELECT
  relnamespace::regnamespace AS schema,
  relname AS relation,
  reloptions
FROM pg_class
WHERE relkind = 'r' and reloptions is not NULL;

# Get double indexes


# Storage INFO
lsblk --all -o "NAME,MAJ:MIN,ALIGNMENT,MIN-IO,PHY-SEC,LOG-SEC,SCHED,RQ-SIZE,DISC-ALN,DISC-GRAN,DISC-MAX,DISC-ZERO,TYPE,RAND,TRAN,SUBSYSTEMS,REV,VENDOR,MODEL"


MISC:
SELECT query, state,locktype,mode
FROM pg_locks
JOIN pg_stat_activity
  USING (pid)
WHERE relation::regclass = 'relname'::regclass
  AND granted IS TRUE
  AND backend_xmin IS NOT NULL;

----------------
CPU Governor
get cpu governor:
  cpupower frequency-info

set cpu governor:
  cpupower frequency-set -g performance

----------------------------------------------------------
dmesg -T
----------------------------------------------------------
text log
 -------------------------------------------
mamonsu report
--------------------------------

sysctl -a
--------------------------
THP
# echo never > /sys/kernel/mm/transparent_hugepage/enabled
# echo never > /sys/kernel/mm/transparent_hugepage/defrag
--------------------------
select pid, age(backend_xid), (EXTRACT(EPOCH from now() - xact_start))::bigint as time from pg_stat_activity order by xact_start ASC limit 1;
select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_flush_lsn(), (select redo_lsn from pg_control_checkpoint())));