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?
