Re: Full Text Search dictionary issues

Howard Rogers <hjr@diznix.com> writes:
> I have 10 million rows in a table, with full text index created on one
> of the columns. I submit this query:

> ims=# select count(*) from search_rm
> ims-# where to_tsvector('english', textsearch)
> ims-# @@ to_tsquery('english', 'woman & beach & ball');
>  count
> -------
>    646
> (1 row)
> Time: 107.570 ms

> ...and those are excellent times. But if I alter the query to read:

> ims=# select count(*) from search_rm
> where to_tsvector('english', textsearch)
> @@ to_tsquery('english', 'woman & beach & ftx1');
>  count
> -------
>  38343
> (1 row)
> Time: 640.985 ms

> ...then, as you see, it slows the query down by a factor of about 6,

... um, but it increased the number of matching rows by a factor of
almost 60.  I think your complaint of poor scaling is misplaced.

> which is not so good! The problem is that we need to be able to search
> for "ftx1", since that's a flag we put in our document records to tell
> us the file type, and we need to be able to retrieve different file
> types at different times.

You might want to rethink how you're doing that --- it seems like a file
type flag ought to be a separate column rather than a word in a text
field.

            regards, tom lane