This question is an exact duplicate of:
- How to estimate reindexing time? 1 answer
How do you estimate the total time reindexing an entire table will take in PostgreSQL?
I have a large PostgreSQL database, the primary table of which has 15 million records and 100 columns, and 200 indexes.
I'm seeing a problem where, after a night of processes loading data into the table, queries to the table are terribly slow for about 6 hours after the loading ends. However, these queries miraculously become fast again after 6 hours post-load.
Is this temporary slowness caused by Postgres updating indexes in the background?
I was thinking about running a REINDEX on the table immediately after loading ends, but I'm not sure how long this will take, and I'm worried it might take longer than the original load and will only worsen performance.