From: | George Essig <george_essig(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | tsearch2 and gist index bloat |
Date: | 2003-11-05 20:27:05 |
Message-ID: | 20031105202705.86860.qmail@web80204.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.series"
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_pkey" 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_vec',
'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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2003-11-05 20:47:02 | Re: Getting time from date field |
Previous Message | Simon Stiefel | 2003-11-05 19:56:06 | Question on database structure |