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

### 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 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. New operator classes

The `rum` extension provides the following operator classes.

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

#### F.57.4.2. `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.

#### F.57.4.3. `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.

#### F.57.4.4. `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.

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

#### F.57.4.6. `rum_tsvector_timestamptz_ops` — operator class for `tsvector` with `timestamptz`

See description of the rum_tsvector_timestamp_ops operator class.

#### F.57.4.7. `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.

#### F.57.4.8. `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.

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

### F.57.5. License

This module available under the same license as PostgreSQL.

### 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