Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux