Full text search with more than one word

From: "Ritanjali Majihee" <rmajhee(at)247headhunting(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Cc: "Ambiger, Mahantesh" <mambiger(at)ibs(dot)com>, <rmajhee(at)247headhunting(dot)com>
Subject: Full text search with more than one word
Date: 2019-01-18 10:06:56
Message-ID: !&!AAAAAAAAAAAYAAAAAAAAAKQM2CcVIwNCpuajI1dPVRHCgAAAEAAAAEaa48fsi4hCqOYT0Eat45oBAAAAAA==@247headhunting.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Team,

We have around 20 millions of records in Postgresql database. One of the
column is html document stored as Text datatype. We want Use this column in
search functionality. We have converted html content to plaintext and
created tokens using to_tsvector. We have implemented full text search on
this token column.

1. When we search with one word and logical operators, it returns
results as expected. Example: ("Jack" AND ("Jill" OR "Tom") )

2. However, when we use two or more worded search strings with logical
operators, then it is not giving expected output. Example: for search string
("Jack Jill" OR "Jack Tom"), it is not searching for "Jack Jill" together
but if it finds those two words in one text it returns. Here, if the column
has "Jack Ryan Mark Jill", it will return the row but, we are expecting it
to return only when it is like "Jack Jill Ryan Mark". We found that
proximity operator(<->) can be used in between the words. It returns the
results as expected but, it takes way more than expected time to get the
results; Performance is very poor with proximity operator.

Can you please help us solve this issue. Let us know if you need any
details from our side.

Thanks,

Ritanjali

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michal 2019-01-18 10:30:59 Upgrading from 9.2.15 to 9.4.20
Previous Message Ben Snaidero 2019-01-18 02:05:43 Re: strange slow query performance