Re: [GENERAL] Updated RUM-index and support for bigint as part of index

[Andreas Joseph Krogh]

På torsdag 25. august 2016 kl. 18:12:34, skrev Oleg Bartunov <
obartu...@gmail.com >:
Andreas,
  
sorry for delay,
it looks like a bug to me, could you please, share your dataset with me, so I 
could reproduce the behaviour.

 
 
I'll send you a Google Drive link on your email
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Updated RUM-index and support for bigint as part of index

[Oleg Bartunov]

Andreas,

sorry for delay,
it looks like a bug to me, could you please, share your dataset with me, so
I could reproduce the behaviour.

Regards,
Oleg

On Sun, Aug 7, 2016 at 11:05 AM, Andreas Joseph Krogh 
wrote:

> På søndag 07. august 2016 kl. 08:27:06, skrev Oleg Bartunov <
> obartu...@gmail.com>:
>
> [snip]
> have you considered <=| and |=> operators ? <=> in ORDER BY works like KNN.
>
>
> I don't get how these operators should work. Neither give me the expected
> results.
>
> Using <=>
>
>
> SELECTdel.entity_id,
> del.folder_id,
> del.received_timestampFROM origo_email_delivery delWHERE del.fts_all @@ 
> to_tsquery('simple', 'andreas:*:*')ORDER BY '2000-01-01' :: TIMESTAMP 
> <=> del.received_timestampLIMIT 10;
>
>  entity_id | folder_id |   received_timestamp
> ---+---+-
>   1224278 |   1068087 | 2015-08-17 23:53:26
>   1224382 |   1068087 | 2015-08-18 03:07:55
>   1224404 |   1068087 | 2015-08-18 03:49:02
>   1505713 | 48496 | 2015-10-27 14:51:45
>142132 | 66658 | 2012-12-03 14:14:05.488
>122565 | 90115 | 2012-11-20 15:41:04.936
>200744 | 66655 | 2013-01-28 21:47:44.561
>   1445927 |888665 | 2015-09-29 00:26:56
>123671 | 83509 | 2012-11-21 14:16:26.448
>   1129928 | 66658 | 2015-05-09 08:39:14.128
> (10 rows)
>
>
> Using <=|
>
> SELECTdel.entity_id,
> del.folder_id,
> del.received_timestampFROM origo_email_delivery delWHERE del.fts_all @@ 
> to_tsquery('simple', 'andreas:*:*')ORDER BY '2000-01-01' :: TIMESTAMP 
> <=| del.received_timestampLIMIT 10;
>
>
>  entity_id | folder_id |   received_timestamp
> ---+---+-
>   1224278 |   1068087 | 2015-08-17 23:53:26
>   1224382 |   1068087 | 2015-08-18 03:07:55
>   1224404 |   1068087 | 2015-08-18 03:49:02
>   1505713 | 48496 | 2015-10-27 14:51:45
>142132 | 66658 | 2012-12-03 14:14:05.488
>122565 | 90115 | 2012-11-20 15:41:04.936
>200744 | 66655 | 2013-01-28 21:47:44.561
>   1445927 |888665 | 2015-09-29 00:26:56
>123671 | 83509 | 2012-11-21 14:16:26.448
>   1129928 | 66658 | 2015-05-09 08:39:14.128
> (10 rows)
>
>
> Neither are ordered by received_timestamp
>
> Can you explain how to get ORDER BY received_timestamp DESC?
>
> Thanks.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> 
>
>

Re: [GENERAL] Updated RUM-index and support for bigint as part of index

[Andreas Joseph Krogh]

