Question about RUM-index

Поиск
Список
Период
Сортировка
От Andreas Joseph Krogh
Тема Question about RUM-index
Дата
Msg-id VisenaEmail.2.50828cf902439c14.15553aab226@tc7-visena
обсуждение исходный текст
Ответы Re: Question about RUM-index  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: Question about RUM-index  (Jeff Janes <jeff.janes@gmail.com>)
Список 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 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?
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Martín Marqués
Дата:
Сообщение: Re: PgQ and pg_dump
Следующее
От: Durgamahesh Manne
Дата:
Сообщение: regarding schema only migration from sqlserver to postgres with runmtk.sh