Re: tsearch2 and gist index bloat

Thanks for the reply. For this project, I can update the data and reindex during off-peak hours.
I was just surprised to see the size of the index double after heavy write activity.

George Essig

--- "Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
> Hello,
>
> I don't know if you can do this with a gist index but try using the
> REINDEX command.
>
> J
>
>
> George Essig wrote:
>
> >
> > --- 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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
>