WITH table_data AS (
SELECT c.oid,
nspname AS table_schema,
relname AS table_name,
c.reltuples::BIGINT AS rows,
pg_total_relation_size(c.oid) AS total_bytes,
pg_indexes_size(c.oid) AS index_bytes,
pg_total_relation_size(reltoastrelid) AS toast_bytes,
pg_total_relation_size(c.oid)
- pg_indexes_size(c.oid)
- COALESCE(pg_total_relation_size(
reltoastrelid), 0) AS table_bytes
FROM pg_class c
LEFT JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE relkind = 'r'
)
SELECT table_name,
rows,
pg_size_pretty(total_bytes) AS total_size,
pg_size_pretty(index_bytes) AS index_size,
pg_size_pretty(toast_bytes) AS toast_size,
pg_size_pretty(table_bytes) AS table_size,
pg_size_pretty(total_bytes / rows) AS total_per_row,
pg_size_pretty(index_bytes / rows) AS index_per_row,
pg_size_pretty(table_bytes / rows) AS table_per_row
FROM table_data
WHERE table_schema = 'public' AND rows > 0
ORDER BY total_bytes DESC;
SELECT
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
CASE WHEN indisunique THEN 'Y'
ELSE 'N'
END AS UNIQUE,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid
JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY pg_relation_size(quote_ident(indexrelname)::text) desc;
select * from pg_stat_user_indexes order by indexrelname;
Note that it is possible to examine the number of index scans (including index-only scans and bitmap index scans) by examining pg_stat_user_indexes.idx_scan. If your covering index isn't being used, you're essentially paying for the overhead of maintaining it during writes with no benefit in return. Drop the index!
https://wiki.postgresql.org/wiki/Index-only_scans
adapted from https://dba.stackexchange.com/a/147121 - takes into account specific table settings
WITH config AS (
SELECT oid, reltuples, CAST(
coalesce(nullif(split_part(split_part(array_to_string(reloptions, ','),
'autovacuum_vacuum_threshold=', 2), ',', 1), ''),
current_setting('autovacuum_vacuum_threshold'))::BIGINT
+ coalesce(nullif(split_part(split_part(array_to_string(reloptions, ','),
'autovacuum_vacuum_scale_factor=', 2), ',', 1), ''),
current_setting('autovacuum_vacuum_scale_factor'))::NUMERIC
* reltuples AS NUMERIC) AS threshold
FROM pg_class
)
SELECT stats.relname,
TO_CHAR(config.reltuples, '9G999G999G999') AS tuples,
TO_CHAR(stats.n_dead_tup, '9G999G999G999') AS dead_tuples,
TO_CHAR(config.threshold, '9G999G999G999') AS threshold,
ROUND(stats.n_dead_tup / config.threshold, 2) AS autovacuum_percent,
autovacuum_count AS autovacuum_count,
TO_CHAR(stats.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
vacuum_count AS vacuum_count,
TO_CHAR(stats.last_vacuum, 'YYYY-MM-DD HH24:MI') AS last_vacuum
FROM pg_stat_user_tables stats
JOIN config ON stats.relid = config.oid
ORDER BY config.reltuples DESC;
WITH config AS (
SELECT oid, reltuples, CAST(
coalesce(nullif(split_part(split_part(array_to_string(reloptions, ','),
'autovacuum_analyze_threshold=', 2), ',', 1), ''),
current_setting('autovacuum_analyze_threshold'))::BIGINT
+ coalesce(nullif(split_part(split_part(array_to_string(reloptions, ','),
'autovacuum_analyze_scale_factor=', 2), ',', 1), ''),
current_setting('autovacuum_analyze_scale_factor'))::NUMERIC
* reltuples AS NUMERIC) AS threshold
FROM pg_class
)
SELECT stats.relname,
TO_CHAR(config.reltuples, '9G999G999G999') AS tuples,
TO_CHAR(stats.n_mod_since_analyze, '9G999G999G999') AS mod_tuples,
TO_CHAR(config.threshold, '9G999G999G999') AS threshold,
ROUND(stats.n_mod_since_analyze / config.threshold, 2) AS autoanalyze_pct,
autoanalyze_count AS autoanalyze_count,
TO_CHAR(stats.last_autoanalyze, 'YYYY-MM-DD HH24:MI') AS last_autoanalyze,
analyze_count AS analyze_count,
TO_CHAR(stats.last_analyze, 'YYYY-MM-DD HH24:MI') AS last_analyze
FROM pg_stat_user_tables stats
JOIN config ON stats.relid = config.oid
ORDER BY config.reltuples DESC;
select relname, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, n_mod_since_analyze, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count from pg_stat_user_tables;
Sort the columns by their type length as defined in pg_type
Much like filling a jar with rocks, pebbles, and sand, the most efficient way to declare a Postgres table is by the column alignment type. Bigger columns first, medium columns next, small columns last, and weird exceptions like NUMERIC and TEXT tacked to the end as if they were dust in our analogy.
ALTER TABLE table_name SET autovacuum_vacuum_scale_factor = 0.02;
