Re: phraseto_tsquery design

Hello,

On Thu, Jun 21, 2018 at 11:02:32AM -0400, Sagiv Some wrote:
> 2. However, it seems impossible to bypass the performance problem of phrase
> searching. I conduct quite a bit of phrase searching, and although
> postgres' "phraseto_tsquery" performs great on phrases with uncommon words,
> it slows to a screeching halt on phrases with common words such as "law
> firm" or, for example, "bank of america". This is a huge problem, because
> "plainto_tsquery" performs just fine on these but as I understand it,
> phrase searching is built to do a scan after finding each word using
> "plainto"?
>
> There are already positions and the "plainto" function is quite fast; is
> there a way to modify the "phraseto" query to perform a useful and fast
> search that looks for the distance between found words appropriately?

If I understood you correctly you use GIN index for text search.

Unfortunately it isn't phraseto_tsquery() function issue. It is GIN
index characteristic.

tsvector consists of lexemes and their positions retreived from text
document. GIN has only lexems, and it is OK for regular search (using
plainto_tsquery() function). But phrase search (via phraseto_tsquery())
requires lexem positions. During phrase search additional work is made:
- first get all items from index which satisfy the query (as in reqular
search)
- then read entire tsvector from the heap
- recheck all got items and exclude those of them which don't satisfy
the phrase query

Last two point is additional work.

We have our index as an extension. It is changed GIN index and can store
lexemes and their positions. And therefore phrase queries are faster.

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company