Feasibility of adding range operator for tsquery

[Jon-Murray]

Hi,

I'm just reading through https://www.postgresql.org/message-id/20180709150905.6a27d15c@asp437-ThinkPad-L380 where Aleksandr submitted a patch to include the range operator inside a standard tsquery. For example (taken from the thread):

'term1 <4,10> term2'::tsquery -- Distance between term1 and term2 is
-- at least 4 and no greater than 10
'term1 <,10> term2'::tsquery  -- Distance between term1 and term2 is
-- no greater than 10
'term1 <4,> term2'::tsquery   -- Distance between term1 and term2 is
-- at least 4

This is an absolutely fantastic and much needed addition to postgres FTS imo. I was wondering the feasibility of including this in rum as an operator, or even tsvector2 - or would you require applying a patch/modifying postgres source code? From what i've seen the tsvector code has changed a lot in postgres13 so retroactively applying the patch might not be possible.

Any advice/information would be hugely appreciated - thanks!

[pashkinelfe]

From what I know about FTS, I think the change should be definitely made in PG core fts capabilities not just in RUM extension. The reason is that index data can be not enough to know whether tsvector field suits tsquery for complicated queries especially when they contain ! operator. So we sometimes need to recheck with table entry which is done by internal PG fts and so to have RUM in accordance with PG fts capabilities.

It's pity that this supposedly useful capability hasn't got much attention and hasn't been committed. I think this should be the first step and the Rum modification should follow later.

In principle it seems a feasible modification however I may be wrong. The one thing to be considered: in the current tsquery syntax (a & ! b) <-> c means that a and ! b should have the same position in tsvector and it is not equal to (a <-> c) & (! b <-> c) which means possibly two different positions of c in tsvector. So it seems we can not just push down all phrase operators. And it is needed to agree how will logical operators work inside <k,n> operator and how it can be nested itself. Maybe it will be needed a tree instead of simple positions list to be transferred towards the root of query tree in TS_phrase_output().

[obartunov]

On Mon, May 3, 2021 at 2:50 PM Jonathan Murray ***@***.***> wrote: Hi, I'm just reading through ***@***.***ThinkPad-L380 where Aleksandr submitted a patch to include the range operator inside a standard tsquery. For example (taken from the thread): 'term1 <4,10> term2'::tsquery -- Distance between term1 and term2 is -- at least 4 and no greater than 10 'term1 <,10> term2'::tsquery -- Distance between term1 and term2 is -- no greater than 10 'term1 <4,> term2'::tsquery -- Distance between term1 and term2 is -- at least 4

That was proposed in our original phrase search patch ( http://www.sai.msu.su/~megera/postgres/talks/2009.pdf) , but community decided to postpone this for the simplicity.

This is an absolutely fantastic and much needed addition to postgres FTS imo. I was wondering the feasibility of including this in rum as an operator, or even tsvector2 - or would you require applying a patch/modifying postgres source code? From what i've seen the tsvector code has changed a lot in postgres13 so retroactively applying the patch might not be possible. Any advice/information would be hugely appreciated - thanks!

Honestly, I think it should be a part of postgres core, somebody need to work on the idea. Moreover, it would be nice to merge RUM and GIN. RUM was born as result of experiments on improving GIN.

…

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#97>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQURYWD56VN52D4JCULGALTL2EZDANCNFSM44AVVOZQ> .

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

[Jon-Murray]

FWIW I was able to pretty easily backport this patch into postgres 13 with only very minor modifications. I agree that it should be part of standard, but will have to close this issue for now until it is. Thanks for the replies (and the original patch!)

[obartunov]

On Mon, May 10, 2021 at 9:23 PM pashkinelfe ***@***.***> wrote: From what I know about FTS I think the change should be definitely in PG core fts capabilities not just in RUM extension.

Agree.

The reason for this is that index data can be not enough to know whether tsvector field suits tsquery for complicated queries especially when they contain ! operator. So we sometimes need to recheck with table entry which is done by internal PG fts and so to have RUM in accordance with PG fts capabilities. It's pity that this supposedly useful capability hasn't got much attention and hasn't been committed. I think this should be the first step and the Rum modification should follow later.

As I wrote upthread, ideally, GIN should be merged with RUM.

In principle it seems a feasible modification however I may be wrong. The one thing to be considered: in the current tsquery syntax (a & ! b) <-> c means that a and ! b should have the same position in tsvector and it is not equal to (a <-> c) & (! b <-> c) which means possibly two different positions of c in tsvector. So it seems we can not just push down all phrase operators. And it is needed to agree how will logical operators work inside <k,n> operator and how it can be nested itself. Maybe it will be needed aa tree instead of simple positions list to be transferred towards the root of query tree in TS_phrase_output().

Looks like neat idea. Interesting, in 2008 we worked on algebra of FTS (some russian words, but slides should be understandable) http://www.sai.msu.su/~megera/postgres/talks/algebra-fts.pdf http://www.sai.msu.su/~megera/postgres/talks/algebra-fts-II.pdf

…

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#97 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQURYVD47EZEFPIMVBX7DLTNAQCJANCNFSM44AVVOZQ> .

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

[pashkinelfe]

FWIW I was able to pretty easily backport this patch into postgres 13 with only very minor modifications. I agree that it should be part of standard, but will have to close this issue for now until it is. Thanks for the replies (and the original patch!)

Yes, I've also taken a look at the patch. Even if it applies and tests are successful my main reason for doubt on it is that it has only tests for simple queries with range operator. And I know from the activity on Tsearch modification in PG13 that main problems were detected anew after several years of tsearch was already in PG in some complicated queries, possibly that ones which have nesting of phrase and logical operators (especially problematic was (!) operator inside other ones). So it definitely needs some elaboration of tests i.e. add complicated queries designed to verify the right implementation of phrase/logical operators nesting. Like select tsvector @@ 'a | (b <1,10> (! c:A & d <1,2> e:C) | f:AB) '::tsquery etc.

The main point of doubt on FTS algebra is that logical operators inside phrase ones are implemented to be not the same as logical operators outside for agglutinative languages support. That needs implementation of range operator to be in accordance, and it may need some limitations for it.