tsearch is slow

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

Responses

Browse pgsql-general by date

  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