Re: TSearch2 and optimisation ...

George,

Le Jeudi 26 Août 2004 19:58, George Essig a écrit :
> Bill Footcow wrote:
>
> ...
>
> > I have done a simple request, looking for title or description having
> > Postgres inside order by rank and date, like this :
> > SELECT a.title, a.id, a.url, to_char(a.r_date, 'DD/MM/YYYY HH24:MI:SS')
> > as dt, s.site_name, s.id_site, case when exists (select id_user from
> > user_choice u where u.id_site=s.id_site and u.id_user = 1) then 1 else 0
> > end as bookmarked FROM article a, site s
> > WHERE s.id_site = a.id_site
> > AND idxfti @@ to_tsquery('postgresql')
> > ORDER BY rank(idxfti, to_tsquery('postgresql')) DESC, a.r_date DESC;
> >
> > The request takes about 4 seconds ... I have about 1 400 000 records in
> > article and 36 000 records in site table ... it's a Bi-Pentium III 933
> > MHz server with 1 Gb memory ... I'm using Postgresql 7.4.5
> > For me this result is very very slow I really need a quicker result with
> > less than 1 second ...
> > The next time I call the same request I have got the result in 439 ms ...
> > but
>
> ...
>
> The first query is slow because the relevant index pages are not cached in
> memory. Everyone experiences this. GiST indexes on tsvector columns can
> get really big. You have done nothing wrong. When you have a lot of
> records, tsearch2 will not run fast without extensive performance tuning.
>
> Read the following:
>
> Optimization
> http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/
>optimization.html
>
> ...

I have well read many pages about this subject ... but I have not found any
thing for the moment to really help me ...
What can I do to optimize my PostgreSQL configuration for a special use of
Tsearch2 ...
I'm a little dispointed looking the Postgresql Russian search engine using
Tsearch2 is really quick ... why I can't haev the same result with a
bi-pentium III 933 and 1Gb of RAM with the text indexation of 1 500 000
records ?

Regards,
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902