Index fragmentation in PostgreSQL refers to the situation where the physical storage of the index entries is not contiguous, leading to inefficient access patterns and increased I/O operations. This fragmentation can hinder performance by requiring more time to traverse the index. To check for index fragmentation, the pg_stat_all_indexes view can be utilized, which provides statistics about each index in the database, including the number of blocks, tuples, and the last vacuumed timestamp. Monitoring these metrics can help identify fragmented indexes that may need maintenance, such as reindexing, to improve database performance.

Check the index fragmentation in PostgreSQL

SELECT
    schemaname,
    relname,
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
select * from pg_stat_all_indexes ORDER BY pg_relation_size(indexrelid) DESC;