Skip to content

Instantly share code, notes, and snippets.

@boriska70

boriska70/postgres_analyze.txt

Last active April 23, 2022 18:36
Show Gist options
  • Save boriska70/3eac1d27d06e942fd5f77088ad3529b1 to your computer and use it in GitHub Desktop.
Save boriska70/3eac1d27d06e942fd5f77088ad3529b1 to your computer and use it in GitHub Desktop.
Download ZIP
Useful Postgres analysis queries
Raw
postgres_analyze.txt
Title: Postgres DDL blocking and non-blocking operations
URL: https://leopard.in.ua/2016/09/20/safe-and-unsafe-operations-postgresql#.YmRFt5NByDU
============================================================
Title: Postgres size functions (and many others)
URL: https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
============================================================
Title: count number of rows for some time interval divided by time slots
Query:
select (extract(minute FROM created_at)::int / 5) AS min5_slot, count(*) from some_events re where created_at > now() - interval '3 hour' group by 1 order by 1;
select (extract(minute FROM created_at)::int/1) AS min1_slot, count(*) from some_events re where created_at > now() - interval '30 minute' group by 1;
select (extract(day FROM updated_at)::int / 1) AS d1_slot, (extract(hour FROM updated_at)::int / 1) AS h1_slot, count(*) from some_events re where created_at > now() - interval '7 day' group by 1,2 order by 1,2;
============================================================
Title: estimate number of rows without count
Query: SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'participants_2019_10';
============================================================
Title: list existing indices:
Query: select * from pg_catalog.pg_indexes where tablename = 'quests';
============================================================
Title: currently running queries (example for vacuum but good for all)
Source:
Query: SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%' AND query LIKE '%autovacuum%';
And deleting such queries:
select pg_cancel_backend(pid) from pg_stat_activity where usename = 'XYZ' and state <> 'idle';
============================================================
Title: show last auto-vacuum and auto-analyze
Source:
Query: select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;
============================================================
Title: vacuum thresholds and more
Source: https://medium.com/gett-engineering/scaling-postgresql-check-your-vacuum-f03092a6399e
Query: SELECT
pg_stat_user_tables.relname,
pg_stat_user_tables.n_dead_tup,
50 + 0.1 * pg_class.reltuples as vacuum_threshold,
pg_class.reltuples,
pg_stat_user_tables.n_live_tup,
pg_stat_user_tables.n_tup_del,
pg_stat_user_tables.n_tup_upd,
pg_stat_user_tables.autovacuum_count,
pg_stat_user_tables.last_vacuum,
pg_stat_user_tables.last_autovacuum,
now() as now,
pg_stat_user_tables.n_dead_tup > (50 + 0.1 * pg_class.reltuples) as is_vacuum
FROM
pg_stat_user_tables INNER JOIN pg_class ON pg_stat_user_tables.relname = pg_class.relname
ORDER BY
pg_stat_user_tables.n_dead_tup > (50 + 0.1 * pg_class.reltuples) DESC;
==========================================
Title: estimate wasted space in DB
Source: https://wiki.postgresql.org/wiki/Show_database_bloat
Query: SELECT
current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
iname, /*ituples::bigint, ipages::bigint, iotta,*/
ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) 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 (
SELECT
ma,bs,schemaname,tablename,
(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
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+COUNT(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::NUMERIC) AS bs,
CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC
==========================================
Title: size of tables/objects in the database
Query:
SELECT
relname as "Table",
pg_size_pretty(pg_total_relation_size(relid)) As "Size",
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
NOTE: Size includes external size (that is for indexes, stored procedures, etc.)
Query:
SELECT
relname AS objectname,
relkind AS objecttype,
reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_class
WHERE relpages >= 8
ORDER BY relpages DESC;
NOTE: entries - number of rows for table, size is for per relation (table w/o index)
Query - rows counter without count:
SELECT * FROM pg_class WHERE relname = 'table_name';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment