| 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: | Whole Thread | Raw Message | 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 |