Comparing two timestamps with either <=| or |=>

[ngigiwaithaka]

Given an a rum index ..

> CREATE INDEX tsts_idx ON tsts USING rum (t rum_tsvector_addon_ops, time_created_as_from_server)
>     WITH (attach = 'time_created_as_from_server', to = 't');

Where time_created_as_from_server is a BIGINT

I would like to use this index to compare a given timestamp to time_created_as_from_server.

When I do ...

timestamp |=> time_created_as_from_server
or
timestamp or timestamp <=| time_created_as_from_server

While it returns the positive values okay, where it should return -ve values, it returns Infinity!

Is there a way to use these indexes to tell when one timestamp is either > or < than the other?

[za-arthur]

Hello @ngigiwaithaka ,
This behavior is intentional. With |=> we get distances of right timestamps, with <=| we get distances of left timestamps, others in Infinity distance.
What kind of queries do you want to run?

[ngigiwaithaka]

Using the above example...

SELECT * FROM articles
WHERE t @@ to_tsquery('English', 'SearchTerm')
AND time_created_as_from_server > 1527076860000

Basically, since I have the time_created_as_from_server on the index I would like to get records filtered by that index...

Regards

[ngigiwaithaka]

In additon,

what I was doing is

SELECT * FROM articles
WHERE t @@ to_tsquery('English', 'SearchTerm')
AND (time_created_as_from_server <=| 1524582843794) > 0

Which is what gets me infinity if not true above!

[za-arthur]

I see. Then you should use >and < operators. What is query plan of such query?

|=> and <=| operators are intended to be used within ORDER BY clause.

Also you can try this index:

CREATE INDEX tsts_idx ON tsts USING rum (t rum_tsvector_addon_ops, time_created_as_from_server)
    WITH (attach = 'time_created_as_from_server', to = 't', order_by_attach = 't');

[ngigiwaithaka]

The query plan is as below, quite fast actually and indeed it's using the Index (weighted_tsv = t)

I haven't tried the second index, what does order_by_attach = 't' do?

P.S
I think you could make it clearer in your documentation here that those operators are only used for Ordering.

Good work!

[za-arthur]

I haven't tried the second index, what does order_by_attach = 't' do?

order_by_attach options specify that an index will be ordered by time_created_as_from_server attribute (i.e. by attribute specified by attach option). Here 't' means true, not first t attribute.
It can be useful if you run queries by attached attribute and you can omit @@ FTS operator.

I think you could make it clearer in your documentation here that those operators are only used for Ordering.

Agree! Documentation needs a lot of work actually with more proper examples.

[ngigiwaithaka]

Interesting,

Will try it out and revert.

Thanks...

[ngigiwaithaka]

@za-arthur

I have recreated the index as you had suggested and works pretty well.

Now, I am trying to understand what you mean by this...

It can be useful if you run queries by attached attribute and you can omit @@ FTS operator.

Any example of how I can do that?

[za-arthur]

Thank you for the feedback. You can try this query:

SELECT * FROM articles
WHERE time_created_as_from_server > 1527076860000

Is it still use the index?

[ngigiwaithaka]

Yes it does!

Glad to say RUM index is sped up some of our queries 5x - 10x from earlier GIN Indexes

One final thing, can you attach more than one field to the ts_vector?

E.g if you had both these fields time_created_as_per_server and time_created_as_per_user, can you attach both?

[za-arthur]

Great!
Unfortunately, it isn't possible. You can attach only one attribute. We had a discussion about that, but it's implementation would be too complicated.

[ngigiwaithaka]

Thanks a lot, really appreciate!

[za-arthur]

Thanks. I close the issue then.