summaryrefslogtreecommitdiffstats
path: root/src/go/collectors/go.d.plugin/modules/postgres/queries.go
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--src/go/collectors/go.d.plugin/modules/postgres/queries.go757
1 files changed, 757 insertions, 0 deletions
diff --git a/src/go/collectors/go.d.plugin/modules/postgres/queries.go b/src/go/collectors/go.d.plugin/modules/postgres/queries.go
new file mode 100644
index 000000000..f6afc9342
--- /dev/null
+++ b/src/go/collectors/go.d.plugin/modules/postgres/queries.go
@@ -0,0 +1,757 @@
+// SPDX-License-Identifier: GPL-3.0-or-later
+
+package postgres
+
+func queryServerVersion() string {
+ return "SHOW server_version_num;"
+}
+
+func queryIsSuperUser() string {
+ return "SELECT current_setting('is_superuser') = 'on' AS is_superuser;"
+}
+
+func queryPGIsInRecovery() string {
+ return "SELECT pg_is_in_recovery();"
+}
+
+func querySettingsMaxConnections() string {
+ return "SELECT current_setting('max_connections')::INT - current_setting('superuser_reserved_connections')::INT;"
+}
+
+func querySettingsMaxLocksHeld() string {
+ return `
+SELECT current_setting('max_locks_per_transaction')::INT *
+ (current_setting('max_connections')::INT + current_setting('max_prepared_transactions')::INT);
+`
+}
+
+// TODO: this is not correct and we should use pg_stat_activity.
+// But we need to check what connections (backend_type) count towards 'max_connections'.
+// I think python version query doesn't count it correctly.
+// https://github.com/netdata/netdata/blob/1782e2d002bc5203128e5a5d2b801010e2822d2d/collectors/python.d.plugin/postgres/postgres.chart.py#L266
+func queryServerCurrentConnectionsUsed() string {
+ return "SELECT sum(numbackends) FROM pg_stat_database;"
+}
+
+func queryServerConnectionsState() string {
+ return `
+SELECT state,
+ COUNT(*)
+FROM pg_stat_activity
+WHERE state IN
+ (
+ 'active',
+ 'idle',
+ 'idle in transaction',
+ 'idle in transaction (aborted)',
+ 'fastpath function call',
+ 'disabled'
+ )
+GROUP BY state;
+`
+}
+
+func queryCheckpoints() string {
+ // definition by version: https://pgpedia.info/p/pg_stat_bgwriter.html
+ // docs: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-BGWRITER-VIEW
+ // code: https://github.com/postgres/postgres/blob/366283961ac0ed6d89014444c6090f3fd02fce0a/src/backend/catalog/system_views.sql#L1104
+
+ return `
+SELECT checkpoints_timed,
+ checkpoints_req,
+ checkpoint_write_time,
+ checkpoint_sync_time,
+ buffers_checkpoint * current_setting('block_size')::numeric AS buffers_checkpoint_bytes,
+ buffers_clean * current_setting('block_size')::numeric AS buffers_clean_bytes,
+ maxwritten_clean,
+ buffers_backend * current_setting('block_size')::numeric AS buffers_backend_bytes,
+ buffers_backend_fsync,
+ buffers_alloc * current_setting('block_size')::numeric AS buffers_alloc_bytes
+FROM pg_stat_bgwriter;
+`
+}
+
+func queryServerUptime() string {
+ return `SELECT EXTRACT(epoch FROM CURRENT_TIMESTAMP - pg_postmaster_start_time());`
+}
+
+func queryTXIDWraparound() string {
+ // https://www.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql
+ return `
+ WITH max_age AS ( SELECT
+ 2000000000 as max_old_xid,
+ setting AS autovacuum_freeze_max_age
+ FROM
+ pg_catalog.pg_settings
+ WHERE
+ name = 'autovacuum_freeze_max_age'), per_database_stats AS ( SELECT
+ datname ,
+ m.max_old_xid::int ,
+ m.autovacuum_freeze_max_age::int ,
+ age(d.datfrozenxid) AS oldest_current_xid
+ FROM
+ pg_catalog.pg_database d
+ JOIN
+ max_age m
+ ON (true)
+ WHERE
+ d.datallowconn) SELECT
+ max(oldest_current_xid) AS oldest_current_xid ,
+ max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound ,
+ max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovacuum
+ FROM
+ per_database_stats;
+`
+}
+
+func queryWALWrites(version int) string {
+ if version < pgVersion10 {
+ return `
+SELECT
+ pg_xlog_location_diff(
+ CASE
+ pg_is_in_recovery()
+ WHEN
+ TRUE
+ THEN
+ pg_last_xlog_receive_location()
+ ELSE
+ pg_current_xlog_location()
+ END
+, '0/0') AS wal_writes ;
+`
+ }
+ return `
+SELECT
+ pg_wal_lsn_diff(
+ CASE
+ pg_is_in_recovery()
+ WHEN
+ TRUE
+ THEN
+ pg_last_wal_receive_lsn()
+ ELSE
+ pg_current_wal_lsn()
+ END
+, '0/0') AS wal_writes ;
+`
+}
+
+func queryWALFiles(version int) string {
+ if version < pgVersion10 {
+ return `
+SELECT count(*) FILTER (WHERE type = 'recycled') AS wal_recycled_files,
+ count(*) FILTER (WHERE type = 'written') AS wal_written_files
+FROM (SELECT wal.name,
+ pg_xlogfile_name(
+ CASE pg_is_in_recovery()
+ WHEN true THEN NULL
+ ELSE pg_current_xlog_location()
+ END),
+ CASE
+ WHEN wal.name > pg_xlogfile_name(
+ CASE pg_is_in_recovery()
+ WHEN true THEN NULL
+ ELSE pg_current_xlog_location()
+ END) THEN 'recycled'
+ ELSE 'written'
+ END AS type
+ FROM pg_catalog.pg_ls_dir('pg_xlog') AS wal(name)
+ WHERE name ~ '^[0-9A-F]{24}$'
+ ORDER BY (pg_stat_file('pg_xlog/' || name, true)).modification,
+ wal.name DESC) sub;
+`
+ }
+ return `
+SELECT count(*) FILTER (WHERE type = 'recycled') AS wal_recycled_files,
+ count(*) FILTER (WHERE type = 'written') AS wal_written_files
+FROM (SELECT wal.name,
+ pg_walfile_name(
+ CASE pg_is_in_recovery()
+ WHEN true THEN NULL
+ ELSE pg_current_wal_lsn()
+ END),
+ CASE
+ WHEN wal.name > pg_walfile_name(
+ CASE pg_is_in_recovery()
+ WHEN true THEN NULL
+ ELSE pg_current_wal_lsn()
+ END) THEN 'recycled'
+ ELSE 'written'
+ END AS type
+ FROM pg_catalog.pg_ls_dir('pg_wal') AS wal(name)
+ WHERE name ~ '^[0-9A-F]{24}$'
+ ORDER BY (pg_stat_file('pg_wal/' || name, true)).modification,
+ wal.name DESC) sub;
+`
+}
+
+func queryWALArchiveFiles(version int) string {
+ if version < pgVersion10 {
+ return `
+ SELECT
+ CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.ready$$r$ as INT)),
+ 0) AS INT) AS wal_archive_files_ready_count,
+ CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.done$$r$ AS INT)),
+ 0) AS INT) AS wal_archive_files_done_count
+ FROM
+ pg_catalog.pg_ls_dir('pg_xlog/archive_status') AS archive_files (archive_file);
+`
+ }
+ return `
+ SELECT
+ CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.ready$$r$ as INT)),
+ 0) AS INT) AS wal_archive_files_ready_count,
+ CAST(COALESCE(SUM(CAST(archive_file ~ $r$\.done$$r$ AS INT)),
+ 0) AS INT) AS wal_archive_files_done_count
+ FROM
+ pg_catalog.pg_ls_dir('pg_wal/archive_status') AS archive_files (archive_file);
+`
+}
+
+func queryCatalogRelations() string {
+ // kind of same as
+ // https://github.com/netdata/netdata/blob/750810e1798e09cc6210e83594eb9ed4905f8f12/collectors/python.d.plugin/postgres/postgres.chart.py#L336-L354
+ // TODO: do we need that? It is optional and disabled by default in py version.
+ return `
+SELECT relkind,
+ COUNT(1),
+ SUM(relpages) * current_setting('block_size')::NUMERIC AS size
+FROM pg_class
+GROUP BY relkind;
+`
+}
+
+func queryAutovacuumWorkers() string {
+ // https://github.com/postgres/postgres/blob/9e4f914b5eba3f49ab99bdecdc4f96fac099571f/src/backend/postmaster/autovacuum.c#L3168-L3183
+ return `
+SELECT count(*) FILTER (
+ WHERE
+ query LIKE 'autovacuum: ANALYZE%%'
+ AND query NOT LIKE '%%to prevent wraparound%%'
+ ) AS autovacuum_analyze,
+ count(*) FILTER (
+ WHERE
+ query LIKE 'autovacuum: VACUUM ANALYZE%%'
+ AND query NOT LIKE '%%to prevent wraparound%%'
+ ) AS autovacuum_vacuum_analyze,
+ count(*) FILTER (
+ WHERE
+ query LIKE 'autovacuum: VACUUM %.%%'
+ AND query NOT LIKE '%%to prevent wraparound%%'
+ ) AS autovacuum_vacuum,
+ count(*) FILTER (
+ WHERE
+ query LIKE '%%to prevent wraparound%%'
+ ) AS autovacuum_vacuum_freeze,
+ count(*) FILTER (
+ WHERE
+ query LIKE 'autovacuum: BRIN summarize%%'
+ ) AS autovacuum_brin_summarize
+FROM pg_stat_activity
+WHERE query NOT LIKE '%%pg_stat_activity%%';
+`
+}
+
+func queryXactQueryRunningTime() string {
+ return `
+SELECT datname,
+ state,
+ EXTRACT(epoch from now() - xact_start) as xact_running_time,
+ EXTRACT(epoch from now() - query_start) as query_running_time
+FROM pg_stat_activity
+WHERE datname IS NOT NULL
+ AND state IN
+ (
+ 'active',
+ 'idle in transaction',
+ 'idle in transaction (aborted)'
+ )
+ AND backend_type = 'client backend';
+`
+}
+
+func queryReplicationStandbyAppDelta(version int) string {
+ if version < pgVersion10 {
+ return `
+SELECT application_name,
+ pg_xlog_location_diff(
+ CASE pg_is_in_recovery()
+ WHEN true THEN pg_last_xlog_receive_location()
+ ELSE pg_current_xlog_location()
+ END,
+ sent_location) AS sent_delta,
+ pg_xlog_location_diff(
+ sent_location, write_location) AS write_delta,
+ pg_xlog_location_diff(
+ write_location, flush_location) AS flush_delta,
+ pg_xlog_location_diff(
+ flush_location, replay_location) AS replay_delta
+FROM pg_stat_replication psr
+WHERE application_name IS NOT NULL;
+`
+ }
+ return `
+SELECT application_name,
+ pg_wal_lsn_diff(
+ CASE pg_is_in_recovery()
+ WHEN true THEN pg_last_wal_receive_lsn()
+ ELSE pg_current_wal_lsn()
+ END,
+ sent_lsn) AS sent_delta,
+ pg_wal_lsn_diff(
+ sent_lsn, write_lsn) AS write_delta,
+ pg_wal_lsn_diff(
+ write_lsn, flush_lsn) AS flush_delta,
+ pg_wal_lsn_diff(
+ flush_lsn, replay_lsn) AS replay_delta
+FROM pg_stat_replication
+WHERE application_name IS NOT NULL;
+`
+}
+
+func queryReplicationStandbyAppLag() string {
+ return `
+SELECT application_name,
+ COALESCE(EXTRACT(EPOCH FROM write_lag)::bigint, 0) AS write_lag,
+ COALESCE(EXTRACT(EPOCH FROM flush_lag)::bigint, 0) AS flush_lag,
+ COALESCE(EXTRACT(EPOCH FROM replay_lag)::bigint, 0) AS replay_lag
+FROM pg_stat_replication psr
+WHERE application_name IS NOT NULL;
+`
+}
+
+func queryReplicationSlotFiles(version int) string {
+ if version < pgVersion11 {
+ return `
+WITH wal_size AS (
+ SELECT
+ current_setting('wal_block_size')::INT * setting::INT AS val
+ FROM pg_settings
+ WHERE name = 'wal_segment_size'
+ )
+SELECT
+ slot_name,
+ slot_type,
+ replslot_wal_keep,
+ count(slot_file) AS replslot_files
+FROM
+ (SELECT
+ slot.slot_name,
+ CASE
+ WHEN slot_file <> 'state' THEN 1
+ END AS slot_file ,
+ slot_type,
+ COALESCE (
+ floor(
+ CASE WHEN pg_is_in_recovery()
+ THEN (
+ pg_wal_lsn_diff(pg_last_wal_receive_lsn(), slot.restart_lsn)
+ -- this is needed to account for whole WAL retention and
+ -- not only size retention
+ + (pg_wal_lsn_diff(restart_lsn, '0/0') % s.val)
+ ) / s.val
+ ELSE (
+ pg_wal_lsn_diff(pg_current_wal_lsn(), slot.restart_lsn)
+ -- this is needed to account for whole WAL retention and
+ -- not only size retention
+ + (pg_walfile_name_offset(restart_lsn)).file_offset
+ ) / s.val
+ END
+ ),0) AS replslot_wal_keep
+ FROM pg_replication_slots slot
+ LEFT JOIN (
+ SELECT
+ slot2.slot_name,
+ pg_ls_dir('pg_replslot/' || slot2.slot_name) AS slot_file
+ FROM pg_replication_slots slot2
+ ) files (slot_name, slot_file)
+ ON slot.slot_name = files.slot_name
+ CROSS JOIN wal_size s
+ ) AS d
+GROUP BY
+ slot_name,
+ slot_type,
+ replslot_wal_keep;
+`
+ }
+
+ return `
+WITH wal_size AS (
+ SELECT
+ setting::int AS val
+ FROM pg_settings
+ WHERE name = 'wal_segment_size'
+ )
+SELECT
+ slot_name,
+ slot_type,
+ replslot_wal_keep,
+ count(slot_file) AS replslot_files
+FROM
+ (SELECT
+ slot.slot_name,
+ CASE
+ WHEN slot_file <> 'state' THEN 1
+ END AS slot_file ,
+ slot_type,
+ COALESCE (
+ floor(
+ CASE WHEN pg_is_in_recovery()
+ THEN (
+ pg_wal_lsn_diff(pg_last_wal_receive_lsn(), slot.restart_lsn)
+ -- this is needed to account for whole WAL retention and
+ -- not only size retention
+ + (pg_wal_lsn_diff(restart_lsn, '0/0') % s.val)
+ ) / s.val
+ ELSE (
+ pg_wal_lsn_diff(pg_current_wal_lsn(), slot.restart_lsn)
+ -- this is needed to account for whole WAL retention and
+ -- not only size retention
+ + (pg_walfile_name_offset(restart_lsn)).file_offset
+ ) / s.val
+ END
+ ),0) AS replslot_wal_keep
+ FROM pg_replication_slots slot
+ LEFT JOIN (
+ SELECT
+ slot2.slot_name,
+ pg_ls_dir('pg_replslot/' || slot2.slot_name) AS slot_file
+ FROM pg_replication_slots slot2
+ ) files (slot_name, slot_file)
+ ON slot.slot_name = files.slot_name
+ CROSS JOIN wal_size s
+ ) AS d
+GROUP BY
+ slot_name,
+ slot_type,
+ replslot_wal_keep;
+`
+}
+
+func queryQueryableDatabaseList() string {
+ return `
+SELECT datname
+FROM pg_database
+WHERE datallowconn = true
+ AND datistemplate = false
+ AND datname != current_database()
+ AND has_database_privilege((SELECT CURRENT_USER), datname, 'connect');
+`
+}
+
+func queryDatabaseStats() string {
+ // definition by version: https://pgpedia.info/p/pg_stat_database.html
+ // docs: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW
+ // code: https://github.com/postgres/postgres/blob/366283961ac0ed6d89014444c6090f3fd02fce0a/src/backend/catalog/system_views.sql#L1018
+
+ return `
+SELECT stat.datname,
+ numbackends,
+ pg_database.datconnlimit,
+ xact_commit,
+ xact_rollback,
+ blks_read * current_setting('block_size')::numeric AS blks_read_bytes,
+ blks_hit * current_setting('block_size')::numeric AS blks_hit_bytes,
+ tup_returned,
+ tup_fetched,
+ tup_inserted,
+ tup_updated,
+ tup_deleted,
+ conflicts,
+ temp_files,
+ temp_bytes,
+ deadlocks
+FROM pg_stat_database stat
+ INNER JOIN
+ pg_database
+ ON pg_database.datname = stat.datname
+WHERE pg_database.datistemplate = false;
+`
+}
+
+func queryDatabaseSize(version int) string {
+ if version < pgVersion10 {
+ return `
+SELECT datname,
+ pg_database_size(datname) AS size
+FROM pg_database
+WHERE pg_database.datistemplate = false
+ AND has_database_privilege((SELECT CURRENT_USER), pg_database.datname, 'connect');
+`
+ }
+ return `
+SELECT datname,
+ pg_database_size(datname) AS size
+FROM pg_database
+WHERE pg_database.datistemplate = false
+ AND (has_database_privilege((SELECT CURRENT_USER), datname, 'connect')
+ OR pg_has_role((SELECT CURRENT_USER), 'pg_read_all_stats', 'MEMBER'));
+`
+}
+
+func queryDatabaseConflicts() string {
+ // definition by version: https://pgpedia.info/p/pg_stat_database_conflicts.html
+ // docs: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-CONFLICTS-VIEW
+ // code: https://github.com/postgres/postgres/blob/366283961ac0ed6d89014444c6090f3fd02fce0a/src/backend/catalog/system_views.sql#L1058
+
+ return `
+SELECT stat.datname,
+ confl_tablespace,
+ confl_lock,
+ confl_snapshot,
+ confl_bufferpin,
+ confl_deadlock
+FROM pg_stat_database_conflicts stat
+ INNER JOIN
+ pg_database
+ ON pg_database.datname = stat.datname
+WHERE pg_database.datistemplate = false;
+`
+}
+
+func queryDatabaseLocks() string {
+ // definition by version: https://pgpedia.info/p/pg_locks.html
+ // docs: https://www.postgresql.org/docs/current/view-pg-locks.html
+
+ return `
+SELECT pg_database.datname,
+ mode,
+ granted,
+ count(mode) AS locks_count
+FROM pg_locks
+ INNER JOIN
+ pg_database
+ ON pg_database.oid = pg_locks.database
+WHERE pg_database.datistemplate = false
+GROUP BY datname,
+ mode,
+ granted
+ORDER BY datname,
+ mode;
+`
+}
+
+func queryUserTablesCount() string {
+ return "SELECT count(*) from pg_stat_user_tables;"
+}
+
+func queryStatUserTables() string {
+ return `
+SELECT current_database() as datname,
+ schemaname,
+ relname,
+ inh.parent_relname,
+ seq_scan,
+ seq_tup_read,
+ idx_scan,
+ idx_tup_fetch,
+ n_tup_ins,
+ n_tup_upd,
+ n_tup_del,
+ n_tup_hot_upd,
+ n_live_tup,
+ n_dead_tup,
+ EXTRACT(epoch from now() - last_vacuum) as last_vacuum,
+ EXTRACT(epoch from now() - last_autovacuum) as last_autovacuum,
+ EXTRACT(epoch from now() - last_analyze) as last_analyze,
+ EXTRACT(epoch from now() - last_autoanalyze) as last_autoanalyze,
+ vacuum_count,
+ autovacuum_count,
+ analyze_count,
+ autoanalyze_count,
+ pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(relname)) as total_relation_size
+FROM pg_stat_user_tables
+LEFT JOIN(
+ SELECT
+ c.oid AS child_oid,
+ p.relname AS parent_relname
+ FROM
+ pg_inherits
+ JOIN pg_class AS c ON (inhrelid = c.oid)
+ JOIN pg_class AS p ON (inhparent = p.oid)
+ ) AS inh ON inh.child_oid = relid
+WHERE has_schema_privilege(schemaname, 'USAGE');
+`
+}
+
+func queryStatIOUserTables() string {
+ return `
+SELECT current_database() AS datname,
+ schemaname,
+ relname,
+ inh.parent_relname,
+ heap_blks_read * current_setting('block_size')::numeric AS heap_blks_read_bytes,
+ heap_blks_hit * current_setting('block_size')::numeric AS heap_blks_hit_bytes,
+ idx_blks_read * current_setting('block_size')::numeric AS idx_blks_read_bytes,
+ idx_blks_hit * current_setting('block_size')::numeric AS idx_blks_hit_bytes,
+ toast_blks_read * current_setting('block_size')::numeric AS toast_blks_read_bytes,
+ toast_blks_hit * current_setting('block_size')::numeric AS toast_blks_hit_bytes,
+ tidx_blks_read * current_setting('block_size')::numeric AS tidx_blks_read_bytes,
+ tidx_blks_hit * current_setting('block_size')::numeric AS tidx_blks_hit_bytes
+FROM pg_statio_user_tables
+LEFT JOIN(
+ SELECT
+ c.oid AS child_oid,
+ p.relname AS parent_relname
+ FROM
+ pg_inherits
+ JOIN pg_class AS c ON (inhrelid = c.oid)
+ JOIN pg_class AS p ON (inhparent = p.oid)
+ ) AS inh ON inh.child_oid = relid
+WHERE has_schema_privilege(schemaname, 'USAGE');
+`
+}
+
+func queryUserIndexesCount() string {
+ return "SELECT count(*) from pg_stat_user_indexes;"
+}
+
+func queryStatUserIndexes() string {
+ return `
+SELECT current_database() as datname,
+ schemaname,
+ relname,
+ indexrelname,
+ inh.parent_relname,
+ idx_scan,
+ idx_tup_read,
+ idx_tup_fetch,
+ pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(indexrelname)::text) as size
+FROM pg_stat_user_indexes
+LEFT JOIN(
+ SELECT
+ c.oid AS child_oid,
+ p.relname AS parent_relname
+ FROM
+ pg_inherits
+ JOIN pg_class AS c ON (inhrelid = c.oid)
+ JOIN pg_class AS p ON (inhparent = p.oid)
+ ) AS inh ON inh.child_oid = relid
+WHERE has_schema_privilege(schemaname, 'USAGE');
+`
+}
+
+// The following query for bloat was taken from the venerable check_postgres
+// script (https://bucardo.org/check_postgres/), which is:
+//
+// Copyright (c) 2007-2017 Greg Sabino Mullane
+//------------------------------------------------------------------------------
+
+func queryBloat() string {
+ return `
+SELECT
+ current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
+ ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
+ CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
+ CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
+ CASE WHEN relpages < otta THEN '0 bytes'::text ELSE (bs*(relpages-otta))::bigint::text || ' bytes' END AS wastedsize,
+ iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
+ ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
+ CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
+ CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
+ CASE WHEN ipages < iotta THEN '0 bytes' ELSE (bs*(ipages-iotta))::bigint::text || ' bytes' END AS wastedisize,
+ CASE WHEN relpages < otta THEN
+ CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
+ ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
+ ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
+ END AS totalwastedbytes
+FROM (
+ SELECT
+ nn.nspname AS schemaname,
+ cc.relname AS tablename,
+ COALESCE(cc.reltuples,0) AS reltuples,
+ COALESCE(cc.relpages,0) AS relpages,
+ COALESCE(bs,0) AS bs,
+ COALESCE(CEIL((cc.reltuples*((datahdr+ma-
+ (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
+ COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
+ COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
+ FROM
+ pg_class cc
+ JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'
+ LEFT JOIN
+ (
+ SELECT
+ ma,bs,foo.nspname,foo.relname,
+ (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
+ (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
+ FROM (
+ SELECT
+ ns.nspname, tbl.relname, hdr, ma, bs,
+ SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
+ MAX(coalesce(null_frac,0)) AS maxfracsum,
+ hdr+(
+ SELECT 1+count(*)/8
+ FROM pg_stats s2
+ WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
+ ) AS nullhdr
+ FROM pg_attribute att
+ JOIN pg_class tbl ON att.attrelid = tbl.oid
+ JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
+ LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
+ AND s.tablename = tbl.relname
+ AND s.inherited=false
+ AND s.attname=att.attname,
+ (
+ SELECT
+ (SELECT current_setting('block_size')::numeric) AS bs,
+ CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')
+ IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
+ CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma
+ FROM (SELECT version() AS v) AS foo
+ ) AS constants
+ WHERE att.attnum > 0 AND tbl.relkind='r'
+ GROUP BY 1,2,3,4,5
+ ) AS foo
+ ) AS rs
+ ON cc.relname = rs.relname AND nn.nspname = rs.nspname
+ LEFT JOIN pg_index i ON indrelid = cc.oid
+ LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
+) AS sml
+WHERE sml.relpages - otta > 10 OR ipages - iotta > 10;
+`
+}
+
+func queryColumnsStats() string {
+ return `
+SELECT current_database() AS datname,
+ nspname AS schemaname,
+ relname,
+ st.attname,
+ typname,
+ (st.null_frac * 100)::int AS null_percent,
+ case
+ when st.n_distinct >= 0
+ then st.n_distinct
+ else
+ abs(st.n_distinct) * reltuples
+ end AS "distinct"
+FROM pg_class c
+ JOIN
+ pg_namespace ns
+ ON
+ (ns.oid = relnamespace)
+ JOIN
+ pg_attribute at
+ ON
+ (c.oid = attrelid)
+ JOIN
+ pg_type t
+ ON
+ (t.oid = atttypid)
+ JOIN
+ pg_stats st
+ ON
+ (st.tablename = relname AND st.attname = at.attname)
+WHERE relkind = 'r'
+ AND nspname NOT LIKE E'pg\\_%'
+ AND nspname != 'information_schema'
+ AND NOT attisdropped
+ AND attstattarget != 0
+ AND reltuples >= 100
+ORDER BY nspname,
+ relname,
+ st.attname;
+`
+}
generated by cgit v1.2.3 (git 2.39.1) at 2024-07-16 22:43:37 +0000