Repository files navigation

Therum module provides access method to work with theRUM indexes. It is basedon theGIN access method code.

GIN index allows you to perform fast full-text search usingtsvector andtsquery types. However, full-text search withGIN index has some performanceissues because positional and other additional information is not stored.

RUM solves these issues by storing additional information in a posting tree.As compared toGIN,RUM index has the following benefits:

• Faster ranking. Ranking requires positional information. And after theindex scan we do not need an additional heap scan to retrieve lexeme positionsbecauseRUM index stores them.
• Faster phrase search. This improvement is related to the previous one asphrase search also needs positional information.
• Faster ordering by timestamp.RUM index stores additional information togetherwith lexemes, so it is not necessary to perform a heap scan.
• A possibility to perform depth-first search and therefore return firstresults immediately.

You can get an idea ofRUM with the following diagram:

The drawback ofRUM is that it has slower build and insert time as compared toGINThis is because we need to store additional information besides keys and becausebecauseRUM stores additional information together with keys and uses generic WAL records.

This module is available under thelicense similar toPostgreSQL.

Before building and installingrum, you should ensure following are installed:

• PostgreSQL version is 9.6+.

Typical installation procedure may look like this:

$ git clone https://github.com/postgrespro/rum$ cd rum$ make USE_PGXS=1$ make USE_PGXS=1 install$ make USE_PGXS=1 installcheck$ psql DB -c "CREATE EXTENSION rum;"

$ USE_PGXS=1 pgxn install rum

Important: Don't forget to set thePG_CONFIG variable in case you want to testRUM on a custom build of PostgreSQL. Read morehere.

$ make check

This command runs:

• regression tests;

• isolation tests;

• tap tests.

  One of the tap tests downloads a 1GB archive and then unpacks itinto a file weighing almost 3GB. It is disabled by default.

  To run this test, you need to set an environment variable:

    $ export PG_TEST_EXTRA=big_values

  The way to turn it off again:

    $ export -n PG_TEST_EXTRA

Therum module provides next operators.

The last three operations also work for types timestamptz, int2, int4, int8, float4, float8,money and oid.

rum provides the following operator classes.

For type:tsvector

This operator class storestsvector lexemes with positional information. It supportsordering by the<=> operator and prefix search. See the example below.

Let us assume we have the table:

CREATETABLEtest_rum(ttext, a tsvector);CREATETRIGGERtsvectorupdateBEFOREUPDATEOR INSERTON test_rumFOR 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 therum index we need create an extension:

CREATE EXTENSION rum;

Then we can create new index:

CREATEINDEXrumidxON test_rum USING rum (a rum_tsvector_ops);

And we can execute the following queries:

SELECT t, a<=> to_tsquery('english','beautiful | place')AS rankFROM test_rumWHERE a @@ to_tsquery('english','beautiful | place')ORDER BY a<=> to_tsquery('english','beautiful | place');                t                |  rank---------------------------------+--------- It lookslike a beautiful place |8.22467 The situation is most beautiful |16.4493 It is a beautiful               |16.4493(3 rows)SELECT t, a<=> to_tsquery('english','place | situation')AS rankFROM test_rumWHERE a @@ to_tsquery('english','place | situation')ORDER BY a<=> to_tsquery('english','place | situation');                t                |  rank---------------------------------+--------- The situation is most beautiful |16.4493 It lookslike a beautiful place |16.4493(2 rows)

For type:tsvector

This operator class stores a hash oftsvector lexemes with positional information.It supports ordering by the<=> operator. Itdoesn't support prefix search.

For types: int2, int4, int8, float4, float8, money, oid, time, timetz, date,interval, macaddr, inet, cidr, text, varchar, char, bytea, bit, varbit,numeric, timestamp, timestamptz

Supported operations:<,<=,=,>=,> for all types and<=>,<=| and|=> for int2, int4, int8, float4, float8, money, oid,timestamp and timestamptz types.

This operator supports ordering by the<=>,<=| and|=> operators. It can be used withrum_tsvector_addon_ops,rum_tsvector_hash_addon_ops andrum_anyarray_addon_ops operator classes.