På torsdag 11. august 2016 kl. 19:13:10, skrev Artur Zakirov <
a.zaki...@postgrespro.ru >:
On 07.08.2016 11:05, Andreas Joseph Krogh wrote:
 > På søndag 07. august 2016 kl. 08:27:06, skrev Oleg Bartunov
 > >:
 >
 >     [snip]
 >     have you considered <=| and |=> operators ? <=> in ORDER BY works
 >     like KNN.
 >
 >
 > I don't get how these operators should work. Neither give me the
 > expected results.
 >
 > Using <=>
 >
 >
 > SELECT del.entity_id, del.folder_id, del.received_timestamp FROM
 > origo_email_delivery del WHERE del.fts_all @@ to_tsquery('simple',
 > 'andreas:*:*') ORDER BY '2000-01-01' :: TIMESTAMP <=>
 > del.received_timestamp LIMIT 10;
 >
 >  entity_id | folder_id |   received_timestamp
 > ---+---+-
 >   1224278 |   1068087 | 2015-08-17 23:53:26
 >   1224382 |   1068087 | 2015-08-18 03:07:55
 >   1224404 |   1068087 | 2015-08-18 03:49:02
 >   1505713 |     48496 | 2015-10-27 14:51:45
 >    142132 |     66658 | 2012-12-03 14:14:05.488
 >    122565 |     90115 | 2012-11-20 15:41:04.936
 >    200744 |     66655 | 2013-01-28 21:47:44.561
 >   1445927 |    888665 | 2015-09-29 00:26:56
 >    123671 |     83509 | 2012-11-21 14:16:26.448
 >   1129928 |     66658 | 2015-05-09 08:39:14.128
 > (10 rows)
 >
 >
 > Using <=|
 >
 > SELECT del.entity_id, del.folder_id, del.received_timestamp FROM
 > origo_email_delivery del WHERE del.fts_all @@ to_tsquery('simple',
 > 'andreas:*:*') ORDER BY '2000-01-01' :: TIMESTAMP <=|
 > del.received_timestamp LIMIT 10;
 >
 >
 >  entity_id | folder_id |   received_timestamp
 > ---+---+-
 >   1224278 |   1068087 | 2015-08-17 23:53:26
 >   1224382 |   1068087 | 2015-08-18 03:07:55
 >   1224404 |   1068087 | 2015-08-18 03:49:02
 >   1505713 |     48496 | 2015-10-27 14:51:45
 >    142132 |     66658 | 2012-12-03 14:14:05.488
 >    122565 |     90115 | 2012-11-20 15:41:04.936
 >    200744 |     66655 | 2013-01-28 21:47:44.561
 >   1445927 |    888665 | 2015-09-29 00:26:56
 >    123671 |     83509 | 2012-11-21 14:16:26.448
 >   1129928 |     66658 | 2015-05-09 08:39:14.128
 > (10 rows)
 >
 >
 > Neither are ordered by received_timestamp
 >
 > Can you explain how to get ORDER BY received_timestamp DESC?
 >
 > Thanks.
 >
 > --
 > *Andreas Joseph Krogh*
 > CTO / Partner - Visena AS
 > Mobile: +47 909 56 963
 > andr...@visena.com 
 > www.visena.com 
 > 
 >

 Do you need simple ordering by received_timestamp column? Not ordering
 by distance between received_timestamp and some date?

 Then you can use simple "ORDER BY received_timestamp". For example, we
 have data:

 =# SELECT * FROM test;
   id |     fts     |        received
 +-+-
    1 | 'andreas':1 | 2015-08-17 23:53:26
    2 | 'andreas':1 | 2015-08-18 03:07:55
    3 | 'andreas':1 | 2015-08-18 03:49:02
    4 | 'andreas':1 | 2012-12-03 14:14:05.488
    5 | 'andreas':1 | 2012-11-20 15:41:04.936
    6 | 'andreas':1 | 2013-01-28 21:47:44.561
    6 | 'andreas':1 | 2015-09-29 00:26:56
    7 | 'andreas':1 | 2012-11-21 14:16:26.448
    8 | 'andreas':1 | 2015-05-09 08:39:14.128
 (9 rows)

 I created index:

 CREATE INDEX rumidx ON test USING rum (fts rum_tsvector_timestamp_ops,
 received) WITH (attach = 'received', to = 'fts');

 Then we can execute queries:

 =# SELECT id, received FROM test WHERE fts @@ to_tsquery('simple',
 'andreas') ORDER BY received LIMIT 8;
   id |        received
 +-
    5 | 2012-11-20 15:41:04.936
    7 | 2012-11-21 14:16:26.448
    4 | 2012-12-03 14:14:05.488
    6 | 2013-01-28 21:47:44.561
    8 | 2015-05-09 08:39:14.128
    1 | 2015-08-17 23:53:26
    2 | 2015-08-18 03:07:55
    3 | 2015-08-18 03:49:02
 (8 rows)

 =# SELECT id, received FROM test WHERE fts @@ to_tsquery('simple',
 'andreas') ORDER BY received DESC LIMIT 8;
   id |        received
 +-
    6 | 2015-09-29 00:26:56
    3 | 2015-08-18 03:49:02
    2 | 2015-08-18 03:07:55
    1 | 2015-08-17 23:53:26
    8 | 2015-05-09 08:39:14.128
    6 | 2013-01-28 21:47:44.561
    4 | 2012-12-03 14:14:05.488
    7 | 2012-11-21 14:16:26.448
 (8 rows)
 
