RUM: improved inverted index for full-text search based on GIN index
RUM is an extension which adds a RUM index to Postgresql.
RUM index is based on GIN that stores additional per-entry information in a posting tree. For example, positional information of lexemes or timestamps. In comparison to GIN it can use this information to make faster index-only scans for:
- Phrase search
- Text search with ranking by text distance operator
- Text
SELECTs with ordering by some non-indexed additional column e.g. by timestamp.
RUM works best in scenarios when the possible keys are highly repeatable. I.e. all texts are composed of a limited amount of words, so per-lexeme indexing gives significant speed-up in searching texts containing word combinations or phrases.
Main operators for ordering are:
tsvector <=> tsquery | float4 | Distance between tsvector and tsquery.
value <=> value | float8 | Distance between two values.
Where value is timestamp, timestamptz, int2, int4, int8, float4, float8, money and oid
Usage#
Enable the extension#
You can get started with rum by enabling the extension in your Supabase dashboard.
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "rum" and enable the extension.
Syntax#
For type: tsvector#
To understand the following you may need first to see Official PostgreSQL documentation on text search
rum_tsvector_ops
_11CREATE TABLE test_rum(t text, a tsvector);_11_11CREATE TRIGGER tsvectorupdate_11BEFORE UPDATE OR INSERT ON test_rum_11FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('a', 'pg_catalog.english', 't');_11_11INSERT INTO test_rum(t) VALUES ('The situation is most beautiful');_11INSERT INTO test_rum(t) VALUES ('It is a beautiful');_11INSERT INTO test_rum(t) VALUES ('It looks like a beautiful place');_11_11CREATE INDEX rumidx ON test_rum USING rum (a rum_tsvector_ops);
And we can execute tsvector selects with ordering by text distance operator:
_10SELECT t, a `<=>` to_tsquery('english', 'beautiful | place') AS rank_10 FROM test_rum_10 WHERE a @@ to_tsquery('english', 'beautiful | place')_10 ORDER BY a `<=>` to_tsquery('english', 'beautiful | place');_10 t | rank_10---------------------------------+---------_10 It looks like a beautiful place | 8.22467_10 The situation is most beautiful | 16.4493_10 It is a beautiful | 16.4493_10(3 rows)
rum_tsvector_addon_ops
_10CREATE TABLE tsts (id int, t tsvector, d timestamp);_10CREATE INDEX tsts_idx ON tsts USING rum (t rum_tsvector_addon_ops, d)_10 WITH (attach = 'd', to = 't');
Now we can execute the selects with ordering distance operator on attached column:
_10SELECT 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;_10 id | d | ?column?_10-----+---------------------------------+---------------_10 355 | Mon May 16 14:21:22.326724 2016 | 2.673276_10 354 | Mon May 16 13:21:22.326724 2016 | 3602.673276_10 371 | Tue May 17 06:21:22.326724 2016 | 57597.326724_10 406 | Wed May 18 17:21:22.326724 2016 | 183597.326724_10 415 | Thu May 19 02:21:22.326724 2016 | 215997.326724_10(5 rows)
For type: anyarray#
rum_anyarray_ops
This operator class stores anyarray elements with length of the array. It supports operators &&, @>, <@, =, % operators. It also supports ordering by <=> operator.
_10CREATE TABLE test_array (i int2[]);_10INSERT INTO test_array VALUES ('{}'), ('{0}'), ('{1,2,3,4}'), ('{1,2,3}'), ('{1,2}'), ('{1}');_10CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);
Now we can execute the query using index scan:
_10SELECT * FROM test_array WHERE i && '{1}' ORDER BY i `<=>` '{1}' ASC;_10 i_10-----------_10 {1}_10 {1,2}_10 {1,2,3}_10 {1,2,3,4}_10(4 rows)
rum_anyarray_addon_ops
The does the same with anyarray index as rum_tsvector_addon_ops i.e. allows to order select results using distance
operator by attached column.
Limitations#
RUM has slower build and insert times than GIN due to:
- It is bigger due to the additional attributes stored in the index.
- It uses generic WAL records.