Re: Question about RUM-index

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 table delivery(   id BIGSERIAL primary key,   fts_all TSVECTOR not null,   folder_id BIGINT 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 FUNCTION update_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 delivery
FOR EACH ROW EXECUTE PROCEDURE update_delivery_tsvector_tf();

insert into delivery(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.sent               FROM 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.sent DESC 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 in an 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?