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.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.  
experiences this.  GiST indexes on tsvector columns can get really big.  You have done 
wrong.  When you have a lot of records, tsearch2 will not run fast without extensive 

Read the following:


stat function

Stop words

Multicolumn GiST index

openfts-general mailing list archive

Try some of things out and let me know how it goes.

George Essig

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to