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

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; 

 

 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

andreas@visena.com

www.visena.com