From: | Abbath <abbath(at)invitel(dot)hu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | tsearch is slow |
Date: | 2006-03-15 13:50:25 |
Message-ID: | 1817213301.20060315145025@invitel.hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
Recently I have tried TSearch2 (also on Linux and win with an
Athlon64 3000+ machine).
I have a table and I loaded some text files into it, so I have a row
number, and a text column and there is a ts_vec column for tsvector.
I created the gist index for ts_vec. The table has ~ 1 million
records. It seems that using a regular search query like:
SELECT line_number, headline(line, to_tsquery('keyword'))
FROM tstexts
WHERE ts_vec @@ to_tsquery('keyword')
ORDER BY rank(ts_vec, to_tsquery('keyword')) DESC
is slow for the first time (7-15 sec), but then using the same keyword
next time it is fast (10-100 ms). The reason is, as I read, first time
it is not cached at all, but next time the index pages are
cached so it is fast.
I think in a real word application, in this form, it is useless, because
the 15 sec search time is not allowable for any user. Is there a way
to initially cache the index or tune some postgres parameter? (I tried
to increase "shared_buffers", "effective_cache_size", "work_mem" but
had no effect on it) Or I should look for another search technology? (I
just want to provide a 2 sec maximum search time at 1 million records,
I think it is not a big expectation nowadays)
Abbath
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2006-03-15 14:34:02 | Re: Wisconsin Circuit Court Access (WCCA) on |
Previous Message | Oleg Bartunov | 2006-03-15 11:25:04 | Re: full text indexing |