Skip to content

Instantly share code, notes, and snippets.

@paulmallon

paulmallon/appendix.slq

Last active October 13, 2020 13:23
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save paulmallon/a6ab682f6156fa0d7c84e50ee844dd7b to your computer and use it in GitHub Desktop.
Save paulmallon/a6ab682f6156fa0d7c84e50ee844dd7b to your computer and use it in GitHub Desktop.
Download ZIP
SQL snippets for "PostgreSQL - A maintenance and performance primer"
Raw
appendix.slq
/**
Title: SQL snippets for "PostgreSQL - A maintenance and performance primer"
Author: PM - 2019
*/
----------------------------------------------------
-- Query to find unused indexes
SELECT s.schemaname,
s.relname AS tablename,
s.indexrelname AS indexname,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0 -- has never been scanned
AND 0 <> ALL (i.indkey) -- no index column is an expression
AND NOT i.indisunique -- is not a UNIQUE index
AND NOT EXISTS -- does not enforce a constraint
(SELECT 1
FROM pg_catalog.pg_constraint c
WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;
----------------------------------------------------
-- Find duplicate indexes
SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
(array_agg(idx))[1] AS idx1,
(array_agg(idx))[2] AS idx2,
(array_agg(idx))[3] AS idx3,
(array_agg(idx))[4] AS idx4
FROM (
SELECT indexrelid::regclass AS idx,
(indrelid::text || E'\n' || indclass::text || E'\n' || indkey::text || E'\n' ||
COALESCE(indexprs::text, '') || E'\n' || COALESCE(indpred::text, '')) AS KEY
FROM pg_index) sub
GROUP BY KEY
HAVING COUNT(*) > 1
ORDER BY SUM(pg_relation_size(idx)) DESC;
----------------------------------------------------
-- Show running queries
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>'
AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
----------------------------------------------------
-- Show bocked statements
select pid,
usename,
pg_blocking_pids(pid) as blocked_by,
query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;
----------------------------------------------------
-- kill stuff
SELECT pg_cancel_backend(procpid);
SELECT pg_terminate_backend(procpid);
----------------------------------------------------
-- cache hit rates (should not be less than 0.99)
SELECT sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
(sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;
----------------------------------------------------
-- Enable statiscs module and show data
CREATE EXTENSION pg_stat_statements;
SELECT pg_stat_statements_reset();
select *
from pg_stat_statements;
----------------------------------------------------
-- Show database size
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;
----------------------------------------------------
-- Get table and index sizes
SELECT current_database(),
table_schema as Schema,
TABLE_NAME as Table,
row_estimate as Row_estimate,
pg_size_pretty(total_bytes) AS Total_size_pretty,
pg_size_pretty(index_bytes) AS Index_size_pretty,
pg_size_pretty(toast_bytes) AS Toast_size_pretty,
pg_size_pretty(table_bytes) AS Table_size_pretty,
table_bytes AS Total_size_bytes,
index_bytes AS Index_size_bytes,
toast_bytes AS Toast_size_bytes,
table_bytes AS Table_size_bytes
FROM (
SELECT table_schema,
table_name,
row_estimate,
index_bytes,
toast_bytes,
total_bytes,
total_bytes - index_bytes - COALESCE(toast_bytes, 0) AS table_bytes
FROM (
SELECT c.oid
, nspname AS table_schema
, relname AS table_name
, c.reltuples AS row_estimate
, 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
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) as tableandindexsizes
order by total_bytes desc;
----------------------------------------------------
-- Index size, usage anc last analyze and last vacuum dates
SELECT pg_stat_user_indexes.schemaname,
pg_stat_user_indexes.relname AS tablename,
pg_stat_user_indexes.indexrelname AS indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
pg_stat_user_indexes.idx_tup_read as index_entries_returned_by_scans,
pg_stat_user_indexes.idx_tup_fetch as table_rows_fetched_by_simple_index_scans,
pg_stat_user_indexes.idx_scan as index_scans_initiated_on_this_table,
last_analyze,
last_autoanalyze,
last_autovacuum,
last_vacuum
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
join pg_stat_user_tables on pg_stat_user_indexes.relid = pg_stat_user_tables.relid
WHERE 1 = 1
ORDER BY pg_relation_size(indexrelid) DESC;
----------------------------------------------------
-- Index usage rates (should not be less than 0.99)
SELECT s.schemaname,
s.relname,
i.indexrelname,
CASE
WHEN (s.seq_scan + s.idx_scan) != 0
THEN ROUND(100.0 * s.idx_scan / (s.seq_scan + s.idx_scan), 2)
ELSE 0
END AS percent_of_times_index_used,
n_live_tup AS rows_in_table
FROM pg_stat_user_tables s
JOIN pg_stat_user_indexes i ON s.relid = i.relid
--where n_live_tup > 10000
ORDER BY n_live_tup DESC;
----------------------------------------------------
-- Unused indexes
SELECT s.schemaname,
s.relname AS tablename,
s.indexrelname AS indexname,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
idx_tup_read as index_entries_returned_by_scans,
idx_tup_fetch as table_rows_fetched_by_simple_index_scans,
idx_scan as index_scans_initiated_on_this_table
FROM pg_catalog.pg_stat_user_indexes s
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0 -- has never been scanned
AND 0 <> ALL (i.indkey) -- no index column is an expression
AND NOT i.indisunique -- is not a UNIQUE index
AND NOT EXISTS -- does not enforce a constraint
(SELECT 1
FROM pg_catalog.pg_constraint c
WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;
--- Index hit ratios
with table_stats as (
select psut.schemaname,
psut.relname,
psut.n_live_tup,
1.0 * psut.idx_scan / greatest(1, psut.seq_scan + psut.idx_scan) as index_use_ratio
from pg_stat_user_tables psut
order by psut.n_live_tup desc
),
table_io as (
select psiut.relname,
sum(psiut.heap_blks_read) as table_page_read,
sum(psiut.heap_blks_hit) as table_page_hit,
sum(psiut.heap_blks_hit) /
greatest(1, sum(psiut.heap_blks_hit) + sum(psiut.heap_blks_read)) as table_hit_ratio
from pg_statio_user_tables psiut
group by psiut.relname
order by table_page_read desc
),
index_io as (
select psiui.relname,
psiui.indexrelname,
sum(psiui.idx_blks_read) as idx_page_read,
sum(psiui.idx_blks_hit) as idx_page_hit,
1.0 * sum(psiui.idx_blks_hit) /
greatest(1.0, sum(psiui.idx_blks_hit) + sum(psiui.idx_blks_read)) as idx_hit_ratio
from pg_statio_user_indexes psiui
group by psiui.relname, psiui.indexrelname
order by sum(psiui.idx_blks_read) desc
)
select ts.schemaname,
ts.relname,
ii.indexrelname,
ts.n_live_tup,
trunc(ts.index_use_ratio, 4) as index_use_ratio,
ti.table_page_read,
ti.table_page_hit,
trunc(ti.table_hit_ratio, 4) as table_hit_ratio,
ii.idx_page_read,
ii.idx_page_hit,
trunc(ii.idx_hit_ratio, 4) as idx_hit_ratio
from table_stats ts
left outer join table_io ti
on ti.relname = ts.relname
left outer join index_io ii
on ii.relname = ts.relname
order by ti.table_page_read desc, ii.idx_page_read desc;
----------------------------------------------------
-- Table bloat
select current_database(),
schemaname as Schema,
tblname as Table,
pg_size_pretty(real_size::numeric) as real_size_pretty,
pg_size_pretty(extra_size::numeric) as extra_size_pretty,
ROUND((extra_ratio)::numeric, 2) as extra_ratio,
pg_size_pretty(bloat_size::numeric) as bloat_size_pretty,
ROUND((bloat_ratio)::numeric, 2) as bloat_ratio,
real_size as bloat_size_bytes,
extra_size as extra_size_bytes,
bloat_size as bloat_size_bytes,
fillfactor as Fillfactor
from (
SELECT current_database(),
schemaname,
tblname,
bs * tblpages AS real_size,
(tblpages - est_tblpages) * bs AS extra_size,
CASE
WHEN tblpages - est_tblpages > 0
THEN 100 * (tblpages - est_tblpages) / tblpages::float
ELSE 0
END AS extra_ratio,
fillfactor,
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 100 * (tblpages - est_tblpages_ff) / tblpages::float
ELSE 0
END AS bloat_ratio,
is_na
-- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag
FROM (
SELECT ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) AS est_tblpages,
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
-- , stattuple.pgstattuple(tblid) AS pst
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(null_frac, 0)) > 0 THEN (7 + count(*)) / 8
ELSE 0::int END
+
CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
sum((1 - coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS tpl_data_size,
bool_or(att.atttypid = 'pg_catalog.name'::regtype)
OR
count(att.attname) <> 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 att.attnum > 0
AND NOT att.attisdropped
AND tbl.relkind = 'r'
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, tbl.relhasoids
ORDER BY 2, 3
) AS s
) AS s2
) AS s3
WHERE NOT is_na
) as tablestats
order by real_size desc;
----------------------------------------------------
-- Index bloat
select current_database(),
schemaname as Schema,
tblname as Table,
idxname as Index,
pg_size_pretty(real_size::numeric) as real_size_pretty,
pg_size_pretty(extra_size::numeric) as extra_size_pretty,
ROUND((extra_ratio)::numeric, 2) as extra_ratio,
pg_size_pretty(bloat_size::numeric) as bloat_size_pretty,
ROUND((bloat_ratio)::numeric, 2) as bloat_ratio,
real_size as bloat_size_bytes,
extra_size as extra_size_bytes,
bloat_size as bloat_size_bytes,
fillfactor as Fillfactor
from (
SELECT current_database(),
nspname AS schemaname,
tblname,
idxname,
bs * (relpages)::bigint AS real_size,
bs * (relpages - est_pages)::bigint AS extra_size,
100 * (relpages - est_pages)::float / relpages AS extra_ratio,
fillfactor,
CASE
WHEN relpages > est_pages_ff
THEN bs * (relpages - est_pages_ff)
ELSE 0
END AS bloat_size,
100 * (relpages - est_pages_ff)::float / relpages AS bloat_ratio,
is_na
-- , 100-(sub.pst).avg_leaf_density, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, sub.reltuples, sub.relpages -- (DEBUG INFO)
FROM (
SELECT coalesce(1 +
ceil(reltuples / floor((bs - pageopqdata - pagehdr) / (4 + nulldatahdrwidth)::float)),
0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
) AS est_pages,
coalesce(1 +
ceil(reltuples /
floor((bs - pageopqdata - pagehdr) * fillfactor /
(100 * (4 + nulldatahdrwidth)::float))),
0
) AS est_pages_ff,
bs,
nspname,
table_oid,
tblname,
idxname,
relpages,
fillfactor,
is_na
-- , stattuple.pgstatindex(quote_ident(nspname)||'.'||quote_ident(idxname)) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)
FROM (
SELECT maxalign,
bs,
nspname,
tblname,
idxname,
reltuples,
relpages,
relam,
table_oid,
fillfactor,
(index_tuple_hdr_bm +
maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
WHEN index_tuple_hdr_bm % maxalign = 0 THEN maxalign
ELSE index_tuple_hdr_bm % maxalign
END
+ nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
WHEN nulldatawidth = 0 THEN 0
WHEN nulldatawidth::integer % maxalign = 0
THEN maxalign
ELSE nulldatawidth::integer % maxalign
END
)::numeric AS nulldatahdrwidth,
pagehdr,
pageopqdata,
is_na
-- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
FROM (
SELECT i.nspname,
i.tblname,
i.idxname,
i.reltuples,
i.relpages,
i.relam,
a.attrelid AS table_oid,
current_setting('block_size')::numeric AS bs,
fillfactor,
CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64'
THEN 8
ELSE 4
END AS maxalign,
/* per page header, fixed size: 20 for 7.X, 24 for others */
24 AS pagehdr,
/* per page btree opaque data */
16 AS pageopqdata,
/* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
CASE
WHEN max(coalesce(s.null_frac, 0)) = 0
THEN 2 -- IndexTupleData size
ELSE 2 + ((32 + 8 - 1) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
END AS index_tuple_hdr_bm,
/* data len: we remove null values save space using it fractionnal part from stats */
sum((1 - coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth,
max(CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END) >
0 AS is_na
FROM pg_attribute AS a
JOIN (
SELECT nspname,
tbl.relname AS tblname,
idx.relname AS idxname,
idx.reltuples,
idx.relpages,
idx.relam,
indrelid,
indexrelid,
indkey::smallint[] AS attnum,
coalesce(substring(
array_to_string(idx.reloptions, ' ')
from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor
FROM pg_index
JOIN pg_class idx ON idx.oid = pg_index.indexrelid
JOIN pg_class tbl ON tbl.oid = pg_index.indrelid
JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace
WHERE pg_index.indisvalid
AND tbl.relkind = 'r'
AND idx.relpages > 0
) AS i ON a.attrelid = i.indexrelid
JOIN pg_stats AS s ON s.schemaname = i.nspname
AND ((s.tablename = i.tblname AND
s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE)) -- stats from tbl
OR (s.tablename = i.idxname AND s.attname = a.attname))-- stats from functionnal cols
JOIN pg_type AS t ON a.atttypid = t.oid
WHERE a.attnum > 0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
) AS s1
) AS s2
JOIN pg_am am ON s2.relam = am.oid
WHERE am.amname = 'btree'
) AS sub
WHERE NOT is_na
--ORDER BY 2, 3, 4;
) as indexstats
order by real_size desc;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment