Movatterモバイル変換

• Slow ranking. Ranking requires positional information, butGIN index does not store positions of lexemes. So after the index scan we need an additional heap scan to retrieve lexeme positions.

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

• Slow ordering by timestamp.GIN index cannot store any additional information together with lexemes, so it is necessary to perform a heap scan.

Table F.112. rum Operators

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.

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)