7

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.

3

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.

2

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
    Did you mean "are not built"? – ypercubeᵀᴹ Feb 9 '16 at 19:56
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.