Question about RUM-index

From: Andreas Joseph Krogh <andreas(at)visena(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Question about RUM-index
Date: 2016-06-15 10:56:09
Message-ID: VisenaEmail.2.50828cf902439c14.15553aab226@tc7-visena
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi.
 
First; Is this the correct forum to ask questions about the Postgres Pro's new
RUM-index?
 
If not, please point me to the right forum.
 
I'm trying to do this (done with GIN):
 
create extension if not exists btree_gin; drop table if EXISTS delivery;
create tabledelivery( id BIGSERIAL primary key, fts_all TSVECTOR not null,
folder_idBIGINT NOT NULL, sent TIMESTAMP not null, message varchar not null );
create index gin_idx on delivery using GIN(fts_all, folder_id); CREATE OR
REPLACE FUNCTIONupdate_delivery_tsvector_tf() RETURNS TRIGGER AS $$ BEGIN
NEW.fts_all =to_tsvector('simple', NEW.message); return NEW; END; $$ LANGUAGE
PLPGSQL;CREATE TRIGGER update_delivery_tsvector_t BEFORE INSERT OR UPDATE ON
deliveryFOR EACH ROW EXECUTE PROCEDURE update_delivery_tsvector_tf(); insert
intodelivery(folder_id, sent, message) values (1, '2015-01-01', 'Yes hit four')
, (1, '2014-01-01', 'Hi man') , (2, '2013-01-01', 'Hi man') , (2, '2013-01-01',
'fish') ; analyze delivery; set ENABLE_SEQSCAN to off; explain analyze SELECT
del.id , del.sentFROM delivery del WHERE 1 = 1 AND del.fts_all @@ to_tsquery(
'simple', 'hi:*') AND del.folder_id = ANY(ARRAY[2,3]::BIGINT[]) ORDER BY
del.sentDESC LIMIT 101 OFFSET 0;
 
                                                         QUERY PLAN
                                                         

----------------------------------------------------------------------------------------------------------------------------
Limit  (cost=3.63..3.63 rows=1 width=16) (actual time=0.025..0.025 rows=1
loops=1)
  ->  Sort  (cost=3.63..3.63 rows=1 width=16) (actual time=0.024..0.024
rows=1 loops=1)
        Sort Key: sent DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Bitmap Heap Scan on delivery del  (cost=2.40..3.62 rows=1
width=16) (actual time=0.019..0.019 rows=1 loops=1)
              Recheck Cond: ((fts_all @@ '''hi'':*'::tsquery) AND (folder_id
= ANY ('{2,3}'::bigint[])))
              Heap Blocks: exact=1
              ->  Bitmap Index Scan on gin_idx  (cost=0.00..2.40 rows=1
width=0) (actual time=0.015..0.015 rows=1 loops=1)
                    Index Cond: ((fts_all @@ '''hi'':*'::tsquery) AND
(folder_id = ANY ('{2,3}'::bigint[])))
Planning time: 0.153 ms
Execution time: 0.047 ms
(11 rows)

 
Note that GIN does almost what I want, except use the index when sorting by
"sent"-timestamp.
 
So I wonder if RUM can do any better?
What I don't understand is how to have "folder_id" as part of the RUM-index so
that I can search inan array of folders using the index, AND have the whole
result sorted by "sent"-timestamp also using the RUM-index.
 
In the (limited) documentation sorting using timestamp is done like this:
 
ORDER BY sent <-> '2000-01-01'::TIMESTAMP
 
which I don't understand; Why must one specify a value here, and how does that
value affect the result?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Durgamahesh Manne 2016-06-15 12:31:26 regarding schema only migration from sqlserver to postgres with runmtk.sh
Previous Message Martín Marqués 2016-06-15 10:19:49 Re: PgQ and pg_dump