The rum module provides access method to work with the RUM indexes. It is based on the GIN access method code.
GIN index allows you to perform fast full-text search
using tsvector and tsquery types.
However, full-text search with GIN index has some
performance issues because positional and other additional information
is not stored.
RUM solves these issues by storing additional information in a posting tree. As compared to GIN, RUM index has the following benefits:
Faster ranking. Ranking requires positional information. And after the index scan we do not need an additional heap scan to retrieve lexeme positions because RUM index stores them.
Faster phrase search. This improvement is related to the previous one as phrase search also needs positional information.
Faster ordering by timestamp. RUM index stores additional information together with lexemes, so it is not necessary to perform a heap scan.
A possibility to perform depth-first search and therefore return first results immediately.
The drawback of RUM is that it has slower build and insert time as compared to GIN because RUM stores additional information together with keys and uses generic WAL records.
rum is a Postgres Pro Standard
extension and it has no special prerequisites.
Install extension as follows:
$ psql dbname -c "CREATE EXTENSION rum"
The operators provided by the rum module are shown
in Table F.36:
Table F.36. 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. |
rum introduces its own ranking function that is
executed inside the <=> operator. It calculates
the score (inverted distance) using the specified normalization method.
This function is a combination of ts_rank and
ts_rank_cd (see Section 9.13
for details). While ts_rank does not support logical
operators and ts_rank_cd works poorly with
OR queries, the rum-specific
ranking function overcomes these drawbacks.
The rum extension provides the following operator classes:
rum_tsvector_ops
Stores tsvector lexemes with positional information.
Supports ordering by <=> operator and prefix search.
rum_tsvector_hash_ops
Stores hash of tsvector lexemes with
positional information. Supports ordering by <=>
operator, but does not support prefix search.
rum_tsvector_addon_ops
Stores tsvector lexemes with additional data of any type supported by RUM.
rum_tsvector_hash_addon_ops
Stores tsvector lexemes with additional data of any type supported by RUM.
Does not support prefix search.
rum_tsquery_ops
Stores branches of query tree in additional information.
rum_anyarray_ops
Stores anyarray elements with length of the array.
Supports ordering by <=> operator.
Indexable operators: && @> <@ = %
rum_anyarray_addon_ops
Stores anyarray elements with additional data
of any type supported by RUM.
rum_type_ops
Stores lexemes of the corresponding type with positional information.
The type placeholder in the class name
must be substituted by one of the following type names:
int2, int4, int8,
float4, float8, money,
oid, timestamp, timestamptz,
time, timetz, date,
interval, macaddr, inet,
cidr, text, varchar,
char, bytea, bit,
varbit, numeric.
rum_ supports ordering by type_ops<=>, <=|,
and |=> operators. This operator class can be used together with
rum_tsvector_addon_ops, rum_tsvector_hash_addon_ops,
and rum_anyarray_addon_ops operator classes.
Supported indexable operators depend on the data type:
< <= = >= > <=> <=| |=> are supported for
int2, int4, int8,
float4, float8, money,
oid, timestamp, timestamptz.
< <= = >= > are supported for
time, timetz, date,
interval, macaddr, inet,
cidr, text, varchar,
char, bytea, bit,
varbit, numeric.
The following operator classes are now deprecated:
rum_tsvector_timestamp_ops,
rum_tsvector_timestamptz_ops,
rum_tsvector_hash_timestamp_ops,
rum_tsvector_hash_timestamptz_ops.
The RUM index provides functions for low-level inspection of all its page types.
rum_metapage_info(rel_name text, blk_num int4) returns record
Returns information about a RUM index metapage. For example:
SELECT * FROM rum_metapage_info('rum_index', 0);
-[ RECORD 1 ]----+-----------
pending_head | 4294967295
pending_tail | 4294967295
tail_free_size | 0
n_pending_pages | 0
n_pending_tuples | 0
n_total_pages | 87
n_entry_pages | 80
n_data_pages | 6
n_entries | 1650
version | 0xC0DE0002
rum_page_opaque_info(rel_name text, blk_num int4) returns record
Returns information about a RUM index opaque area,
such as leftlink and rightlink,
maxoff, and freespace. The
maxoff parameter is the number of elements stored
in the page, it is used differently for different types of pages.
The freespace column represents the amount of free
space in the page. For example:
SELECT * FROM rum_page_opaque_info('rum_index', 10);
leftlink | rightlink | maxoff | freespace | flags
----------+-----------+--------+-----------+--------
6 | 11 | 0 | 0 | {leaf}
(1 row)
rum_internal_entry_page_items(rel_name text, blk_num int4) returns setof record
Returns information stored in internal pages of the entry tree. This
information is extracted from IndexTuple. For
example:
SELECT * FROM rum_internal_entry_page_items('rum_index', 1);
key | attrnum | category | down_link
---------------------------------+---------+------------------+-----------
3d | 1 | RUM_CAT_NORM_KEY | 3
6k | 1 | RUM_CAT_NORM_KEY | 2
a8 | 1 | RUM_CAT_NORM_KEY | 4
...
Tue May 10 21:21:22.326724 2016 | 2 | RUM_CAT_NORM_KEY | 83
Sat May 14 19:21:22.326724 2016 | 2 | RUM_CAT_NORM_KEY | 84
Wed May 18 17:21:22.326724 2016 | 2 | RUM_CAT_NORM_KEY | 85
+inf | | | 86
(79 rows)
The RUM index, just like GIN,
packs downlinks and keys in pairs of the (P_n, K_{n+1})
type on internal pages of the entry tree. Note that there is no key for
P_0, it is assumed to be equal to -inf.
Also, there is no downlink for the last key K_{n+1},
it is assumed to be the largest key, or high key, in the subtree to
which the P_n link leads. In the rightmost page of
each internal level of the entry tree, the key related to
P_n does not have any value and is assumed to be
equal to +inf.
rum_leaf_entry_page_items(rel_name text, blk_num int4) returns setof record
Returns information that is stored in leaf pages of the entry tree. This information is extracted from compressed posting lists. For example:
SELECT * FROM rum_leaf_entry_page_items('rum_index', 10);
key | attrnum | category | tuple_id | add_info_is_null | add_info | is_posting_tree | posting_tree_root
-----+---------+------------------+----------+------------------+----------+------------------+--------------------
ay | 1 | RUM_CAT_NORM_KEY | (0,16) | t | | f |
ay | 1 | RUM_CAT_NORM_KEY | (0,23) | t | | f |
ay | 1 | RUM_CAT_NORM_KEY | (2,1) | t | | f |
...
az | 1 | RUM_CAT_NORM_KEY | (0,15) | t | | f |
az | 1 | RUM_CAT_NORM_KEY | (0,22) | t | | f |
az | 1 | RUM_CAT_NORM_KEY | (1,4) | t | | f |
...
b9 | 1 | RUM_CAT_NORM_KEY | | | | t | 7
...
(1602 rows)
Each posting list is an IndexTuple that stores
the key value and a compressed list of TIDs. When the
rum_leaf_entry_page_items() function is called,
the key value is attached to each TID for convenience, but in the page
it is stored separately.
If the number of TIDs is too large, then a posting tree is used for
storage rather than a posting list. In the example above, a posting
tree is created for the key with the b9 value. The
key in the posting tree is the TID. In this case, the magic number and
the page number, which is the root of the posting tree, are stored
inside IndexTuple instead of the posting list.
rum_internal_data_page_items(rel_name text, blk_num int4) returns setof record
Returns information that is stored in internal pages of the posting
tree. This information is extracted from arrays of
the RumPostingItem structures. For example:
SELECT * FROM rum_internal_data_page_items('rum_index', 7);
is_high_key | block_number | tuple_id | add_info_is_null | add_info
-------------+--------------+----------+------------------+----------
t | | (0,0) | t |
f | 9 | (138,79) | t |
f | 8 | (0,0) | t |
(3 rows)
Each internal page element of the posting tree contains the high key (TID) value for the child page and a link to this child page as well as additional information if it was added when creating the index.
At the beginning of am internal page of the posting tree, the high
key of this page is always stored if it has the
(0,0) value, this is equivalent to
+inf. This is always the case if the page is the
rightmost.
At the moment, RUM does not support storing data of
the varlena data types in internal pages of the posting
tree. These types of data can be stored only on leaf pages. Therefore,
the following output is possible:
is_high_key | block_number | tuple_id | add_info_is_null | add_info -------------+--------------+----------+------------------+------------------------------------------------ ... f | 23 | (39,43) | f | varlena types in posting tree is not supported f | 22 | (74,9) | f | varlena types in posting tree is not supported ...
rum_leaf_data_page_items(rel_name text, blk_num int4) returns setof record
Returns information that is stored in leaf pages of the posting tree. This information is extracted from compressed posting lists. For example:
SELECT * FROM rum_leaf_data_page_items('rum_idx', 9);
is_high_key | tuple_id | add_info_is_null | add_info
-------------+-----------+------------------+----------
t | (138,79) | t |
f | (0,9) | t |
f | (1,23) | t |
f | (3,5) | t |
f | (3,22) | t |
Unlike leaf pages of the entry tree, compressed posting lists are not
stored in IndexTuple in the posting tree leaf
pages. The high key is the largest key on the page.
Let's assume we have the following 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');
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)
Let's assume we have the following 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_addon_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)
Suppose 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)
Let's assume we have the following table:
CREATE TABLE test_array (i int2[]);
INSERT INTO test_array VALUES ('{}'), ('{0}'), ('{1,2,3,4}'), ('{1,2,3}'), ('{1,2}'), ('{1}');
CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);
Now we can execute the following query using index scan:
SET enable_seqscan TO off;
EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{1}' ORDER BY i <=> '{1}' ASC;
QUERY PLAN
------------------------------------------
Index Scan using idx_array on test_array
Index Cond: (i && '{1}'::smallint[])
Order By: (i <=> '{1}'::smallint[])
(3 rows)
SELECT * FROM test_array WHERE i && '{1}' ORDER BY i <=> '{1}' ASC;
i
-----------
{1}
{1,2}
{1,2,3}
{1,2,3,4}
(4 rows)