Re: ltree + gist index performance degrades

On Fri, 2006-02-24 at 11:02, CG wrote:
> PostgreSQL 8.1.3
>
> I'm trying to collect some hard numbers to show just how much it degrades and
> over how long a time interval.
>
> All I have now is anecdotal evidence, and I was hoping to save myself some
> downtime by seeking advice early.
>
> I have a search table which I use for partial-match text searches:
>
> CREATE TABLE search
> (
> id int4 NOT NULL DEFAULT nextval('search_id_seq'::regclass),
> item_id int8 NOT NULL,
> search_vector ltree NOT NULL,
> CONSTRAINT search_id_pkey PRIMARY KEY (id),
> CONSTRAINT search_item_id_fkey FOREIGN KEY (item_id)
> REFERENCES items (id) MATCH SIMPLE
> ON UPDATE CASCADE ON DELETE CASCADE
> )
> WITH OIDS;
>
> CREATE INDEX lsearch_vector_idx
> ON search
> USING gist
> (search_vector);
>
> I have some triggers that insert rows into the search table as rows are
> inserted into "items".
>
> I implimented this yesterday, and the immediate effect was a fantastic return
> time for partial text searches in the sub-second range. By today, these queries
> take 10 minutes sometimes... There are about 134000 rows in the table.
>
> The table gets analyzed nightly. Should the frequency be more? There are about
> 1000 rows added a day, only about 30 or so rows removed, and nothing is ever
> updated. There's not that much turnover.
>
> The search vectors are built like this:
>
> For a string "Hello World" the ltree is created like 'h.e.l.l.o.w.o.r.l.d' ...
> If I wanted to find all rows with "orl" in them i would construct an lquery
> like '*.o.r.l.*' and use the "~" operator in the where clause. I would link to
> the table "items" by the item_id ...
>
> What could be making this go so wrong? Is there a better way to accomplish my
> task?

Are you vacuuming regularly, are your fsm settings high enough, and what
does vacuum verbose say?