Yes, this gives the correct result, but the whole motivation for using 
RUM-index is for the query to use the same index for ORDER BY, as it seems to 
do using the <=> operator.
 
The query you gave above does not the index for sorting AFAIU.
 
Operators <=>, |=>, <=| you can use to order by nearest date to specific
 date:
 [snip]
 
I hope this is what you want.
 
I still don't understand how my query which had ORDER BY '2000-01-01' :: 
TIMESTAMP <=> del.received_timestamp
can produce the following ordering:
 
 entity_id | folder_id |   received_timestamp 
---+---+-
  1224278 |   1068087 | 2015-08-17 23:53:26
  1224382 |   1068087 | 

Re: [GENERAL] Updated RUM-index and support for bigint as part of index

[Artur Zakirov]

On 07.08.2016 11:05, Andreas Joseph Krogh wrote:

På søndag 07. august 2016 kl. 08:27:06, skrev Oleg Bartunov
>:

[snip]
have you considered <=| and |=> operators ? <=> in ORDER BY works
like KNN.


I don't get how these operators should work. Neither give me the
expected results.

Using <=>


SELECT del.entity_id, del.folder_id, del.received_timestamp FROM
origo_email_delivery del WHERE del.fts_all @@ to_tsquery('simple',
'andreas:*:*') ORDER BY '2000-01-01' :: TIMESTAMP <=>
del.received_timestamp LIMIT 10;

 entity_id | folder_id |   received_timestamp
---+---+-
  1224278 |   1068087 | 2015-08-17 23:53:26
  1224382 |   1068087 | 2015-08-18 03:07:55
  1224404 |   1068087 | 2015-08-18 03:49:02
  1505713 | 48496 | 2015-10-27 14:51:45
   142132 | 66658 | 2012-12-03 14:14:05.488
   122565 | 90115 | 2012-11-20 15:41:04.936
   200744 | 66655 | 2013-01-28 21:47:44.561
  1445927 |888665 | 2015-09-29 00:26:56
   123671 | 83509 | 2012-11-21 14:16:26.448
  1129928 | 66658 | 2015-05-09 08:39:14.128
(10 rows)


Using <=|

SELECT del.entity_id, del.folder_id, del.received_timestamp FROM
origo_email_delivery del WHERE del.fts_all @@ to_tsquery('simple',
'andreas:*:*') ORDER BY '2000-01-01' :: TIMESTAMP <=|
del.received_timestamp LIMIT 10;


 entity_id | folder_id |   received_timestamp
---+---+-
  1224278 |   1068087 | 2015-08-17 23:53:26
  1224382 |   1068087 | 2015-08-18 03:07:55
  1224404 |   1068087 | 2015-08-18 03:49:02
  1505713 | 48496 | 2015-10-27 14:51:45
   142132 | 66658 | 2012-12-03 14:14:05.488
   122565 | 90115 | 2012-11-20 15:41:04.936
   200744 | 66655 | 2013-01-28 21:47:44.561
  1445927 |888665 | 2015-09-29 00:26:56
   123671 | 83509 | 2012-11-21 14:16:26.448
  1129928 | 66658 | 2015-05-09 08:39:14.128
(10 rows)


Neither are ordered by received_timestamp

Can you explain how to get ORDER BY received_timestamp DESC?

Thanks.

--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 




Do you need simple ordering by received_timestamp column? Not ordering 
by distance between received_timestamp and some date?


