- Notifications
You must be signed in to change notification settings - Fork58
RUM access method - inverted index with additional information in posting lists
License
postgrespro/rum
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
Therum module provides access method to work withRUM index. It is basedon theGIN access methods code.
GIN index allows to perform fast full text search usingtsvector andtsquery types. But full text search with GIN index has several problems:
- Slow ranking. It is need position information about lexems to ranking.
GINindex doesn't store positions of lexems. So after index scan we need additionalheap scan to retreive lexems positions. - Slow phrase search with
GINindex. This problem relates with previousproblem. It is need position information to perform phrase search. - Slow ordering by timestamp.
GINindex can't store some related informationin index with lexemes. So it is necessary to perform additional heap scan.
RUM solves this problems by storing additional information in posting tree.For example, positional information of lexemes or timestamps. You can get anidea ofRUM by the following picture:
Drawback ofRUM is that it has slower build and insert time thanGIN.It is because we need to store additional information besides keys and becauseRUM uses generic WAL records.
This module available under the same license asPostgreSQL.
Before build and installrum you should ensure following:
- 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 rumImportant: Don't forget to set the
PG_CONFIGvariable in case you want to testRUMon a custom build of PostgreSQL. Read morehere.
rum module provides next operators.
| Operator | Returns | Description |
|---|---|---|
| tsvector <=> tsquery | float4 | Returns distance between tsvector and tsquery. |
| timestamp <=> timestamp | float8 | Returns distance between two timestamps. |
| timestamp <=| timestamp | float8 | Returns distance only for left timestamps. |
| timestamp |=> timestamp | float8 | Returns distance only for right timestamps. |
Last three operations also works for types timestamptz, int2, int4, int8, float4, float8,money and oid.
rum provides next operator classes.
For type:tsvector
This operator class storestsvector lexemes with positional information. Supportsordering by<=> operator and prefix search. There is the example.
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 hash oftsvector lexemes with positional information.Supports ordering by<=> operator. Butdoesn'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.
Supports ordering by<=>,<=| and|=> operators. 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 lexems with any supported by modulefield. There is the example.
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)
For type:tsvector
This operator class stores hash oftsvector lexems with any supported by modulefield.
Doesn't support prefix search.
For type:tsquery
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 storesanyarrray elements with length of the array.Supports operators&&,@>,<@,=,% operators. 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 storesanyarrray 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
About
RUM access method - inverted index with additional information in posting lists
Topics
Resources
License
Uh oh!
There was an error while loading.Please reload this page.
Stars
Watchers
Forks
Packages0
Uh oh!
There was an error while loading.Please reload this page.