Re: tsearch2 and gist index bloat

--- George Essig <george_essig(at)yahoo(dot)com> wrote:
> I have installed tsearch2 and have noticed that the gist index used to do searches grows and
> grows
> as I update rows, delete rows, or run VACUUM FULL ANALYZE. Below are some details:
>

....

>
> There are 110,873 rows in this table and 13398 unique words indexed by ts_in. Using oid2name, I
> monitored the size of the index ts_in as I performed different operations:
>
> 154 MB After the index was created.
> 190 MB After updating 40,422 rows.
> 243 MB After VACUUM FULL
> 275 MB After deleting 40,422 rows & again VACUUM FULL
>

Sorry, I mis-reported the index sizes. They are about 1/10 the size:

15 MB After the index was created.
19 MB After updating 40,422 rows.
24 MB After VACUUM FULL
27 MB After deleting 40,422 rows & again VACUUM FULL

I still have a problem that the index size grows and grows and eventually searches slow to a
crawl.

George Essig