Then you can use simple "ORDER BY received_timestamp". For example, we 
have data:


=# SELECT * FROM test;
 id | fts |received
+-+-
  1 | 'andreas':1 | 2015-08-17 23:53:26
  2 | 'andreas':1 | 2015-08-18 03:07:55
  3 | 'andreas':1 | 2015-08-18 03:49:02
  4 | 'andreas':1 | 2012-12-03 14:14:05.488
  5 | 'andreas':1 | 2012-11-20 15:41:04.936
  6 | 'andreas':1 | 2013-01-28 21:47:44.561
  6 | 'andreas':1 | 2015-09-29 00:26:56
  7 | 'andreas':1 | 2012-11-21 14:16:26.448
  8 | 'andreas':1 | 2015-05-09 08:39:14.128
(9 rows)

I created index:

CREATE INDEX rumidx ON test USING rum (fts rum_tsvector_timestamp_ops, 
received) WITH (attach = 'received', to = 'fts');


Then we can execute queries:

=# SELECT id, received FROM test WHERE fts @@ to_tsquery('simple', 
'andreas') ORDER BY received LIMIT 8;

 id |received
+-
  5 | 2012-11-20 15:41:04.936
  7 | 2012-11-21 14:16:26.448
  4 | 2012-12-03 14:14:05.488
  6 | 2013-01-28 21:47:44.561
  8 | 2015-05-09 08:39:14.128
  1 | 2015-08-17 23:53:26
  2 | 2015-08-18 03:07:55
  3 | 2015-08-18 03:49:02
(8 rows)

=# SELECT id, received FROM test WHERE fts @@ to_tsquery('simple', 
'andreas') ORDER BY received DESC LIMIT 8;

 id |received
+-
  6 | 2015-09-29 00:26:56
  3 | 2015-08-18 03:49:02
  2 | 2015-08-18 03:07:55
  1 | 2015-08-17 23:53:26
  8 | 2015-05-09 08:39:14.128
  6 | 2013-01-28 21:47:44.561
  4 | 2012-12-03 14:14:05.488
  7 | 2012-11-21 14:16:26.448
(8 rows)

Operators <=>, |=>, <=| you can use to order by nearest date to specific 
date:


=# SELECT id, received, received <=> '2013-01-01' AS rank FROM test 
WHERE fts @@ to_tsquery('simple', 'andreas') ORDER BY received <=> 
'2013-01-01' LIMIT 8;

 id |received | rank
+-+--
  6 | 2013-01-28 21:47:44.561 |  2411264.561
  4 | 2012-12-03 14:14:05.488 |  2454354.512
  7 | 2012-11-21 14:16:26.448 |  3491013.552
  5 | 2012-11-20 15:41:04.936 |  3572335.064
  8 | 2015-05-09 08:39:14.128 | 74162354.128
  1 | 2015-08-17 23:53:26 | 82857206
  2 | 2015-08-18 03:07:55 | 82868875
  3 | 2015-08-18 03:49:02 | 82871342
(8 rows)

=# SELECT id, received, received <=> '2013-01-01' AS rank FROM test 
WHERE fts @@ to_tsquery('simple', 'andreas') ORDER BY received <=> 
'2013-01-01' DESC LIMIT 8;

 id |received | rank
+-+--
  6 | 2015-09-29 00:26:56 | 86488016
  3 | 2015-08-18 03:49:02 | 82871342
  2 | 2015-08-18 03:07:55 |  

Re: [GENERAL] Updated RUM-index and support for bigint as part of index

[Andreas Joseph Krogh]

På søndag 07. august 2016 kl. 08:27:06, skrev Oleg Bartunov >:
[snip]
have you considered <=| and |=> operators ? <=> in ORDER BY works like KNN.



 
I don't get how these operators should work. Neither give me the expected 
results.
 
Using <=>
 
