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
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
