F.67. rum — an access method to work with the RUM
indexes #
F.67.1. Introduction #
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. However, full-text search with GIN
index has the following drawbacks:
Slow ranking. Ranking requires positional information, but
GIN
index does not store positions of lexemes. So after the index scan we need an additional heap scan to retrieve lexeme positions.Slow phrase search. This problem is related to the previous one as phrase search also needs positional information.
Slow ordering by timestamp.
GIN
index cannot store any additional information together with lexemes, so it is necessary to perform a heap scan.
RUM
solves these issues by storing additional information in posting tree. In particular, it stores positional information of lexemes or timestamps.
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.
F.67.2. Installation #
rum
is a Postgres Pro Enterprise extension and it has no special prerequisites.
Install extension as follows:
$ psql dbname
-c "CREATE EXTENSION rum"
F.67.3. Common Operators #
The operators provided by the rum
module are shown in Table F.52:
Table F.52. 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. |
F.67.4. Operator Classes #
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 byRUM
.rum_tsvector_hash_addon_ops
Stores
tsvector
lexemes with additional data of any type supported byRUM
. 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 byRUM
.rum_
type
_opsStores 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 bytype
_ops<=>
,<=|
, and|=>
operators. This operator class can be used together withrum_tsvector_addon_ops
,rum_tsvector_hash_addon_ops
, andrum_anyarray_addon_ops
operator classes.Supported indexable operators depend on the data type:
< <= = >= > <=> <=| |=>
are supported forint2
,int4
,int8
,float4
,float8
,money
,oid
,timestamp
,timestamptz
.< <= = >= >
are supported fortime
,timetz
,date
,interval
,macaddr
,inet
,cidr
,text
,varchar
,char
,bytea
,bit
,varbit
,numeric
.
Note
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
.
F.67.5. Examples #
F.67.5.1. rum_tsvector_ops Example #
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)
F.67.5.2. rum_tsvector_addon_ops Example #
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)
F.67.5.3. rum_tsquery_ops Example #
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)
F.67.5.4. rum_anyarray_ops Example #
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)