SELECT del.entity_id, del.folder_id, del.received_timestamp FROM 
origo_email_delivery delWHERE del.fts_all @@ to_tsquery('simple', 
'andreas:*:*') ORDER BY '2000-01-01' :: TIMESTAMP <=> 
del.received_timestampLIMIT 10; 
 entity_id | folder_id |   received_timestamp 
 ---+---+-
   1224278 |   1068087 | 2015-08-17 23:53:26
   1224382 |   1068087 | 2015-08-18 03:07:55
   1224404 |   1068087 | 2015-08-18 03:49:02
   1505713 | 48496 | 2015-10-27 14:51:45
    142132 | 66658 | 2012-12-03 14:14:05.488
    122565 | 90115 | 2012-11-20 15:41:04.936
    200744 | 66655 | 2013-01-28 21:47:44.561
   1445927 |    888665 | 2015-09-29 00:26:56
    123671 | 83509 | 2012-11-21 14:16:26.448
   1129928 | 66658 | 2015-05-09 08:39:14.128
 (10 rows)

  
Using <=|
SELECT del.entity_id, del.folder_id, del.received_timestamp FROM 
origo_email_delivery delWHERE del.fts_all @@ to_tsquery('simple', 
'andreas:*:*') ORDER BY '2000-01-01' :: TIMESTAMP <=| 
del.received_timestampLIMIT 10; 
 
 entity_id | folder_id |   received_timestamp 
 ---+---+-
   1224278 |   1068087 | 2015-08-17 23:53:26
   1224382 |   1068087 | 2015-08-18 03:07:55
   1224404 |   1068087 | 2015-08-18 03:49:02
   1505713 | 48496 | 2015-10-27 14:51:45
    142132 | 66658 | 2012-12-03 14:14:05.488
    122565 | 90115 | 2012-11-20 15:41:04.936
    200744 | 66655 | 2013-01-28 21:47:44.561
   1445927 |    888665 | 2015-09-29 00:26:56
    123671 | 83509 | 2012-11-21 14:16:26.448
   1129928 | 66658 | 2015-05-09 08:39:14.128
 (10 rows)

  
Neither are ordered by received_timestamp
 
Can you explain how to get ORDER BY received_timestamp DESC?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Updated RUM-index and support for bigint as part of index

[Andreas Joseph Krogh]

På lørdag 06. august 2016 kl. 20:54:32, skrev Artur Zakirov <
a.zaki...@postgrespro.ru >:
Hello,   2016-08-02 21:08 GMT+03:00 Andreas Joseph Krogh >:  The ORDER BY part seems strange; It seems one 
has to find a value "lower than any other value" to use as a kind of base, why 
is this necessary? It also seems that in order to be able to sort DESC one has 
to provide a timestamp value "higher than any other value", is this correct? 
 
It would be great if the docs explained this.
 
We will write more detailed documentation for RUM.



 
Great!
 
 
I really miss the opportunity to include a BIGINT as part of the index, so 
that the WHERE-clause could be like this:
 
WHERE del.fts_all @@ to_tsquery('simple', 'andreas') AND del.folder_id IN (1
,2,3)
 
Having this would be perfect for my use-case searching in email in folders, 
sorted by received_date, and having it use ONE index.
 
Will this be supported?


 We have a plan to use generic types to able to include bigint, timestamp and 
other types as part of index.

 
Does this eliminate the need for a btree_rum equivalent of btree_gin, being 
that the RUM-index will handle all "btree-able" datatypes?
 
 
But I cant tell date of it.


 
I understand.
Do you think it will be done by the time 9.6 is released?
 
Thanks.
 

-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Updated RUM-index and support for bigint as part of index

[Oleg Bartunov]

On Tue, Aug 2, 2016 at 9:08 PM, Andreas Joseph Krogh 
wrote:

