PostgreSQL Scheduled Reindexing

66 views Asked by At

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.

0

There are 0 answers