pg_index_bloat slow query #678
Description
The pg_index_bloat function defined in
2026-01-14 20:57:01.699 UTC,"dbuser_monitor","postgres",<redacted PID>,"[local]",,<redacted A>,1,"SELECT",2026-01-14 20:56:20 UTC,<redacted TID>/286,0,LOG,00000,"duration: 1106.273 ms plan:
Query Text: SELECT CURRENT_CATALOG AS datname, nspname, idxname AS relname, tblid, idxid,
relpages::BIGINT * bs AS size,
COALESCE((relpages - ( reltuples * (6 + ma - (CASE WHEN index_tuple_hdr % ma = 0 THEN ma ELSE index_tuple_hdr % ma END)
+ nulldatawidth + ma - (CASE WHEN nulldatawidth % ma = 0 THEN ma ELSE nulldatawidth % ma END))
/ (bs - pagehdr)::FLOAT + 1 )), 0) / relpages::FLOAT AS ratio
FROM (
SELECT nspname, idxname, indrelid AS tblid, indexrelid AS idxid,
reltuples, relpages,
current_setting('block_size')::INTEGER AS bs,
(CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END) AS ma,
24 AS pagehdr,
(CASE WHEN max(COALESCE(pg_stats.null_frac, 0)) = 0 THEN 2 ELSE 6 END) AS index_tuple_hdr,
sum((1.0 - COALESCE(pg_stats.null_frac, 0.0)) *
COALESCE(pg_stats.avg_width, 1024))::INTEGER AS nulldatawidth
FROM pg_attribute
JOIN (
SELECT pg_namespace.nspname, ic.relname AS idxname, ic.reltuples, ic.relpages, pg_index.indrelid,
pg_index.indexrelid, tc.relname AS tablename,
regexp_split_to_table(pg_index.indkey::TEXT, ' ')::INTEGER AS attnum,
pg_index.indexrelid AS index_oid
FROM pg_index
JOIN pg_class ic ON pg_index.indexrelid = ic.oid
JOIN pg_class tc ON pg_index.indrelid = tc.oid
JOIN pg_namespace ON ic.relnamespace = pg_namespace.oid
JOIN pg_am ON ic.relam = pg_am.oid
WHERE pg_am.amname = 'btree' AND ic.relpages > 0 AND pg_namespace.nspname NOT IN ('pg_catalog', 'information_schema')
) ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid AND pg_attribute.attnum = ind_atts.attnum
JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname
AND ((pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE))
OR (pg_stats.tablename = ind_atts.idxname AND pg_stats.attname = pg_attribute.attname))
WHERE pg_attribute.attnum > 0
GROUP BY nspname, idxname, indrelid, indexrelid, reltuples, relpages
) est
Subquery Scan on est (cost=6486.94..6487.08 rows=1 width=120) (actual time=1105.523..1106.231 rows=271 loops=1)
Output: CURRENT_CATALOG, est.nspname, est.idxname, est.tblid, est.idxid, ((est.relpages)::bigint * est.bs), (COALESCE(((est.relpages)::double precision - (((est.reltuples * ((((((6 + est.ma) - CASE WHEN ((est.index_tuple_hdr % est.ma) = 0) THEN est.ma ELSE (est.index_tuple_hdr % est.ma) END) + est.nulldatawidth) + est.ma) - CASE WHEN ((est.nulldatawidth % est.ma) = 0) THEN est.ma ELSE (est.nulldatawidth % est.ma) END))::double precision) / ((est.bs - est.pagehdr))::double precision) + '1'::double precision)), '0'::double precision) / (est.relpages)::double precision)
-> GroupAggregate (cost=6486.94..6487.00 rows=1 width=164) (actual time=1105.515..1106.141 rows=271 loops=1)
Output: pg_namespace.nspname, ic.relname, pg_index.indrelid, pg_index.indexrelid, ic.reltuples, ic.relpages, (current_setting('block_size'::text))::integer, CASE WHEN ((version() ~ 'mingw32'::text) OR (version() ~ '64-bit|x86_64|ppc64|ia64|amd64'::text)) THEN 8 ELSE 4 END, 24, CASE WHEN (max(COALESCE(pg_stats.null_frac, '0'::real)) = '0'::double precision) THEN 2 ELSE 6 END, (sum((('1'::double precision - COALESCE(pg_stats.null_frac, '0'::real)) * (COALESCE(pg_stats.avg_width, 1024))::double precision)))::integer
Group Key: pg_namespace.nspname, ic.relname, pg_index.indrelid, pg_index.indexrelid, ic.reltuples, ic.relpages
-> Sort (cost=6486.94..6486.94 rows=1 width=152) (actual time=1105.485..1105.522 rows=330 loops=1)
Output: pg_namespace.nspname, ic.relname, pg_index.indrelid, pg_index.indexrelid, ic.reltuples, ic.relpages, pg_stats.null_frac, pg_stats.avg_width
Sort Key: pg_namespace.nspname, ic.relname, pg_index.indrelid, pg_index.indexrelid, ic.reltuples, ic.relpages
Sort Method: quicksort Memory: 79kB
-> Nested Loop (cost=1825.28..6486.93 rows=1 width=152) (actual time=24.288..1105.248 rows=330 loops=1)
Output: pg_namespace.nspname, ic.relname, pg_index.indrelid, pg_index.indexrelid, ic.reltuples, ic.relpages, pg_stats.null_frac, pg_stats.avg_width
Inner Unique: true
Join Filter: (((pg_stats.tablename = tc.relname) AND (pg_stats.attname = pg_get_indexdef(pg_attribute.attrelid, (pg_attribute.attnum)::integer, true))) OR ((pg_stats.tablename = ic.relname) AND (pg_stats.attname = pg_attribute.attname)))
Rows Removed by Join Filter: 499545
-> Hash Join (cost=1825.00..6452.65 rows=102 width=348) (actual time=21.554..140.100 rows=1149679 loops=1)
Output: pg_namespace.nspname, ic.relname, pg_index.indrelid, pg_index.indexrelid, ic.reltuples, ic.relpages, (((regexp_split_to_table((pg_index.indkey)::text, ' '::text)))::integer), tc.relname, pg_stats.null_frac, pg_stats.avg_width, pg_stats.tablename, pg_stats.attname
Hash Cond: (pg_namespace.nspname = pg_stats.schemaname)
-> Result (cost=99.41..3213.55 rows=110000 width=216) (actual time=0.665..4.969 rows=1407 loops=1)
Output: pg_namespace.nspname, ic.relname, ic.reltuples, ic.relpages, pg_index.indrelid, pg_index.indexrelid, tc.relname, ((regexp_split_to_table((pg_index.indkey)::text, ' '::text)))::integer, NULL::oid
-> ProjectSet (cost=99.41..738.55 rows=110000 width=240) (actual time=0.664..4.785 rows=1407 loops=1)
Output: regexp_split_to_table((pg_index.indkey)::text, ' '::text), pg_namespace.nspname, ic.relname, ic.reltuples, ic.relpages, pg_index.indrelid, pg_index.indexrelid, tc.relname
-> Nested Loop (cost=99.41..187.17 rows=110 width=234) (actual time=0.633..3.450 rows=949 loops=1)
Output: pg_index.indkey, pg_namespace.nspname, ic.relname, ic.reltuples, ic.relpages, pg_index.indrelid, pg_index.indexrelid, tc.relname
Inner Unique: true
-> Hash Join (cost=99.14..147.03 rows=110 width=170) (actual time=0.629..1.701 rows=949 loops=1)
Output: pg_index.indrelid, pg_index.indexrelid, pg_index.indkey, ic.relname, ic.reltuples, ic.relpages, pg_namespace.nspname
Hash Cond: (pg_index.indexrelid = ic.oid)
-> Seq Scan on pg_catalog.pg_index (cost=0.00..42.49 rows=1149 width=34) (actual time=0.012..0.201 rows=1112 loops=1)
Output: pg_index.indexrelid, pg_index.indrelid, pg_index.indnatts, pg_index.indnkeyatts, pg_index.indisunique, pg_index.indnullsnotdistinct, pg_index.indisprimary, pg_index.indisexclusion, pg_index.indimmediate, pg_index.indisclustered, pg_index.indisvalid, pg_index.indcheckxmin, pg_index.indisready, pg_index.indislive, pg_index.indisreplident, pg_index.indkey, pg_index.indcollation, pg_index.indclass, pg_index.indoption, pg_index.indexprs, pg_index.indpred
-> Hash (cost=96.86..96.86 rows=182 width=140) (actual time=0.612..0.618 rows=949 loops=1)
Output: ic.relname, ic.reltuples, ic.relpages, ic.oid, pg_namespace.nspname
Buckets: 1024 Batches: 1 Memory Usage: 168kB
-> Hash Join (cost=2.62..96.86 rows=182 width=140) (actual time=0.037..0.540 rows=949 loops=1)
Output: ic.relname, ic.reltuples, ic.relpages, ic.oid, pg_namespace.nspname
Inner Unique: true
Hash Cond: (ic.relnamespace = pg_namespace.oid)
-> Hash Join (cost=1.10..94.71 rows=200 width=80) (actual time=0.021..0.414 rows=1073 loops=1)
Output: ic.relname, ic.reltuples, ic.relpages, ic.oid, ic.relnamespace
Inner Unique: true
Hash Cond: (ic.relam = pg_am.oid)
-> Seq Scan on pg_catalog.pg_class ic (cost=0.00..87.71 rows=1399 width=84) (actual time=0.008..0.258 rows=1400 loops=1)
Output: ic.oid, ic.relname, ic.relnamespace, ic.reltype, ic.reloftype, ic.relowner, ic.relam, ic.relfilenode, ic.reltablespace, ic.relpages, ic.reltuples, ic.relallvisible, ic.reltoastrelid, ic.relhasindex, ic.relisshared, ic.relpersistence, ic.relkind, ic.relnatts, ic.relchecks, ic.relhasrules, ic.relhastriggers, ic.relhassubclass, ic.relrowsecurity, ic.relforcerowsecurity, ic.relispopulated, ic.relreplident, ic.relispartition, ic.relrewrite, ic.relfrozenxid, ic.relminmxid, ic.relacl, ic.reloptions, ic.relpartbound
Filter: (ic.relpages > 0)
Rows Removed by Filter: 497
-> Hash (cost=1.09..1.09 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)
Output: pg_am.oid
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_catalog.pg_am (cost=0.00..1.09 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)
Output: pg_am.oid
Filter: (pg_am.amname = 'btree'::name)
Rows Removed by Filter: 8
-> Hash (cost=1.27..1.27 rows=20 width=68) (actual time=0.014..0.015 rows=20 loops=1)
Output: pg_namespace.nspname, pg_namespace.oid
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on pg_catalog.pg_namespace (cost=0.00..1.27 rows=20 width=68) (actual time=0.009..0.012 rows=20 loops=1)
Output: pg_namespace.nspname, pg_namespace.oid
Filter: (pg_namespace.nspname <> ALL ('{pg_catalog,information_schema}'::name[]))
Rows Removed by Filter: 2
-> Index Scan using pg_class_oid_index on pg_catalog.pg_class tc (cost=0.28..0.36 rows=1 width=68) (actual time=0.002..0.002 rows=1 loops=949)
Output: tc.oid, tc.relname, tc.relnamespace, tc.reltype, tc.reloftype, tc.relowner, tc.relam, tc.relfilenode, tc.reltablespace, tc.relpages, tc.reltuples, tc.relallvisible, tc.reltoastrelid, tc.relhasindex, tc.relisshared, tc.relpersistence, tc.relkind, tc.relnatts, tc.relchecks, tc.relhasrules, tc.relhastriggers, tc.relhassubclass, tc.relrowsecurity, tc.relforcerowsecurity, tc.relispopulated, tc.relreplident, tc.relispartition, tc.relrewrite, tc.relfrozenxid, tc.relminmxid, tc.relacl, tc.reloptions, tc.relpartbound
Index Cond: (tc.oid = pg_index.indrelid)
-> Hash (cost=1724.31..1724.31 rows=102 width=200) (actual time=20.746..20.754 rows=2111 loops=1)
Output: pg_stats.null_frac, pg_stats.avg_width, pg_stats.schemaname, pg_stats.tablename, pg_stats.attname
Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 511kB
-> Subquery Scan on pg_stats (cost=111.07..1724.31 rows=102 width=200) (actual time=0.596..20.432 rows=2111 loops=1)
Output: pg_stats.null_frac, pg_stats.avg_width, pg_stats.schemaname, pg_stats.tablename, pg_stats.attname
-> Nested Loop Left Join (cost=111.07..1723.29 rows=102 width=469) (actual time=0.596..20.263 rows=2111 loops=1)
Output: n.nspname, c.relname, a.attname, NULL::boolean, s.stanullfrac, s.stawidth, NULL::real, NULL::anyarray, NULL::real[], NULL::anyarray, NULL::real, NULL::anyarray, NULL::real[], NULL::real[], NULL::anyarray, NULL::real, NULL::anyarray
Inner Unique: true
-> Nested Loop (cost=110.92..1717.72 rows=102 width=140) (actual time=0.589..19.672 rows=2111 loops=1)
Output: s.stanullfrac, s.stawidth, c.relname, c.relnamespace, a.attname
Join Filter: (s.starelid = c.oid)
-> Hash Join (cost=110.62..539.72 rows=3444 width=142) (actual time=0.572..8.154 rows=10693 loops=1)
Output: c.relname, c.oid, c.relnamespace, a.attname, a.attrelid, a.attnum
Inner Unique: true
Hash Cond: (a.attrelid = c.oid)
Join Filter: has_column_privilege(c.oid, a.attnum, 'select'::text)
-> Seq Scan on pg_catalog.pg_attribute a (cost=0.00..401.00 rows=10693 width=70) (actual time=0.022..1.927 rows=10693 loops=1)
Output: a.attrelid, a.attname, a.atttypid, a.attlen, a.attnum, a.attcacheoff, a.atttypmod, a.attndims, a.attbyval, a.attalign, a.attstorage, a.attcompression, a.attnotnull, a.atthasdef, a.atthasmissing, a.attidentity, a.attgenerated, a.attisdropped, a.attislocal, a.attinhcount, a.attcollation, a.attstattarget, a.attacl, a.attoptions, a.attfdwoptions, a.attmissingval
Filter: (NOT a.attisdropped)
Rows Removed by Filter: 7
-> Hash (cost=87.71..87.71 rows=1833 width=72) (actual time=0.543..0.544 rows=1897 loops=1)
Output: c.relname, c.oid, c.relnamespace
Buckets: 2048 Batches: 1 Memory Usage: 209kB
-> Seq Scan on pg_catalog.pg_class c (cost=0.00..87.71 rows=1833 width=72) (actual time=0.005..0.380 rows=1897 loops=1)
Output: c.relname, c.oid, c.relnamespace
Filter: ((NOT c.relrowsecurity) OR (NOT row_security_active(c.oid)))
-> Memoize (cost=0.30..0.58 rows=1 width=14) (actual time=0.001..0.001 rows=0 loops=10693)
Output: s.stanullfrac, s.stawidth, s.starelid, s.staattnum
Cache Key: a.attrelid, a.attnum
Cache Mode: logical
Hits: 0 Misses: 10693 Evictions: 0 Overflows: 0 Memory Usage: 826kB
-> Index Scan using pg_statistic_relid_att_inh_index on pg_catalog.pg_statistic s (cost=0.29..0.57 rows=1 width=14) (actual time=0.001..0.001 rows=0 loops=10693)
Output: s.stanullfrac, s.stawidth, s.starelid, s.staattnum
Index Cond: ((s.starelid = a.attrelid) AND (s.staattnum = a.attnum))
-> Memoize (cost=0.15..0.17 rows=1 width=68) (actual time=0.000..0.000 rows=1 loops=2111)
Output: n.nspname, n.oid
Cache Key: c.relnamespace
Cache Mode: logical
Hits: 2103 Misses: 8 Evictions: 0 Overflows: 0 Memory Usage: 2kB
-> Index Scan using pg_namespace_oid_index on pg_catalog.pg_namespace n (cost=0.14..0.16 rows=1 width=68) (actual time=0.002..0.002 rows=1 loops=8)
Output: n.nspname, n.oid
Index Cond: (n.oid = c.relnamespace)
-> Index Scan using pg_attribute_relid_attnum_index on pg_catalog.pg_attribute (cost=0.29..0.31 rows=1 width=70) (actual time=0.001..0.001 rows=0 loops=1149679)
Output: pg_attribute.attrelid, pg_attribute.attname, pg_attribute.atttypid, pg_attribute.attlen, pg_attribute.attnum, pg_attribute.attcacheoff, pg_attribute.atttypmod, pg_attribute.attndims, pg_attribute.attbyval, pg_attribute.attalign, pg_attribute.attstorage, pg_attribute.attcompression, pg_attribute.attnotnull, pg_attribute.atthasdef, pg_attribute.atthasmissing, pg_attribute.attidentity, pg_attribute.attgenerated, pg_attribute.attisdropped, pg_attribute.attislocal, pg_attribute.attinhcount, pg_attribute.attcollation, pg_attribute.attstattarget, pg_attribute.attacl, pg_attribute.attoptions, pg_attribute.attfdwoptions, pg_attribute.attmissingval
Index Cond: ((pg_attribute.attrelid = pg_index.indexrelid) AND (pg_attribute.attnum = (((regexp_split_to_table((pg_index.indkey)::text, ' '::text)))::integer)) AND (pg_attribute.attnum > 0))
Query Identifier: -8<redacted>",,,,,"SQL function ""pg_index_bloat"" statement 1",,,,"pg_exporter","client backend",,<redacted B>
2026-01-14 20:57:01.700 UTC,"dbuser_monitor","postgres",<redacted PID>,"[local]",,<redacted A>,2,"SELECT",2026-01-14 20:56:20 UTC,<redacted TID>/286,0,LOG,00000,"duration: 1108.877 ms plan:
Query Text: SELECT datname, nspname || '.' || relname AS relname, size, ratio FROM pg_index_bloat ORDER BY size DESC LIMIT 64;
Limit (cost=50.25..50.41 rows=64 width=80) (actual time=1108.852..1108.860 rows=64 loops=1)
Output: pg_index_bloat.datname, (((pg_index_bloat.nspname || '.'::text) || pg_index_bloat.relname)), pg_index_bloat.size, pg_index_bloat.ratio
-> Sort (cost=50.25..52.75 rows=1000 width=80) (actual time=1108.851..1108.854 rows=64 loops=1)
Output: pg_index_bloat.datname, (((pg_index_bloat.nspname || '.'::text) || pg_index_bloat.relname)), pg_index_bloat.size, pg_index_bloat.ratio
Sort Key: pg_index_bloat.size DESC
Sort Method: top-N heapsort Memory: 39kB
-> Function Scan on monitor.pg_index_bloat (cost=0.25..15.25 rows=1000 width=80) (actual time=1108.784..1108.811 rows=271 loops=1)
Output: pg_index_bloat.datname, ((pg_index_bloat.nspname || '.'::text) || pg_index_bloat.relname), pg_index_bloat.size, pg_index_bloat.ratio
Function Call: pg_index_bloat()
Query Identifier: 2<redacted>",,,,,,,,,"pg_exporter","client backend",,<redacted B>
2026-01-14 20:57:01.700 UTC,"dbuser_monitor","postgres",<redacted PID>,"[local]",,<redacted A>,3,"SELECT",2026-01-14 20:56:20 UTC,<redacted TID>/0,0,LOG,00000,"duration: 1109.136 ms statement: SELECT datname, nspname || '.' || relname AS relname, size, ratio FROM pg_index_bloat ORDER BY size DESC LIMIT 64;",,,,,,,,,"pg_exporter","client backend",,<redacted B>