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.
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 REINDEX on my GIN index, which took a little over an hour, but afterwards my search was back to 3 seconds.
GIN indexes are built using B-trees, but with a different structure.
"The potential for bloat in non-B-tree indexes has not been well researched. It is a good idea to periodically monitor the index's physical size when using any non-B-tree index type."
See more at: Routine reindex
-
1
Well, it depends a bit on the use case. Reusing item pointers in GIN is definitely a lot harder than it is in a btree because the structure is totally different on the item pointer level. With the new GIN structure life has become even harder to predict so I would keep a close eye on it - depending on your way of using GIN a REINDEX might be necessary. But as stated before - hard evidence is hard to come by here.
answered Nov 21 '14 at 14:27
