Re: GiST index for pgtrgm bloats a lot

On Mon, May 18, 2015 at 04:19:41PM +0300, Heikki Linnakangas wrote:
> Are the columns that are included in the bloated index also updated that
> often? If not, I'd suggest moving those columns to a separate table with a
> one-to-one relationship to the main table. Or perhaps just create a helper
> table that contains copies of those columns, and keep it up-to-date with
> triggers. That would reduce the churn in the index.

I'm not sure if this is possible, but will look into it.

> 1. When GiST has multiple equally good choices where it could insert a
> tuple, it favours branches that are "earlier" in the index. Always
> descending down the same branch is good for cache efficiency when you insert
> multiple items with similar keys, but the downside is that the other
> branches can easily have a lot of free space that goes unused, while the
> "hot" branch just gets split repeatedly. This is explained in the comments
> in the gistchoose() function. That leads to bloat, because the free space
> isn't being used.

This looks related. Too bad that we don't have any other way to handle
it, but at the very least I know it's not some "bug", it's just
a missing feature.

Thanks a lot,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/