Need to select indexes which are fragmented for a certain level. Can someone help me with the query for that?
3
-
What exactly is a "fragmented index"? Are you referring to index bloat?– user1822Aug 11, 2020 at 9:03
-
yes. the bloated indexes for a table due to deletion of large number of records. Need to fetch the indexes for a given table which are bloated above 30 % or so.– ADHITHYA SRINIVASANAug 11, 2020 at 9:41
-
Also this given query SELECT * FROM pgstatindex('index_name'); fetches the leaf_fragmentation percentage for a given index. not sure how to use this function to select the indexes for a table with leaf_fragmentation above 30 %– ADHITHYA SRINIVASANAug 11, 2020 at 9:43
Add a comment
|
1 Answer
You can use pgstatindex from the pgstattuple extension to get the leaf fragmentation of all B-tree indexes:
SELECT i.indexrelid::regclass,
s.leaf_fragmentation
FROM pg_index AS i
JOIN pg_class AS t ON i.indexrelid = t.oid
JOIN pg_opclass AS opc ON i.indclass[0] = opc.oid
JOIN pg_am ON opc.opcmethod = pg_am.oid
CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
WHERE t.relkind = 'i'
AND pg_am.amname = 'btree';
This will scan through all these indexes and might take a long time.
Note that it is normal for an index to be up to 70% fragmented.
answered Aug 11, 2020 at 10:11
