Re: tsearch is slow

Abbath wrote:
> 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.

What, never? Even if this facility is only used once a year by one user
and you have 1000 other users who need their queries to complete in 0.2
secs at most? What you mean is that it's not useful for *your*
application - don't assume the same applies to all applications.

> 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)

If you want to force the data to be cached, just put a cron-job in to
run a query for "abc" or whatever once a minute.

Of course, if it turns out that your users aren't running the query very
often then you're wasting resources, and if they are running it often
then again you're wasting resources. But - if you really need to, that's
the solution.

--
Richard Huxton
Archonet Ltd