PolarDB:Full-text search acceleration (rum)

Operators

Operator classes for tsvector

Operator classes for arrays

Operator classes for other data types

Use rum_<TYPE>_ops for range queries and distance sorting on non-text, non-array columns.

Sort full-text search results by relevance

This example creates a tsvector column with a RUM index and queries it ordered by relevance score.

1. Create the table and insert test data:

   CREATE TABLE t1(
     t text,
     t_vec tsvector GENERATED ALWAYS AS (to_tsvector('pg_catalog.english', t)) STORED
   );
   
   INSERT INTO t1(t) VALUES ('The situation is most beautiful');
   INSERT INTO t1(t) VALUES ('It is a beautiful');
   INSERT INTO t1(t) VALUES ('It looks like a beautiful place');

2. Create a RUM index using the rum_tsvector_ops operator class:

   CREATE INDEX t1_t_vec_idx ON t1 USING rum (t_vec rum_tsvector_ops);

3. Query and sort by relevance. The <=> operator returns a distance value — smaller means more relevant:

   SET enable_seqscan TO off;
   
   SELECT t, t_vec <=> to_tsquery('english', 'beautiful | place') AS rank
   FROM t1
   WHERE t_vec @@ to_tsquery('english', 'beautiful | place')
   ORDER BY t_vec <=> to_tsquery('english', 'beautiful | place');

   Result:

   t                                |  rank
   ---------------------------------+---------
    It looks like a beautiful place | 8.22467
    The situation is most beautiful | 16.4493
    It is a beautiful               | 16.4493

Sort by timestamp with full-text filtering

This example shows how to attach a timestamp column to a tsvector index. Both full-text filtering and timestamp-based sorting complete in a single index scan.

1. Create the table and insert sample data:

   CREATE TABLE tsts (id int, t tsvector, d timestamp);
   
   INSERT INTO tsts VALUES
   (354, to_tsvector('wr qh'), '2016-05-16 14:21:22.326724'),
   (355, to_tsvector('wr qh'), '2016-05-16 13:21:22.326724'),
   (356, to_tsvector('ts op'), '2016-05-16 18:21:22.326724'),
   (358, to_tsvector('ts op'), '2016-05-16 23:21:22.326724'),
   (371, to_tsvector('wr qh'), '2016-05-17 06:21:22.326724'),
   (406, to_tsvector('wr qh'), '2016-05-18 17:21:22.326724'),
   (415, to_tsvector('wr qh'), '2016-05-19 02:21:22.326724');

2. Create a RUM index with the d column attached to the t index entries. The WITH (attach = 'd', to = 't') clause stores values from d alongside the tsvector entries for t, so sorting on d requires no separate table lookup:

   CREATE INDEX tsts_idx ON tsts USING rum (t rum_tsvector_addon_ops, d) WITH (attach = 'd', to = 't');

3. Query records matching the full-text filter and sort by proximity to a target timestamp:

   SET enable_seqscan TO off;
   
   EXPLAIN (costs off)
   SELECT id, d, d <=> '2016-05-16 14:21:25' AS distance
   FROM tsts
   WHERE t @@ 'wr&qh'
   ORDER BY d <=> '2016-05-16 14:21:25'
   LIMIT 5;

   The execution plan confirms that both filtering and sorting use a single index scan:

   QUERY PLAN
   ------------------------------------------------------------------------------
    Limit
      ->  Index Scan using tsts_idx on tsts
            Index Cond: (t @@ '''wr'' & ''qh'''::tsquery)
            Order By: (d <=> '2016-05-16 14:21:25'::timestamp without time zone)

   Result:

   id  |             d              |   distance
   -----+----------------------------+---------------
    354 | 2016-05-16 14:21:22.326724 |      2.673276
    355 | 2016-05-16 13:21:22.326724 |   3602.673276
    371 | 2016-05-17 06:21:22.326724 |  57597.326724
    406 | 2016-05-18 17:21:22.326724 | 183597.326724
    415 | 2016-05-19 02:21:22.326724 | 215997.326724

Query and sort arrays by similarity

This example indexes an integer array column and retrieves rows that overlap with a target array, ordered by similarity.

1. Create the table and insert sample data:

   CREATE TABLE test_array (id serial, i int2[]);
   INSERT INTO test_array(i) VALUES ('{}'), ('{0}'), ('{1,2,3,4}'), ('{1,2,3}'), ('{1,2}'), ('{1}');

2. Create a RUM index using the rum_anyarray_ops operator class:

   CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops);

3. Find rows that contain the element 1 and sort by similarity to {1}. The && operator checks for array overlap, and <=> returns the distance — smaller means more similar:

   SELECT *
   FROM test_array
   WHERE i && '{1}'
   ORDER BY i <=> '{1}' ASC;

   Result:

   i
   -----------
    {1}
    {1,2}
    {1,2,3}
    {1,2,3,4}

Match documents against stored query rules

This example indexes a tsquery column to perform inverted matching: given a new document, find all stored query rules that the document satisfies. This pattern is useful for subscription or alert systems.

1. Create the query rule table and insert rules:

   CREATE TABLE query (id serial, q tsquery, tag text);
   
   INSERT INTO query (q, tag) VALUES
   ('supernova & star', 'sn'),
   ('black', 'color'),
   ('big & bang & black & hole', 'bang'),
   ('spiral & galaxy', 'shape'),
   ('black & hole', 'color');

2. Create a RUM index on the tsquery column:

   CREATE INDEX query_idx ON query USING rum(q rum_tsquery_ops);

3. Match a new document against all stored rules:

   SELECT *
   FROM query
   WHERE to_tsvector('black holes never exists before we think about them') @@ q;

   Result:

   id | q       | tag
   ---+---------+-------
    2 | 'black' | color