Need to select indexes which are fragmented for a certain level. Can someone help me with the query for that?
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 = 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.