Ranking the query with right order in full text search

From: Anto Aravinth <anto(dot)aravinth(dot)cse(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Ranking the query with right order in full text search
Date: 2020-07-13 03:53:54
Message-ID: CANtp6RLmRHK1agKVt=q8-ewaPNhUL=TEKTZNK1Ot02Ui=mOUAg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello All,

I have the following table:

postgres=# \d so_rum;
Table "public.so_rum"
Column | Type | Collation | Nullable | Default
-----------+-------------------------+-----------+----------+---------
id | integer | | |
title | character varying(1000) | | |
posts | text | | |
body | tsvector | | |
parent_id | integer | | |
Indexes:
"so_rum_body_idx" rum (body)

I wanted to do phrase search query, so I came up with the below query, for
example:

select id from so_rum
where body @@ phraseto_tsquery('english','Is it possible to toggle
the visibility');

This gives me the results, which only match's the entire text. However,
there are documents, where the distance between lexmes are more and the
above query doesn't gives me back those data. For example: `'it is
something possible to do toggle between the. . . visibility'` doesn't get
returned. I know I can get it returned with `<2>` (for example) distance
operator by giving in the `to_tsquery`, manually.

But I wanted to understand, how to do this in my sql statement itself, so
that I get the results first with distance of `1` and then `2` and so on
(may be till `6-7`). Finally append results with the actual count of the
search words like the following query:

select count(id) from so_rum
where body @@ to_tsquery('english','string & string . . . ')

Is it possible to do in a single query with good performance?

Browse pgsql-novice by date

  From Date Subject
Next Message Anrik Drenth 2020-07-14 02:56:02 Postgres 12 - enable en_NZ.UTF-8
Previous Message Vianello, Dan A 2020-07-09 19:58:13 RE: [EXTERNAL] Re: Multi-column join + aggregate subquery resulting in infinite run time