> Hi.
>
> I see the RUM-index is updated, which is great!
>
> I wonder, to be able to sort by timestamp one has to create the index like
> this:
>
>
> CREATE INDEX rumidx ON origo_email_delivery USING rum (fts_all 
> rum_tsvector_timestamp_ops, received_timestamp)
> WITH (attach = 'received_timestamp', TO = 'fts_all', order_by_attach = 
> TRUE );
>
> Then, to be able to use the index for sorting by the
> "received_timestamp"-column one has to issue a query like this:
>
> EXPLAIN ANALYZE SELECTdel.entity_id,
> del.subject,
> del.received_timestamp,
> fts_all <=> to_tsquery('simple', 'andreas') AS rank
> FROM origo_email_delivery del
> WHERE del.fts_all @@ to_tsquery('simple', 'andreas')
> ORDER BY '2000-01-01' :: TIMESTAMP <=> del.received_timestamp
> LIMIT 10;
>
> QUERY 
> PLAN
> --
>  Limit  (cost=14.40..26.47 rows=10 width=89) (actual time=10.908..10.952 
> rows=10 loops=1)
>->  Index Scan using rumidx on origo_email_delivery del  
> (cost=14.40..3221.22 rows=2657 width=89) (actual time=10.906..10.947 rows=10 
> loops=1)
>  Index Cond: (fts_all @@ '''andreas'' & ''kr'''::tsquery)
>  Order By: (received_timestamp <=> '2000-01-01 00:00:00'::timestamp 
> without time zone)
>  Planning time: 0.491 ms
>  Execution time: 11.010 ms
> (6 rows)
>
>
> The ORDER BY part seems strange; It seems one has to find a value
> "lower than any other value" to use as a kind of base, why is this
> necessary? It also seems that in order to be able to sort DESC one has to
> provide a timestamp value "higher than any other value", is this correct?
>

have you considered <=| and |=> operators ? <=> in ORDER BY works like KNN.


>
> It would be great if the docs explained this.
>
> I really miss the opportunity to include a BIGINT as part of the index, so
> that the WHERE-clause could be like this:
>
> WHERE del.fts_all @@ to_tsquery('simple', 'andreas') AND del.folder_id IN
> (1,2,3)
>
> Having this would be perfect for my use-case searching in email in
> folders, sorted by received_date, and having it use ONE index.
>
> Will this be supported?
>
> Thanks.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> 
>

Re: [GENERAL] Updated RUM-index and support for bigint as part of index

[Artur Zakirov]

Hello,

2016-08-02 21:08 GMT+03:00 Andreas Joseph Krogh :
>
> The ORDER BY part seems strange; It seems one has to find a value "lower than 
> any other value" to use as a kind of base, why is this necessary? It also 
> seems that in order to be able to sort DESC one has to provide a timestamp 
> value "higher than any other value", is this correct?
>
>
> It would be great if the docs explained this.
>

We will write more detailed documentation for RUM.


>
> I really miss the opportunity to include a BIGINT as part of the index, so
> that the WHERE-clause could be like this:
>
> WHERE del.fts_all @@ to_tsquery('simple', 'andreas') AND del.folder_id IN
> (1,2,3)
>
> Having this would be perfect for my use-case searching in email in
> folders, sorted by received_date, and having it use ONE index.
>
> Will this be supported?
>

We have a plan to use generic types to able to include bigint, timestamp
and other types as part of index. But I cant tell date of it.

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

[GENERAL] Updated RUM-index and support for bigint as part of index

[Andreas Joseph Krogh]

Hi.
 
I see the RUM-index is updated, which is great!
 
I wonder, to be able to sort by timestamp one has to create the index like 
this:
 
CREATE INDEX rumidx ON origo_email_delivery USING rum (fts_all 
rum_tsvector_timestamp_ops, received_timestamp)WITH (attach = 
'received_timestamp', TO = 'fts_all', order_by_attach = TRUE ); 
Then, to be able to use the index for sorting by the 
"received_timestamp"-column one has to issue a query like this:
EXPLAIN ANALYZE SELECT del.entity_id, del.subject, del.received_timestamp, 
fts_all <=>to_tsquery('simple', 'andreas') AS rank FROM origo_email_delivery 
delWHERE del.fts_all @@ to_tsquery('simple', 'andreas') ORDER BY '2000-01-01'
::TIMESTAMP <=> del.received_timestamp LIMIT 10; 
QUERY PLAN 
--
Limit(cost=14.40..26.47 rows=10 width=89) (actual time=10.908..10.952 rows=10 
loops=1) -> Index Scan using rumidx on origo_email_delivery del (cost
=14.40..3221.22rows=2657 width=89) (actual time=10.906..10.947 rows=10 loops=1) 
IndexCond: (fts_all @@ '''andreas'' & ''kr'''::tsquery) Order By: 
(received_timestamp <=>'2000-01-01 00:00:00'::timestamp without time zone) 
Planningtime: 0.491 ms Execution time: 11.010 ms (6 rows) 
 
