Re: TSearch2 and optimisation ... - Mailing list pgsql-performance

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

stat function
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/stat.html

Stop words
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/stop_words.html

Multicolumn GiST index
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/multi_column_index.html

openfts-general mailing list archive
http://sourceforge.net/mailarchive/forum.php?forum=openfts-general

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

George Essig