problems: slow queries with tsearch2

On 2/16/07, Rafa Comino <rafacomino@gmail.com> wrote:

Hi from Spain,

I have a problem with TSearch2,

I have a table with more than a million registers (a table of books, for
example),

I made a tsearch2 index for one of my fields (the title of the books, for
example),

I make queries from that table, over the tsearch2 index. Then some of my
queries get too much slowly, and I don't know how to resolve it. For
example:
Every thing is ok, but when the query get a lot of results (about 1000
register) and I use ORDER BY over other field in the query (even if I use
LIMIT 10) the query gets slowly, 7 or 8 seconds. The thing is, that query
gets all the results and later order those results, and that makes my query
so slow. How can I resolve this.
When my queries have two conditions joined with AND, and each condition is
made over a different tsearch2 index, the execution get first x rows from
the first condition, and y rows from the second condition, later get the
rows from both result set. This makes my queries slow too. How could I
resolve this?.

(Sorry for my English, and thanks for reading me)

I use tsearch and have never found it to be slow, so I wonder if you
have indexes on the tsvector columns in your tables and if you have
vacuumed recently? Something like this..

CREATE INDEX idxFTI_idx ON tblMessages USING gist(idxFTI);
VACUUM FULL ANALYZE;

Also, if you have other conditions in your query they will want to
have valid indexes to use as well.

If you post an EXPLAIN and/or EXPLAIN ANALYZE output more people will
probably chime in.

- Ian