How can I figure out what all the indexes need to be reindexed in a Postgres Database?And I need to schedule a job to reindex the indexes in Postgres, How often should I run it?
And stats gathering of Indexes can be taken care of Autoanalyze of autovacuuming part or should we run it separately ?
You normally don't need to reindex a PostgreSQL index. They will become somewhat fragmented over time, but that is normal.
That said, you can use the pgstatindex function from the pgstattuple extension to examine an index:
CREATE EXTENSION pgstattuple;
SELECT avg_leaf_density FROM pgstatindex('items_1_pkey');
avg_leaf_density
------------------
88.92
(1 row)
That index is perfectly dense, the density is about the same as the fillfactor (90). Consider reindexing if the value drops below 20 or so.