The ORDER BY part seems strange; It seems one has to find a value "lower than 
any other value" to use as a kind of base, why is this necessary? It also seems 
that in order to be able to sort DESC one has to provide a timestamp value 
"higher than any other value", is this correct?
 
It would be great if the docs explained this.
 
I really miss the opportunity to include a BIGINT as part of the index, so 
that the WHERE-clause could be like this:
 
WHERE del.fts_all @@ to_tsquery('simple', 'andreas') AND del.folder_id IN (1
,2,3)
 
Having this would be perfect for my use-case searching in email in folders, 
sorted by received_date, and having it use ONE index.
 
Will this be supported?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: [GENERAL] Updated RUM?

[Andreas Joseph Krogh]

På mandag 27. juni 2016 kl. 13:20:03, skrev Oleg Bartunov >:
    On Fri, Jun 24, 2016 at 2:36 PM, Andreas Joseph Krogh > wrote: Hi.
 
@PostgresPro; Will there be any updates to the rum git-repo any time soon?
 
Sure.
  
 
I understand you're working hard on this, care to share some vision/roadmap? 
I'm specifically interested in if RUM will support the same datatypes as GIN 
supports (arrays, JSON etc.), and what opclasses you plan to implement (for 
equalty and sorting). Will there be some kind of btree_rum like there is for 
btree_gin, to add RUM-support for the types there exists Btree-support for?
 
We are testing fts+timestamp combination right now.  The challenge is to have 
posting list/tree sorted by addinfo (timestamp) not by item pointer. It works 
already, but we are not quite satisfied with generated WAL traffic and are 
researching some options in improving posting tree building and generic wal.
  
We have in mind some sort of generalization like anyarray, but don't have any 
real ideas.



 
Cool!
 
Will RUM support having ts_vector and bigint in the same index, like GIN does 
(utilizine btree_gin), then sorting by some 3rd timestamp-column?
 
This will enable queries like:
 
create table delivery( id BIGSERIAL primary key, fts_all TSVECTOR not null, 
folder_idBIGINT NOT NULL, sent TIMESTAMP not null, message varchar not null ); 
 
SELECT del.id  , del.sent FROM delivery del WHERE del.fts_all 
@@to_tsquery('simple', 'hi:*') AND del.folder_id = ANY(ARRAY[2,3]::BIGINT[]) 
ORDER BYdel.sent DESC LIMIT 101 OFFSET 0; Having this query use one RUM-index 
with optimized sorting by timestamp would be extremely useful.

 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Updated RUM?

[Oleg Bartunov]

On Fri, Jun 24, 2016 at 2:36 PM, Andreas Joseph Krogh 
wrote:

> Hi.
>
> @PostgresPro; Will there be any updates to the rum git-repo any time soon?
>

Sure.


>
> I understand you're working hard on this, care to share some
> vision/roadmap? I'm specifically interested in if RUM will support the same
> datatypes as GIN supports (arrays, JSON etc.), and what opclasses you plan
> to implement (for equalty and sorting). Will there be some kind of
> btree_rum like there is for btree_gin, to add RUM-support for the types
> there exists Btree-support for?
>

We are testing fts+timestamp combination right now.  The challenge is to
have posting list/tree sorted by addinfo (timestamp) not by item pointer.
It works already, but we are not quite satisfied with generated WAL traffic
and are researching some options in improving posting tree building and
generic wal.

We have in mind some sort of generalization like anyarray, but don't have
any real ideas.



>
> Thanks.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> 
>

[GENERAL] Updated RUM?

[Andreas Joseph Krogh]

Hi.
 
@PostgresPro; Will there be any updates to the rum git-repo any time soon?
 
I understand you're working hard on this, care to share some vision/roadmap? 
I'm specifically interested in if RUM will support the same datatypes as GIN 
supports (arrays, JSON etc.), and what opclasses you plan to implement (for 
equalty and sorting). Will there be some kind of btree_rum like there is for 
btree_gin, to add RUM-support for the types there exists Btree-support for?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com