For type:tsvector

This operator class storestsvector lexemes with any supported by modulefield. See the example below.

Let us assume we have the table:

CREATETABLEtsts (idint, t tsvector, dtimestamp);\copy tstsfrom'rum/data/tsts.data'CREATEINDEXtsts_idxON 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 tstsWHERE t @@'wr&qh'ORDER BY d<=>'2016-05-16 14:21:25'LIMIT5;                                    QUERY PLAN-----------------------------------------------------------------------------------Limit->  Index Scan using tsts_idxon 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 tstsWHERE t @@'wr&qh'ORDER BY d<=>'2016-05-16 14:21:25'LIMIT5; id  |                d                |   ?column?-----+---------------------------------+---------------355 | Mon May1614:21:22.3267242016 |2.673276354 | Mon May1613:21:22.3267242016 |3602.673276371 | Tue May1706:21:22.3267242016 |57597.326724406 | Wed May1817:21:22.3267242016 |183597.326724415 | Thu May1902:21:22.3267242016 |215997.326724(5 rows)

Warning: Currently RUM has bogus behaviour when one creates an index using ordering over pass-by-reference additional information. This is due to the fact that posting trees have fixed length right bound and fixed length non-leaf posting items. It isn't allowed to create such indexes.

For type:tsvector

This operator class stores a hash oftsvector lexemes with any supported by modulefield.

Itdoesn't support prefix search.

For type:tsquery

It stores branches of query tree in additional information. For example, we have the table:

CREATETABLEquery (q tsquery, tagtext);INSERT INTO queryVALUES ('supernova & star','sn'),    ('black','color'),    ('big & bang & black & hole','bang'),    ('spiral & galaxy','shape'),    ('black & hole','color');CREATEINDEXquery_idxON query USING rum(q);

Now we can execute the following fast query:

SELECT*FROM queryWHERE to_tsvector('black holes never exists before we think about them') @@ q;        q         |  tag------------------+-------'black'          | color'black' &'hole' | color(2 rows)

For type:anyarray

This operator class storesanyarray elements with length of the array.It supports operators&&,@>,<@,=,% operators. It also supports ordering by<=> operator.For example, we have the table:

CREATETABLEtest_array (i int2[]);INSERT INTO test_arrayVALUES ('{}'), ('{0}'), ('{1,2,3,4}'), ('{1,2,3}'), ('{1,2}'), ('{1}');CREATEINDEXidx_arrayON test_array USING rum (i rum_anyarray_ops);

Now we can execute the query using index scan:

SET enable_seqscan TO off;EXPLAIN (COSTS OFF)SELECT*FROM test_arrayWHERE i &&'{1}'ORDER BY i<=>'{1}'ASC;                QUERY PLAN------------------------------------------ Index Scan using idx_arrayon test_array   Index Cond: (i &&'{1}'::smallint[])Order By: (i<=>'{1}'::smallint[])(3 rowsSELECT*FROM test_arrayWHERE i &&'{1}'ORDER BY i<=>'{1}'ASC;     i----------- {1} {1,2} {1,2,3} {1,2,3,4}(4 rows)

For type:anyarray

This operator class storesanyarray elements with any supported by modulefield.

• Allow multiple additional information (lexemes positions + timestamp).
• Improve ranking function to support TF/IDF.
• Improve insert time.
• Improve GENERIC WAL to support shift (PostgreSQL core changes).

Alexander Korotkova.korotkov@postgrespro.ru Postgres Professional Ltd., Russia

Oleg Bartunovo.bartunov@postgrespro.ru Postgres Professional Ltd., Russia

Teodor Sigaevteodor@postgrespro.ru Postgres Professional Ltd., Russia

Arthur Zakirova.zakirov@postgrespro.ru Postgres Professional Ltd., Russia

Pavel Borisovp.borisov@postgrespro.com Postgres Professional Ltd., Russia

Maxim Orlovm.orlov@postgrespro.ru Postgres Professional Ltd., Russia