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?
CG
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
