tsearch2 and gist index bloat

[George Essig]

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:

PostgreSQL 7.4RC1
Red Hat 9

Table "public.ser ies"
Column | Type | Modifiers
---------------+-------------------+--------------------------------------------------------
id | integer | not null default nextval('public .series_id_seq' ::text)
database | character varying | not null
name | character varying | not null
description | character varying |
documentation | character varying |
ts_vec | tsvector |
Indexes:
"series_pke y" primary key, btree (id)
"series_db_name _un" unique, btree ("database", name)
"ts_in" gist (ts_vec)
Triggers:
ts_update BEFORE INSERT OR UPDATE ON series FOR EACH ROW EXECUTE PROCEDURE tsearch2('ts_ve c',
'description', 'documentation' )

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

Below is some output from VACUUM FULL ANALYZE VERBOSE after the 40,422 rows were deleted.

INFO: index "ts_in" now contains 70451 row versions in 2969 pages
DETAIL: 40422 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.27s/0.44u sec elapsed 1.03 sec.

As the index size grows, search performance slows to a crawl because it's too to fit in RAM. Is
the only solution to drop and recreate the index after large updates?

Thanks,

George Essig

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings