Thread: Full Text Search - Slow on common words

Hi,

I have a small web page set up to search within my domain based on keywords.
One of the queries is:
  SELECT page.id ts_rank_cd('{1.0, 1.0, 1.0, 1.0}',contFTI,q) FROM page,
    to_tsquery('steve') as q WHERE contFTI @@ q

My problem is: when someone puts in a commonly seen word, the system slows
down and takes a while because of the large amount of data being returned
(retrieved from the table) & processed by the rand_cd function.

How does everyone else handle something like this?  I can only think of 2
possible solutions:
- change the query to search for the same terms at least twice in the same
document (can I do that?)
- limit any searches to x results before ranking & tell the user their
search criteria is too generic.

Is there a better solution that I am missing?

Thanks,
Steve



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Full-Text-Search-Slow-on-common-words-tp3241060p3241060.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thu, 2010-10-28 at 12:08 -0700, sub3 wrote:
> Hi,
>
> I have a small web page set up to search within my domain based on keywords.
> One of the queries is:
>   SELECT page.id ts_rank_cd('{1.0, 1.0, 1.0, 1.0}',contFTI,q) FROM page,
>     to_tsquery('steve') as q WHERE contFTI @@ q
>
> My problem is: when someone puts in a commonly seen word, the system slows
> down and takes a while because of the large amount of data being returned
> (retrieved from the table) & processed by the rand_cd function.
>
> How does everyone else handle something like this?  I can only think of 2
> possible solutions:
> - change the query to search for the same terms at least twice in the same
> document (can I do that?)
> - limit any searches to x results before ranking & tell the user their
> search criteria is too generic.
>
> Is there a better solution that I am missing?
>

if the keyword is that common, is it really a keyword?  Exclude it.