Movatterモバイル変換

• Faster ranking. Ranking requires positional information. And after the index scan we do not need an additional heap scan to retrieve lexeme positions becauseRUM index stores them.

• Faster phrase search. This improvement is related to the previous one as phrase search also needs positional information.

• Faster ordering by timestamp.RUM index stores additional information together with lexemes, so it is not necessary to perform a heap scan.

• A possibility to perform depth-first search and therefore return first results immediately.

Table F.35. rum Operators

Note

rum introduces its own ranking function that is executed inside the<=> operator. It calculates the score (inverted distance) using the specified normalization method. This function is a combination ofts_rank andts_rank_cd (seeSection 9.13 for details). Whilets_rank does not support logical operators andts_rank_cd works poorly withOR queries, therum-specific ranking function overcomes these drawbacks.

rum_tsvector_ops

Storestsvector lexemes with positional information. Supports ordering by<=> operator and prefix search.

rum_tsvector_hash_ops

Stores hash oftsvector lexemes with positional information. Supports ordering by<=> operator, but does not support prefix search.

rum_tsvector_addon_ops

Storestsvector lexemes with additional data of any type supported byRUM.

Note

To use therum_tsvector_addon_ops operator class, when creating theRUM index withCREATE INDEX, specify theattach andto storage parameters in theWITH clause.

rum_tsvector_hash_addon_ops

Storestsvector lexemes with additional data of any type supported byRUM. Does not support prefix search.

rum_tsquery_ops

Stores branches of query tree in additional information.

rum_anyarray_ops

Storesanyarray elements with length of the array. Supports ordering by <=> operator.

Indexable operators:&& @> <@ = %

rum_anyarray_addon_ops

Storesanyarray elements with additional data of any type supported byRUM.

rum_type_ops

Stores lexemes of the corresponding type with positional information. Thetype 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 withrum_tsvector_addon_ops,rum_tsvector_hash_addon_ops, andrum_anyarray_addon_ops operator classes.

Supported indexable operators depend on the data type:

• < <= = >= > <=> <=| |=> are supported forint2,int4,int8,float4,float8,money,oid,timestamp,timestamptz.

• < <= = >= > are supported fortime,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.

Let's assume we have the following table:

CREATE TABLE test_rum(t text, a tsvector);CREATE TRIGGER tsvectorupdateBEFORE UPDATE OR INSERT ON 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');

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)

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)

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)

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)