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