From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Howard Rogers <hjr(at)diznix(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Full Text Search dictionary issues |
Date: | 2010-07-16 00:23:15 |
Message-ID: | 967.1279239795@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Howard Rogers <hjr(at)diznix(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Howard Rogers | 2010-07-16 04:22:19 | Re: Full Text Search dictionary issues |
Previous Message | Howard Rogers | 2010-07-15 23:20:38 | Full Text Search dictionary issues |