## F.57. rum

### F.57.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.57.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.57.3. Common Operators

The operators provided by the `rum` module are shown in Table F.44:

Table F.44. `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.57.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 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 `anyarrray` 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_`type`_ops` supports ordering by `<=>`, `<=|`, 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`.

### 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.57.5. Examples

#### F.57.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.57.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.57.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.57.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)

### F.57.6. Authors

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