Home > mailing lists

Re: Updated RUM-index and support for bigint as part of index - Mailing list pgsql-general

From	Andreas Joseph Krogh
Subject	Re: Updated RUM-index and support for bigint as part of index
Date	August 7, 2016 11:05:58
Msg-id	VisenaEmail.72.6511f5c5af6d2a29.156640575f8@tc7-visena Whole thread Raw
In response to	Re: Updated RUM-index and support for bigint as part of index (Oleg Bartunov <obartunov@gmail.com>)
Responses	Re: Updated RUM-index and support for bigint as part of index Re: Updated RUM-index and support for bigint as part of index
List	pgsql-general

Tree view

På søndag 07. august 2016 kl. 08:27:06, skrev Oleg Bartunov <obartunov@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 <=>

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