Need to select indexes which are fragmented for a certain level. Can someone help me with the query for that?

  • What exactly is a "fragmented index"? Are you referring to index bloat?
    – user1822
    Commented Aug 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. Commented Aug 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 % Commented Aug 11, 2020 at 9:43

1 Answer 1


You can use pgstatindex from the pgstattuple extension to get the leaf fragmentation of all B-tree indexes:

SELECT i.indexrelid::regclass,
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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.