patternsqlMinor
Do Postgres GIN indexes need to be rebuilt periodically?
Viewed 0 times
postgresneedginindexesperiodicallyrebuilt
Problem
Do they get bloated like btree indexes? If so how can the bloat be measured e.g. by an automated reindexing script. I am interested in improving query performance and saving space.
Solution
Empirically, I've found GIN indexes need to be re-indexed after I've inserted a large number of rows into the table.
I had a table with 15 million rows that had a GIN index that let me do a keyword search in under 3 seconds. After I inserted another million rows, that same search took 30 seconds. I waited for a day after my inserts, but the search was still slow, so I manually ran
I had a table with 15 million rows that had a GIN index that let me do a keyword search in under 3 seconds. After I inserted another million rows, that same search took 30 seconds. I waited for a day after my inserts, but the search was still slow, so I manually ran
REINDEX on my GIN index, which took a little over an hour, but afterwards my search was back to 3 seconds.Context
StackExchange Database Administrators Q#83258, answer score: 3
Revisions (0)
No revisions yet.