Reindexing in PostgreSql

Question

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 ?

Answer 1

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.