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 rows with 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 3-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? How do I speed up this processing? I can't find any parameters for controlling this.
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.
My table stores dozens of descriptors about a customer, and the indexes (many of them partial indexes) are for speeding up very routine queries run by users.
The database is nearly a terabyte, so I rarely load the entire thing to a dev environment. Instead, I have a script that copies a few thousand records.
REINDEX
with measuring the time needed. Then calculate the total size of your indexes and do some math.EXPLAIN (ANALYZE, BUFFERS)
to capture the execution plan/stats on the queries when they are slow (and for comparison, also when they are fast) is the best way to find the real bottleneck.