Ranking the query with right order in full text search

Поиск
Список
Период
Сортировка
От Anto Aravinth
Тема Ranking the query with right order in full text search
Дата
Msg-id CANtp6RLmRHK1agKVt=q8-ewaPNhUL=TEKTZNK1Ot02Ui=mOUAg@mail.gmail.com
обсуждение исходный текст
Список 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? 

В списке pgsql-novice по дате отправления:

Предыдущее
От: "Vianello, Dan A"
Дата:
Сообщение: RE: [EXTERNAL] Re: Multi-column join + aggregate subquery resulting in infinite run time
Следующее
От: Anrik Drenth
Дата:
Сообщение: Postgres 12 - enable en_NZ.UTF-8