Re: problems: slow queries with tsearch2

On 2/16/07, Rafa Comino <rafacomino(at)gmail(dot)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