I want to prepare a job that checks for fragmentation and reindexes those with fragmentation over 60. But I couldn't figure out how to do it. Currently, I have a query that only checks the index and reindexes it if the fragmentation of this index is over 60. But this is not enough, I need to list the indexes directly on the table and ensure that it reindexes all indexes that meet the condition. How can I do that?
DO
$$
BEGIN
IF EXISTS (
SELECT 1
FROM pgstatindex('my_schema."index_name"')
WHERE leaf_fragmentation > 60
) THEN
EXECUTE 'REINDEX INDEX my_schema."index_name"';
end if;
END
$$;
end
However, this is not sufficient; I need to directly list the indexes on the table and ensure that it reindexes all indexes that meet the condition. How can I achieve this?
The query where I normally list indexes with fragmentation above 60 on a table is as follows:
select s.leaf_fragmentation, i.indexrelid::regclass AS index_name,c.idx_scan,
pg_size_pretty(pg_total_relation_size(i.indexrelid)) AS total_size,
s.* FROM pg_index AS i
JOIN pg_class AS t ON i.indexrelid = t.oid
JOIN pg_stat_all_indexes AS c ON i.indexrelid = c.indexrelid
CROSS JOIN LATERAL pgstatindex (i.indexrelid) AS s
where i.indrelid = 'my_schema."my_table"'::regclass and s.leaf_fragmentation > 60;
Even if I add this query appropriately to the condition section and list it on a table basis, I do not know how to write the query that renews the indexes listed here in the execute section.