| Summary |
' as info;
select '| Item', ' | Value' as info;
select ' |
| '||' Database :', '',
' | '||current_database()
union
select ' |
| '||' Version :', '',
' | '||substring(version() for position('on' in version())-1)
union
select ' |
| '||' DB Size:', '',
' | '||pg_size_pretty(sum(pg_database_size(datname)))
from pg_database;
select ' |
| '||' Created :',
'', ' | '|| (pg_stat_file('base/'||oid ||'/PG_VERSION')).modification
from pg_database
where datname='template0';
select ' |
| '||' Started :',
'', ' | '||pg_postmaster_start_time()
union
select ' |
| '||' Memory buffers (MB) :',
'', ' | '||trunc(sum(setting::int*8)/1024)
from pg_settings
where name like '%buffers'
union
select ' |
| '||' Work area (MB) :',
'', ' | '||trunc(sum(setting::int)/1024)
from pg_settings
where name like '%mem'
union
select ' |
| '||' Wal Archiving :',
'', ' | '||setting
from pg_settings
where name like 'archive_mode';
select ' |
| '||' Databases :', '', ' | '||count(*)
from pg_database
where not datistemplate
union
select ' |
| '||' Defined Users/Roles :',
'', ' | '||sum(case when rolcanlogin then 1 else 0 end)||
' / '|| sum(case when rolcanlogin then 0 else 1 end)
from pg_roles
union
select ' |
| '||' Defined Schemata :',
'', ' | '||count(distinct relowner)
from pg_class
union
select ' |
| '||' Defined Tables :',
'', ' | '||count(*)
from pg_class
where relkind='r';
select ' |
| '||' Sessions :', '', ' | '||count(*)
from pg_stat_activity
union
select ' |
| '||' Sessions (active) :', '', ' | '||count(*)
from pg_stat_activity
where state = 'active';
select ' |
| '||' Host IP :',
'', ' | '||inet_server_addr()
union
select ' |
| '||' Port :',
'', ' | '||inet_server_port()
order by 2;
select ' |
| Version check |
' ;
select '| Version',
' | Supported',
' | Last major release (N or N-2)',
' | Last minor release (N or N-1)',
' | Notes';
SELECT ' |
| '||substring(version() for position('on' in version())-1);
SELECT ' | ', CASE WHEN trunc(cast(current_setting('server_version_num')
as integer)/100)
in (1000, 1100, 1200, 1300, 1400, 1500) THEN 'YES'
ELSE 'NO' END;
SELECT ' | ', CASE WHEN trunc(cast(current_setting('server_version_num')
as integer)/100)
in (1300, 1400, 1500) THEN 'YES'
ELSE 'NO' END; -- last2 release
SELECT ' | ', CASE WHEN cast(current_setting('server_version_num')as integer)
in (90623,90624, 100023,100022, 110019,110018,110020, 120014,120013,120015,
130010,130009,130011,
140007,140006,140008, 150000,150001,150003) THEN 'YES'
ELSE 'NO' END; -- last2 update
select ' | Latest Releases: 15.2, 14.7, 13.10, 12.14, 11.19';
select '
Latest Unsupported: 10.23, 9.6.24, 9.5.25, 9.4.26, 9.3.25, 9.2.24, 9.1.24, 9.0.23,';
select ' 8.4.21, 8.3.23, 8.2.23, 8.1.23, 8.0.26, 7.4.30; 6.5.3, 1.0.9';
select ' |
| Schema/Object Matrix |
' as info;
select '| Schema | Owner',
' | Table',
' | Index',
' | View',
' | Sequence',
' | Composite type',
' | Foreign table',
' | TOAST table',
' | Materialized view',
' | Partitioned table',
' | Partitioned index',
' | Unlogged',
' | Temporary',
' | TOTAL'
as info;
select ' |
| '||nspname, ' | '||rolname,
' | '||sum(case when relkind='r' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='i' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='v' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='S' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='c' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='f' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='t' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='m' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='p' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='I' THEN 1 ELSE 0 end),
' | '||sum(case when relpersistence='u' THEN 1 ELSE 0 end),
' | '||sum(case when relpersistence='t' THEN 1 ELSE 0 end),
' | '||count(*)
from pg_class, pg_roles, pg_namespace
where relowner=pg_roles.oid
and relnamespace=pg_namespace.oid
group by rolname, nspname
order by nspname, rolname;
select ' |
| TOTAL | TOTAL',
' | '||sum(case when relkind='r' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='i' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='v' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='S' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='c' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='f' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='t' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='m' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='p' THEN 1 ELSE 0 end),
' | '||sum(case when relkind='I' THEN 1 ELSE 0 end),
' | '||sum(case when relpersistence='u' THEN 1 ELSE 0 end),
' | '||sum(case when relpersistence='t' THEN 1 ELSE 0 end),
' | '||count(*)
from pg_class;
select ' |
| Space Usage |
' as info;
select '| Owner',
' | Table#',
' | Tables rows',
' | Tables KBytes',
' | Indexes KBytes',
' | TOAST KBytes',
' | Total KBytes'
as info;
select ' |
| '||rolname,
' | '||to_char(sum(case when relkind='r' THEN 1 ELSE 0 end),'999G999G999G999G999G999G999'),
' | '||to_char(sum(case when relkind='r' THEN greatest(reltuples,0) ELSE 0 end),'999G999G999G999G999G999G999'),
' | '||to_char(trunc(sum(case when relkind='r' THEN cast(1 as bigint)* relpages *8 ELSE 0 end)),'999G999G999G999G999G999G999'),
' | '||to_char(trunc(sum(case when relkind='i' THEN cast(1 as bigint)* relpages *8 ELSE 0 end)),'999G999G999G999G999G999G999'),
' | '||to_char(trunc(sum(case when relkind='t' THEN cast(1 as bigint)* relpages *8 ELSE 0 end)),'999G999G999G999G999G999G999'),
' | '||to_char(trunc(sum(cast(1 as bigint)* relpages *8)),'999G999G999G999G999G999G999')
from pg_class, pg_roles
where relowner=pg_roles.oid
group by rolname
order by rolname;
select ' |
| TOTAL',
' | '||to_char(sum(case when relkind='r' THEN 1 ELSE 0 end),'999G999G999G999G999G999G999'),
' | '||to_char(sum(case when relkind='r' THEN reltuples ELSE 0 end),'999G999G999G999G999G999G999'),
' | '||to_char(trunc(sum(case when relkind='r' THEN cast(1 as bigint)* relpages *8 ELSE 0 end)),'999G999G999G999G999G999G999'),
' | '||to_char(trunc(sum(case when relkind='i' THEN cast(1 as bigint)* relpages *8 ELSE 0 end)),'999G999G999G999G999G999G999'),
' | '||to_char(trunc(sum(case when relkind='t' THEN cast(1 as bigint)* relpages *8 ELSE 0 end)),'999G999G999G999G999G999G999'),
' | '||to_char(trunc(sum(cast(1 as bigint)* relpages *8)),'999G999G999G999G999G999G999')
from pg_class, pg_roles
where relowner=pg_roles.oid;
select ' |
| Internals |
' as info;
select '| Tables#',
' | Rows',
' | Relpages*8',
' | Total Size',
' | Main Fork',
' | Free Space Map',
' | Visibility Map',
' | Initialization Fork'
as info;
select
' |
| '||to_char(count(*),'999G999G999999G999G999G999') obj,
' | '||to_char(sum(reltuples),'999G999G999G999G999G999G999') as rowcount,
' | '||to_char(trunc(sum(relpages *8)),'999G999G999G999G999G999G999') relpages,
' | '||to_char(trunc(sum(pg_total_relation_size(oid))/1024),'999G999G999G999G999G999G999') total,
' | '||to_char(trunc(sum(pg_relation_size(oid, 'main'))/1024),'999G999G999G999G999G999G999') main,
' | '||to_char(trunc(sum(pg_relation_size(oid, 'fsm'))/1024),'999G999G999G999G999G999G999') fsm,
' | '||to_char(trunc(sum(pg_relation_size(oid, 'vm'))/1024),'999G999G999G999G999G999G999') vm,
' | '||to_char(trunc(sum(pg_relation_size(oid, 'init'))/1024),'999G999G999G999G999G999G999') init
from pg_class
where relkind='r';
select ' |
| Bloated tables (estimated size) |
' as info;
select '| Table',
' | Fillfactor',
' | Table Size',
' | HR Size',
' | Bloat',
' | HR Bloat',
' | Bloat%'
as info;
SELECT ' |
| '||schemaname||'.'||tblname, ' | '||fillfactor,
' | '||bs*tblpages AS real_size, ' | '||pg_size_pretty(bs*tblpages) as HR_size,
' | ', CASE WHEN tblpages - est_tblpages_ff > 0
THEN (tblpages-est_tblpages_ff)*bs
ELSE 0
END AS bloat_size,
' | ', CASE WHEN tblpages - est_tblpages_ff > 0
THEN pg_size_pretty( ((tblpages-est_tblpages_ff)*bs)::bigint)
ELSE '0'
END AS hr_bloat_size,
' | ', CASE WHEN tblpages > 0 AND tblpages - est_tblpages_ff > 0
THEN round(100*(tblpages - est_tblpages_ff)/tblpages::float)
ELSE 0
END, '%'
FROM (
SELECT ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
FROM (
SELECT
( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
- CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
- CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na
FROM (
SELECT
tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
coalesce(toast.reltuples, 0) AS toasttuples,
coalesce(substring(
array_to_string(tbl.reloptions, ' ')
FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
current_setting('block_size')::numeric AS bs,
CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
24 AS page_hdr,
23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END
+ CASE WHEN bool_or(att.attname = 'oid' and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size,
bool_or(att.atttypid = 'pg_catalog.name'::regtype)
OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na
FROM pg_attribute AS att
JOIN pg_class AS tbl ON att.attrelid = tbl.oid
JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
WHERE NOT att.attisdropped
AND tbl.relkind in ('r','m')
GROUP BY 1,2,3,4,5,6,7,8,9,10
ORDER BY 2,3
) AS s
) AS s2
) AS s3
where not is_na
and tblpages-est_tblpages_ff>0
ORDER BY 6 desc limit 20;
select ' |
| Bloated tables (estimated percentage) |
' as info;
select '| Table',
' | Fillfactor',
' | Table Size',
' | HR Size',
' | Bloat',
' | HR Bloat',
' | Bloat%'
as info;
SELECT ' |
| '||schemaname||'.'||tblname, ' | '||fillfactor,
' | '||bs*tblpages AS real_size, ' | '||pg_size_pretty(bs*tblpages) as HR_size,
' | ', CASE WHEN tblpages - est_tblpages_ff > 0
THEN (tblpages-est_tblpages_ff)*bs
ELSE 0
END AS bloat_size,
' | ', CASE WHEN tblpages - est_tblpages_ff > 0
THEN pg_size_pretty( ((tblpages-est_tblpages_ff)*bs)::bigint)
ELSE '0'
END AS hr_bloat_size,
' | ', CASE WHEN tblpages > 0 AND tblpages - est_tblpages_ff > 0
THEN round(100*(tblpages - est_tblpages_ff)/tblpages::float)
ELSE 0
END, '%'
FROM (
SELECT ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
FROM (
SELECT
( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
- CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
- CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na
FROM (
SELECT
tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
coalesce(toast.reltuples, 0) AS toasttuples,
coalesce(substring(
array_to_string(tbl.reloptions, ' ')
FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
current_setting('block_size')::numeric AS bs,
CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
24 AS page_hdr,
23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END
+ CASE WHEN bool_or(att.attname = 'oid' and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size,
bool_or(att.atttypid = 'pg_catalog.name'::regtype)
OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na
FROM pg_attribute AS att
JOIN pg_class AS tbl ON att.attrelid = tbl.oid
JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
WHERE NOT att.attisdropped
AND tbl.relkind in ('r','m')
GROUP BY 1,2,3,4,5,6,7,8,9,10
ORDER BY 2,3
) AS s
) AS s2
) AS s3
where not is_na
and tblpages-est_tblpages_ff>0
and tblpages>2
ORDER BY 10 desc limit 5;
select ' |
| Users with poor password |
' as info;
select '| Username',' | Password',
' | Note' ;
select ' |
| ',usename, ' | ', passwd, ' | Weak password'
from pg_shadow
where substr(passwd,4) in (
md5('postgres'||usename), md5('mypass'|| usename), md5('admin'|| usename), md5('secret'|| usename),
md5('root'|| usename), md5('password'|| usename), md5('public'|| usename), md5('private'|| usename),
md5('1234'|| usename), md5('secure'|| usename), md5('pass'|| usename), md5('qwerty'|| usename),
md5('pippo'|| usename), md5('manager'|| usename), md5('changeme'|| usename), md5('pwd'|| usename),
md5('changeme'|| usename), md5('xxx'|| usename), md5('toor'|| usename), md5('supervisor'|| usename))
order by usename;
select ' |
| ',usename, ' | ', passwd, ' | Same as user'
from pg_shadow
where substr(passwd,4) = md5(usename||usename)
order by usename;
select ' |
| ',usename, ' | ', passwd, ' | Empty password'
from pg_shadow
where passwd is null
order by usename;
select ' |
| ',usename, ' | ', passwd, ' | Unencrypted password'
from pg_shadow
where passwd not like 'md5%' and passwd not like 'SCRAM%'
order by usename;
select ' |
';
select '| Per-User Sessions | '
as info;
select '| User', ' | Database',
' | Count', ' | Active' ;
select ' | | ',usename,
' | ',datname,
' | ', count(*),
' | ', sum(case when state='active' then 1 else 0 end)
from pg_stat_activity
group by usename, datname
order by 6 desc, 1;
select ' | | TOTAL (', count(distinct usename),
' distinct users) | | '|| count(*)
from pg_stat_activity;
select ' |
' as info;
select ' | | Per-Host Sessions | '
as info;
select '| User', ' | Database',
' | Count', ' | Active' ;
select ' | | ', client_addr,
' | ',datname,
' | ', count(*),
' | ', sum(case when state='active' then 1 else 0 end)
from pg_stat_activity
group by client_addr, datname
order by 6 desc, 1
limit 20;
select ' | | TOTAL (', count(distinct client_addr),
' distinct clients) | | '|| count(*)
from pg_stat_activity;
select ' |
|
' as info;
select '' as info;
SELECT 'Buffer Cache size: ', pg_size_pretty(setting::bigint*8192::bigint)
FROM pg_settings
WHERE name='shared_buffers';
SELECT '
Estimated Minimal Buffer Cache size: ', pg_size_pretty(count(*) * 8192)
FROM pg_buffercache
WHERE usagecount >= 3;
select '
' as info;
SELECT cast(current_setting('server_version_num')as integer)>= 110000 as version_11p
\gset var_
SELECT cast(current_setting('server_version_num')as integer)>= 120000 as version_12p
\gset var_
SELECT cast(current_setting('server_version_num')as integer)>= 130000 as version_13p
\gset var_
SELECT cast(current_setting('server_version_num')as integer)>= 140000 as version_14p
\gset var_
SELECT cast(current_setting('server_version_num')as integer)>= 150000 as version_15p
\gset var_
SELECT cast(current_setting('server_version_num')as integer)>= 160000 as version_16p
\gset var_
select '
| Statement statistics' as info;
select ' |
| Query',
' | User',
' | Calls',
' | Average (sec.)',
' | Max (sec.)',
' | Total Time',
' | I/O Time',
' | Rows',
' | Hit Ratio%'
as info;
\if :var_version_13p
SELECT ' | WAL MB';
SELECT ' |
| '||replace(query,',',', '), ' | '||pg_get_userbyid(userid), ' | '||calls,
' | '||round((total_exec_time::numeric / calls::numeric)/1000,3),
' | '||round((max_exec_time::numeric::numeric)/1000,3),
' | '||round(total_exec_time), ' | '||round(blk_read_time+blk_write_time),
' | '||rows,
' | '||round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0)),2) AS hit_percent,
' | '||round((wal_bytes::numeric::numeric)/(1024*1024),0)
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;
\else
SELECT ' |
| '||replace(query,',',', '), ' | '||pg_get_userbyid(userid), ' | '||calls,
' | '||round((total_time::numeric / calls::numeric)/1000,3),
' | '||round((max_time::numeric::numeric)/1000,3),
' | '||round(total_time), ' | '||round(blk_read_time+blk_write_time),
' | '||rows,
' | '||round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0)),2) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 20;
\endif
select ' |
| Slowest Statements' as info;
select ' |
| Query',
' | User',
' | Calls',
' | Average (sec.)',
' | Total Time',
' | I/O Time',
' | Rows',
' | Hit Ratio%'
as info;
\if :var_version_13p
SELECT ' | WAL MB';
SELECT ' |
| '||replace(query,',',', '), ' | '||pg_get_userbyid(userid), ' | '||calls,
' | '||round((total_exec_time::numeric / calls::numeric)/1000,3),
' | '||round(total_exec_time), ' | '||round(blk_read_time+blk_write_time), ' | '||rows,
' | '||round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0)),2) AS hit_percent,
' | '||round((wal_bytes::numeric::numeric)/(1024*1024),0)
FROM pg_stat_statements
WHERE pg_get_userbyid(userid) not in ('enterprisedb') -- Comment if needed
ORDER BY (total_exec_time::numeric/calls::numeric) DESC
LIMIT 10;
\else
SELECT ' |
| '||replace(query,',',', '), ' | '||pg_get_userbyid(userid), ' | '||calls,
' | '||round((total_time::numeric / calls::numeric)/1000,3),
' | '||round(total_time), ' | '||round(blk_read_time+blk_write_time), ' | '||rows,
' | '||round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0)),2) AS hit_percent
FROM pg_stat_statements
WHERE pg_get_userbyid(userid) not in ('enterprisedb') -- Comment if needed
ORDER BY (total_time::numeric/calls::numeric) DESC
LIMIT 10;
\endif
select ' |
' as info;
select '| Database',
' | DBcpu',
' | IOcpu'
as info;
\if :var_version_13p
\if :var_version_14p
select ' |
| ', datname,
' | ', round(sum( (total_exec_time)/(EXTRACT(EPOCH FROM (now()-stats_reset))*1000) )::numeric,5) DBcpu,
' | ', round(sum( (blk_read_time+blk_write_time)/(EXTRACT(EPOCH FROM (now()-stats_reset))*1000) )::numeric,5) IOcpu
from pg_stat_statements, pg_database, pg_stat_statements_info
where pg_stat_statements.dbid=pg_database.oid
group by datname;
select ' |
| TOTAL | ', round(sum( (total_exec_time)/(EXTRACT(EPOCH FROM (now()-stats_reset))*1000) )::numeric,5) DBcpu,
' | ', round(sum( (blk_read_time+blk_write_time)/(EXTRACT(EPOCH FROM (now()-stats_reset))*1000) )::numeric,5) IOcpu
from pg_stat_statements, pg_stat_statements_info;
\else
select ' |
| ', datname,
' | ', round(sum( (total_exec_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) DBcpu,
' | ', round(sum( (blk_read_time+blk_write_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) IOcpu
from pg_stat_statements, pg_database
where pg_stat_statements.dbid=pg_database.oid
group by datname;
select ' |
| TOTAL | ', round(sum( (total_exec_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) DBcpu,
' | ', round(sum( (blk_read_time+blk_write_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) IOcpu
from pg_stat_statements;
\endif
\else
select ' |
| ', datname,
' | ', round(sum( (total_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) DBcpu,
' | ', round(sum( (blk_read_time+blk_write_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) IOcpu
from pg_stat_statements, pg_database
where pg_stat_statements.dbid=pg_database.oid
group by datname;
select ' |
| TOTAL | ', round(sum( (total_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) DBcpu,
' | ', round(sum( (blk_read_time+blk_write_time)/(EXTRACT(EPOCH FROM (now()-pg_postmaster_start_time()))*1000) )::numeric,5) IOcpu
from pg_stat_statements;
\endif
select ' |
| Tables statistics' as info;
select ' |
| Schema | Table',
' | Seq. Readed Tuples',
' | Idx. Readed Tuples',
' | Sequential Scan',
' | Index Scan',
' | Insert',
' | Update',
' | Hot Update',
' | Delete',
' | Index Usage Ratio%',
' | HOT Update Ratio%';
select ' |
| '||schemaname,
' | '||relname,
' | '||coalesce(seq_tup_read, 0),
' | '||coalesce(idx_tup_fetch, 0),
' | '||coalesce(seq_scan, 0),
' | '||coalesce(idx_scan, 0),
' | '||coalesce(n_tup_ins, 0),
' | '||coalesce(n_tup_upd, 0),
' | '||coalesce(n_tup_hot_upd, 0),
' | '||coalesce(n_tup_del, 0),
' | '||coalesce(idx_scan*100/nullif(idx_scan+seq_scan,0), -1) as idx_hit_ratio,
' | '||coalesce(n_tup_hot_upd*100/nullif(n_tup_upd,0), -1) as hot_hit_ratio
from pg_stat_user_tables
order by (coalesce(seq_tup_read,0) +coalesce(idx_tup_fetch,0) +coalesce(n_tup_ins,0) +
coalesce(n_tup_upd,0) +coalesce(n_tup_del,0)) desc
limit 20;
select ' |
| Tables caching' as info;
select ' |
| Schema | Table',
' | Heap Reads',
' | Index Reads',
' | TOAST Reads',
' | Heap Hit Ratio%',
' | Index Hit Ratio%',
' | TOAST Hit Ratio%';
select ' |
| '||schemaname,
' | '||relname,
' | '||coalesce(heap_blks_read, 0),
' | '||coalesce(idx_blks_read, 0),
' | '||coalesce(toast_blks_read, 0),
' | '||coalesce(heap_blks_hit*100/nullif(heap_blks_read+heap_blks_hit,0), -1) as tb_hit_ratio,
' | '||coalesce(idx_blks_hit*100/nullif(idx_blks_read+idx_blks_hit,0), -1) as idx_hit_ratio,
' | '||coalesce(toast_blks_hit*100/nullif(toast_blks_read+toast_blks_hit,0), -1) as toast_hit_ratio
from pg_statio_user_tables
where heap_blks_read>0
order by heap_blks_read desc
limit 20;
select ' |
| Missing indexes' as info;
select ' |
| Schema | Relation', ' | Contraint | Issue',
' | #Table Scan | Parent', ' | Columns';
WITH fk_actions ( code, action ) AS (
VALUES ( 'a', 'error' ),
( 'r', 'restrict' ),
( 'c', 'cascade' ),
( 'n', 'set null' ),
( 'd', 'set default' )
),
fk_list AS (
SELECT pg_constraint.oid as fkoid, conrelid, confrelid as parentid,
conname, relname, nspname,
fk_actions_update.action as update_action,
fk_actions_delete.action as delete_action,
conkey as key_cols
FROM pg_constraint
JOIN pg_class ON conrelid = pg_class.oid
JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
JOIN fk_actions AS fk_actions_update ON confupdtype = fk_actions_update.code
JOIN fk_actions AS fk_actions_delete ON confdeltype = fk_actions_delete.code
WHERE contype = 'f'
),
fk_attributes AS (
SELECT fkoid, conrelid, attname, attnum
FROM fk_list
JOIN pg_attribute
ON conrelid = attrelid
AND attnum = ANY( key_cols )
ORDER BY fkoid, attnum
),
fk_cols_list AS (
SELECT fkoid, array_agg(attname) as cols_list
FROM fk_attributes
GROUP BY fkoid
),
index_list AS (
SELECT indexrelid as indexid,
pg_class.relname as indexname,
indrelid,
indkey,
indpred is not null as has_predicate,
pg_get_indexdef(indexrelid) as indexdef
FROM pg_index
JOIN pg_class ON indexrelid = pg_class.oid
WHERE indisvalid
),
fk_index_match AS (
SELECT fk_list.*,
indexid,
indexname,
indkey::int[] as indexatts,
has_predicate,
indexdef,
array_length(key_cols, 1) as fk_colcount,
array_length(indkey,1) as index_colcount,
round(pg_relation_size(conrelid)/(1024^2)::numeric) as table_mb,
cols_list
FROM fk_list
JOIN fk_cols_list USING (fkoid)
LEFT OUTER JOIN index_list
ON conrelid = indrelid
AND (indkey::int2[])[0:(array_length(key_cols,1) -1)] @> key_cols
),
fk_perfect_match AS (
SELECT fkoid
FROM fk_index_match
WHERE (index_colcount - 1) <= fk_colcount
AND NOT has_predicate
AND indexdef LIKE '%USING btree%'
),
fk_index_check AS (
SELECT 'no index' as issue, *, 1 as issue_sort
FROM fk_index_match
WHERE indexid IS NULL
UNION ALL
SELECT 'questionable index' as issue, *, 2
FROM fk_index_match
WHERE indexid IS NOT NULL
AND fkoid NOT IN (
SELECT fkoid
FROM fk_perfect_match)
),
parent_table_stats AS (
SELECT fkoid, tabstats.relname as parent_name,
(n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as parent_writes,
round(pg_relation_size(parentid)/(1024^2)::numeric) as parent_mb
FROM pg_stat_user_tables AS tabstats
JOIN fk_list
ON relid = parentid
),
fk_table_stats AS (
SELECT fkoid,
(n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as writes,
seq_scan as table_scans
FROM pg_stat_user_tables AS tabstats
JOIN fk_list
ON relid = conrelid
)
SELECT ' |
| ', nspname as schema_name,
' | ', relname as table_name,
' | ', conname as fk_name,
' | ', issue,
' | ', table_scans,
' | ', parent_name,
' | ', cols_list
FROM fk_index_check
JOIN parent_table_stats USING (fkoid)
JOIN fk_table_stats USING (fkoid)
WHERE table_mb > 5
AND ( writes > 1000
OR parent_writes > 1000
OR parent_mb > 10 )
ORDER BY table_scans DESC, table_mb DESC, table_name, fk_name
LIMIT 64;
select ' |
' as info;
select '| Tuning Parameters |
'
as info;
select '| Parameter',
' | Value',
' | Min',
' | Max',
' | Unit',
' | Description'
as info;
select ' |
| ',name,' | ',replace(replace(setting,'<','<'),'>','>'),
' | ',min_val,' | ',max_val,
' | ',unit, ' | ',short_desc
from pg_settings
where name in ('max_connections','shared_buffers','effective_cache_size','work_mem', 'wal_buffers',
'checkpoint_completion_target', 'checkpoint_segments', 'synchronous_commit', 'wal_writer_delay',
'max_fsm_pages','fsync','commit_delay','commit_siblings','random_page_cost',
'checkpoint_timeout', 'max_wal_size',
'bgwriter_lru_maxpages', 'bgwriter_lru_multiplier', ' bgwriter_delay',
'autovacuum_vacuum_cost_limit', 'autovacuum_vacuum_cost_delay')
order by name;
select ' |
' as info;
SELECT
EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_stat_statements' and installed_version is not null) as pg_stat_statements,
EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_buffercache' and installed_version is not null) as pg_buffercache,
EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pgstattuple' and installed_version is not null) as pgstattuple,
EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_freespacemap' and installed_version is not null) as pg_freespacemap,
EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='sslinfo' and installed_version is not null) as sslinfo,
EXISTS (SELECT 1 FROM pg_stat_ssl WHERE ssl limit 1) as ssl_active,
EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pgaudit' and installed_version is not null) as pgaudit,
EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pgrowlocks' and installed_version is not null) as pgrowlocks,
EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='postgis' and installed_version is not null) as postgis,
EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='edbspl') as edb,
EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='aurora_stat_utils') as aurora,
EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='aurora_stat_utils') as aurora_stat,
EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='apg_plan_mgmt') as qpm
\gset opt_
-- pg_stat_statements are pg_buffercache are too important to be "optional"
\if :opt_pgstattuple
select '