Sha256: 91ea5b42dd45b12df25b3dddd5c15ad9ea66c862defa27108bf67aaf723d251f
Contents?: true
Size: 1.13 KB
Versions: 49
Compression:
Stored size: 1.13 KB
Contents
/* Find indexes with a high ratio of NULL values */
SELECT
c.oid,
c.relname AS index,
pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
i.indisunique AS unique,
a.attname AS indexed_column,
CASE s.null_frac
WHEN 0 THEN ''
ELSE to_char(s.null_frac * 100, '999.00%%')
END AS null_frac,
pg_size_pretty((pg_relation_size(c.oid) * s.null_frac)::bigint) AS expected_saving
FROM
pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_class c_table ON c_table.oid = i.indrelid
JOIN pg_indexes ixs ON c.relname = ixs.indexname
LEFT JOIN pg_stats s ON s.tablename = c_table.relname AND a.attname = s.attname
WHERE
-- Primary key cannot be partial
NOT i.indisprimary
-- Exclude already partial indexes
AND i.indpred IS NULL
-- Exclude composite indexes
AND array_length(i.indkey, 1) = 1
-- Exclude indexes without null_frac ratio
AND coalesce(s.null_frac, 0) != 0
-- Larger than threshold
AND pg_relation_size(c.oid) > %{min_relation_size_mb} * 1024 ^ 2
ORDER BY
pg_relation_size(c.oid) * s.null_frac DESC;
Version data entries
49 entries across 49 versions & 1 rubygems