The rum module provides access
method to work with the RUM indexes. It is based
on the GIN access methods code.
GIN index allows to perform fast full text search
using tsvector and tsquery types.
But full text search with GIN index has several problems:
Slow ranking. The positional information is needed for ranking.
GIN index doesn't store positions of lexems.
So after index scan we need an additional heap scan to retrieve
lexems positions.
Slow phrase search with GIN index. This problem
related with the previous problem. The positional information
is needed to perform phrase search.
Slow ordering by timestamp. GIN index can't store
some related information in index with lexemes.
So it is necessary to perform an additional heap scan.
RUM solves these problems by storing additional
information in posting tree. In particular, it stores positional
information of lexemes or timestamps.
Drawback of RUM is that it has slower build and
insert time than GIN.
It is because we need to store additional information besides keys and
because RUM uses generic WAL.
rum is a Postgres Pro Enterprise
extension and it has no special prerequisites.
Install extension as follows:
$ psql dbname -c "CREATE EXTENSION rum"
The operators provided by the rum module shown
in Table F.40:
Table F.40. rum Operators
| Operator | Returns | Description |
|---|---|---|
tsvector <=> tsquery | float4 | Returns distance between tsvector and tsquery values. |
timestamp <=> timestamp | float8 | Returns distance between two timestamp values. |
timestamp <=| timestamp | float8 | Returns distance only for ascending timestamp values. |
timestamp |=> timestamp | float8 | Returns distance only for descending timestamp values. |
The rum extension provides the following operator classes.
rum_tsvector_ops — operator class
for tsvector
This operator class stores tsvector lexemes with
positional information.
Supports ordering by <=> operator and prefix search.
Here is an example.
Let us assume we have the table:
CREATE TABLE test_rum(t text, a tsvector);
CREATE TRIGGER tsvectorupdate
BEFORE UPDATE OR INSERT ON test_rum
FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('a', 'pg_catalog.english', 't');
INSERT INTO test_rum(t) VALUES ('The situation is most beautiful');
INSERT INTO test_rum(t) VALUES ('It is a beautiful');
INSERT INTO test_rum(t) VALUES ('It looks like a beautiful place');
To create the rum index we need create an extension:
CREATE EXTENSION rum;
Then we can create a new index:
CREATE INDEX rumidx ON test_rum USING rum (a rum_tsvector_ops);
And we can execute the following queries:
SELECT t, a <=> to_tsquery('english', 'beautiful | place') AS rank
FROM test_rum
WHERE a @@ to_tsquery('english', 'beautiful | place')
ORDER BY a <=> to_tsquery('english', 'beautiful | place');
t | rank
---------------------------------+-----------
The situation is most beautiful | 0.0303964
It is a beautiful | 0.0303964
It looks like a beautiful place | 0.0607927
(3 rows)
SELECT t, a <=> to_tsquery('english', 'place | situation') AS rank
FROM test_rum
WHERE a @@ to_tsquery('english', 'place | situation')
ORDER BY a <=> to_tsquery('english', 'place | situation');
t | rank
---------------------------------+-----------
The situation is most beautiful | 0.0303964
It looks like a beautiful place | 0.0303964
(2 rows)
rum_tsvector_hash_ops — operator class
for hashes of tsvector
This operator class stores hash of tsvector lexemes
with positional information.
Supports ordering by <=> operator.
But doesn't support prefix search.
rum_timestamp_ops — operator class
for timestamp
This operator class provides fast search and ordering by
timestamp fields.
Supports ordering by <=>,
<=|,and |=> operators.
Can be used with rum_tsvector_timestamp_ops
operator class.
rum_timestamptz_ops — operator class
for timestamptz
This operator class provides fast search and ordering by
timestamptz fields.
Supports ordering by <=>,
<=|, and |=> operators.
Can be used with rum_tsvector_timestamptz_ops
operator class.
rum_tsvector_timestamp_ops — operator class
for tsvector with timestamp
This operator class stores tsvector lexems with the timestamp field.
Here is an example.
Let us assume we have the table:
CREATE TABLE tsts (id int, t tsvector, d timestamp);
\copy tsts from 'rum/data/tsts.data'
CREATE INDEX tsts_idx ON tsts USING rum (t rum_tsvector_timestamp_ops, d)
WITH (attach = 'd', to = 't');
Now we can execute the following queries:
EXPLAIN (costs off)
SELECT id, d, d <=> '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY d <=> '2016-05-16 14:21:25' LIMIT 5;
QUERY PLAN
-----------------------------------------------------------------------------------
Limit
-> Index Scan using tsts_idx on tsts
Index Cond: (t @@ '''wr'' & ''qh'''::tsquery)
Order By: (d <=> 'Mon May 16 14:21:25 2016'::timestamp without time zone)
(4 rows)
SELECT id, d, d <=> '2016-05-16 14:21:25' FROM tsts WHERE t @@ 'wr&qh' ORDER BY d <=> '2016-05-16 14:21:25' LIMIT 5;
id | d | ?column?
-----+---------------------------------+---------------
355 | Mon May 16 14:21:22.326724 2016 | 2.673276
354 | Mon May 16 13:21:22.326724 2016 | 3602.673276
371 | Tue May 17 06:21:22.326724 2016 | 57597.326724
406 | Wed May 18 17:21:22.326724 2016 | 183597.326724
415 | Thu May 19 02:21:22.326724 2016 | 215997.326724
(5 rows)
rum_tsvector_timestamptz_ops — operator class
for tsvector with timestamptz
See description of the rum_tsvector_timestamp_ops operator class.
rum_tsvector_hash_timestamp_ops — operator class
for hashes of tsvector
This operator class stores hash of tsvector lexems with
timestamp field. Doesn't support prefix search.
rum_tsvector_hash_timestamptz_ops — operator class
for hashes of tsvector
This operator class stores hash of tsvector lexems with
timestamptz field. Doesn't support prefix search.
rum_tsquery_ops — operator class
for tsquery
Stores branches of query tree in additional information. For example, if we have the table:
CREATE TABLE query (q tsquery, tag text);
INSERT INTO query VALUES ('supernova & star', 'sn'),
('black', 'color'),
('big & bang & black & hole', 'bang'),
('spiral & galaxy', 'shape'),
('black & hole', 'color');
CREATE INDEX query_idx ON query USING rum(q);
We can execute the following fast query:
SELECT * FROM query
WHERE to_tsvector('black holes never exists before we think about them') @@ q;
q | tag
------------------+-------
'black' | color
'black' & 'hole' | color
(2 rows)
This module available under the same license as PostgreSQL.
Alexander Korotkov <a.korotkov@postgrespro.ru>
Postgres Professional Ltd., Russia
Oleg Bartunov <oleg@sai.msu.su> Postgres Professional
Ltd., Russia
Teodor Sigaev <teodor@sigaev.ru> Postgres
Professional Ltd., Russia