pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: George Essig <george_essig@yahoo.com>
To: pgsql-performance@postgresql.org
Cc: footcow@noos.fr
Subject: Re: TSearch2 and optimisation ...
Date: Thu, 26 Aug 2004 10:58:33 -0700 (PDT)
Message-ID: <20040826175833.32182.qmail@web53703.mail.yahoo.com> (raw )
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
view thread (8+ messages) latest in thread
Message-ID: <20040826175833.32182.qmail@web53703.mail.yahoo.com>
Permalink: ../20040826175833.32182.qmail@web53703.mail.yahoo.com/
Also on: postgresql.org/message-id/20040826175833.32182.qmail@web53703.mail.yahoo.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: george_essig@yahoo.com, footcow@noos.fr
Subject: Re: TSearch2 and optimisation ...
In-Reply-To: <20040826175833.32182.qmail@web53703.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox