MODULE_big = rum

OBJS = rumsort.o rum_ts_utils.o\

rumbtree.o rumbulk.o rumdatapage.o\

rumentrypage.o rumfast.o rumget.o ruminsert.o\

rumscan.o rumutil.o rumvacuum.o rumvalidate.o$(WIN32RES)

EXTENSION = rum

DATA = rum--1.0.sql

PGFILEDESC = "RUM index access method"

REGRESS = rum

PG_CONFIG = pg_config

PGXS :=$(shell$(PG_CONFIG) --pgxs)

else

subdir = contrib/rum

top_builddir = ../..

include$(top_builddir)/src/Makefile.global

include$(top_srcdir)/contrib/contrib-global.mk

wal-check: temp-install

The**rum** module provides access methods to work with RUM index. It is based

on the GIN access methods code.

This module available under the same license as

[PostgreSQL](http://www.postgresql.org/about/licence/).

Before build and install**rum** you should ensure following:

* PostgreSQL version is 9.6.

Typical installation procedure may look like this:

The**rum** module provides the access method**rum** and the operator class

**rum_tsvector_ops**.

The module provides new operators.

| Operator | Returns | Description

| ------------------- | ------- | ----------------------------------------------

| tsvector>< tsquery | float4 | Returns distance between tsvector and tsquery.

Let us assume we have the table:

CREATETABLEtest_rum(ttext, a tsvector);

BEFOREUPDATEOR INSERTON test_rum

FOR 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 the**rum** 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:

t | rank

The situation is most beautiful |0.0303964

It is a beautiful |0.0303964

It lookslike a beautiful place |0.0607927

(3 rows)

t | rank

The situation is most beautiful |0.0303964

It lookslike a beautiful place |0.0303964

(2 rows)

Alexander Korotkov <a.korotkov@postgrespro.ru> Postgres Professional Ltd., Russia

Oleg Bartunov<oleg@sai.msu.su> Postgres Professional Ltd., Russia

Teodor Sigaev<teodor@sigaev.ru> Postgres Professional Ltd., Russia

As a reward for your reformation I write to you on this precious sheet.

You see I have come to be wonderfully attached to Heidelberg, the

beautiful, the quaint, the historically poetic, learned and picturesque

old town on the Neckar. It seems like another home. So I could not show

my appreciation of you in a more complimentary way than by sending this

little series of pictures. Have you ever been here, I wonder? You did

not say, but you wrote as if you knew it by sight as well as by heart.

As I cannot know, I will venture an explanation. The panorama speaks for

itself. Put on your “specs” and look at the castle, half way up the

_berg_, “the Jettenhuhl, a wooded spur of the Konigestuhl.” Look at it

from the “Terrasse.” Thus you’ll get something of an idea of it. The

Gesprente Thurm is the one that was blown up by the French. The

thickness of the walls, twenty-one feet, and the solid masonry, held it

so well that only a fragment, as it were, gave way. It still hangs as if

ready to be replaced. “Das Grosse Fass Gebaude,” too, you will have no

difficulty in making out. If you only had it with its 49,000 gallons of

wine, but wouldn’t you divide with your neighbors! The columns in the

portico that shows in the Schlosshof are the four brought from

Charlemagne’s palace at Ingelheim by the Count Palatine Ludwig, some

time between 1508-44. The Zum Ritter has nothing to do with the castle,

but is an ancient structure (1592) in the Renaissance style, and one of

the few that escaped destruction in 1693. It is a beautiful, highly

ornamental building, and I wish you could see it, if you have not seen

it.

All the above information, I beg you to believe, I do not intend you

to think was evolved from my inner consciousness, but gathered from

the--nearest guide-book!

I am so much obliged to you for mapping out Switzerland to me. I have

been trying my best to get all those “passes” into my brain. Now, thanks

to your letter, I have them all in the handiest kind of a bunch. Ariel

like, “I’ll do my bidding gently,” and as surely, if I get there. But

there are dreadful reports of floods and roads caved in and bridges

swept away and snows and--enough of such exciting items as sets one

thinking--“to go or not to go?” We are this far on the way. Reached

here this afternoon. Have spent the evening sauntering in the gardens,

the Conversationhaus, the bazaar, mingling with the throng, listening to

the band, and comparing what it is with what it was. It was a gay and

curious spectacle, but on the whole had “the banquet-hall deserted”

look. The situation is most beautiful. It lies, you know, at the

entrance of the Black Forest, among picturesque, thickly-wooded hills,

in the valley of the Oos, and extends up the slope of some of the hills.

The Oos is a most turbid, turbulent stream; dashes through part of the

town with angry, headlong speed. There is an avenue along its bank of

oaks, limes and maples, bordered with flower-beds and shrubberies, and

adorned with fountains and handsome villas. We shall devote to-morrow to

seeing all there is to be seen, and go to Strassburg to-morrow evening

for two or three days. From there to Constance, and then hold _our_

“Council” as to further movements.

def fgr

def xxx fgr

CREATE EXTENSION rum;

CREATE TABLE test_rum( t text, a tsvector );

CREATE TRIGGER tsvectorupdate

BEFORE UPDATE OR INSERT ON test_rum

FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('a', 'pg_catalog.english', 't');

CREATE INDEX rumidx ON test_rum USING rum (a rum_tsvector_ops);

\copy test_rum(t) from 'data/rum.data';

SET enable_seqscan=off;

explain (costs off)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', 'ever|wrote');

QUERY PLAN

------------------------------------------------------------------

Aggregate

-> Bitmap Heap Scan on test_rum

Recheck Cond: (a @@ '''ever'' | ''wrote'''::tsquery)

-> Bitmap Index Scan on rumidx

Index Cond: (a @@ '''ever'' | ''wrote'''::tsquery)

(5 rows)

explain (costs off)

SELECT * FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', 'ever|wrote')

ORDER BY a >< to_tsquery('pg_catalog.english', 'ever|wrote');

QUERY PLAN

------------------------------------------------------------------

Sort

Sort Key: ((a >< '''ever'' | ''wrote'''::tsquery))

-> Bitmap Heap Scan on test_rum

Recheck Cond: (a @@ '''ever'' | ''wrote'''::tsquery)

-> Bitmap Index Scan on rumidx

Index Cond: (a @@ '''ever'' | ''wrote'''::tsquery)

(6 rows)

explain (costs off)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english',

'def <-> fgr');

QUERY PLAN

-----------------------------------------------------------

Aggregate

-> Index Scan using rumidx on test_rum

Index Cond: (a @@ '''def'' <-> ''fgr'''::tsquery)

(3 rows)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', 'ever|wrote');

count

-------

2

(1 row)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', 'have&wish');

count

-------

1

(1 row)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', 'knew&brain');

count

-------

0

(1 row)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', 'among');

count

-------

1

(1 row)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', 'structure&ancient');

count

-------

1

(1 row)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', '(complimentary|sight)&(sending|heart)');

count

-------

2

(1 row)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english',

'def <-> fgr');

count

-------

1

(1 row)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english',

'def <2> fgr');

count

-------

2

(1 row)

SELECT rum_ts_distance(a, to_tsquery('pg_catalog.english', 'way')), *

FROM test_rum

WHERE a @@ to_tsquery('pg_catalog.english', 'way')

ORDER BY a >< to_tsquery('pg_catalog.english', 'way');

rum_ts_distance | t | a

-----------------+--------------------------------------------------------------------------+---------------------------------------------------------------

0.0607927 | my appreciation of you in a more complimentary way than by sending this | 'appreci':2 'complimentari':8 'send':12 'way':9

0.0607927 | itself. Put on your “specs” and look at the castle, half way up the | 'castl':10 'half':11 'look':7 'put':2 'spec':5 'way':12

0.0607927 | so well that only a fragment, as it were, gave way. It still hangs as if | 'fragment':6 'gave':10 'hang':14 'still':13 'way':11 'well':2

0.0607927 | thinking--“to go or not to go?” We are this far on the way. Reached | 'far':11 'go':3,7 'reach':15 'think':1 'way':14

(4 rows)

SELECT rum_ts_distance(a, to_tsquery('pg_catalog.english', 'way & (go | half)')), *

FROM test_rum

WHERE a @@ to_tsquery('pg_catalog.english', 'way & (go | half)')

ORDER BY a >< to_tsquery('pg_catalog.english', 'way & (go | half)');

rum_ts_distance | t | a

-----------------+---------------------------------------------------------------------+---------------------------------------------------------

0.103556 | thinking--“to go or not to go?” We are this far on the way. Reached | 'far':11 'go':3,7 'reach':15 'think':1 'way':14

0.0991032 | itself. Put on your “specs” and look at the castle, half way up the | 'castl':10 'half':11 'look':7 'put':2 'spec':5 'way':12

(2 rows)

INSERT INTO test_rum (t) VALUES ('foo bar foo the over foo qq bar');

INSERT INTO test_rum (t) VALUES ('345 qwerty copyright');

INSERT INTO test_rum (t) VALUES ('345 qwerty');

INSERT INTO test_rum (t) VALUES ('A fat cat has just eaten a rat.');

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', 'bar');

count

-------

1

(1 row)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', 'qwerty&345');

count

-------

2

(1 row)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', '345');

count

-------

2

(1 row)

SELECT count(*) FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', 'rat');

count

-------

1

(1 row)

SELECT a FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', 'bar') ORDER BY a;

a

------------------------------

'bar':2,8 'foo':1,3,6 'qq':7

(1 row)

DELETE FROM test_rum;

SELECT count(*) from test_rum;

count

-------

0

(1 row)

CREATE TABLE tst (i int4, t tsvector);

INSERT INTO tst SELECT i%10, to_tsvector('simple', substr(md5(i::text), 1, 1)) FROM generate_series(1,100000) i;

CREATE INDEX tstidx ON tst USING rum (t rum_tsvector_ops);

DELETE FROM tst WHERE i = 1;

VACUUM tst;

INSERT INTO tst SELECT i%10, to_tsvector('simple', substr(md5(i::text), 1, 1)) FROM generate_series(10001,11000) i;

DELETE FROM tst WHERE i = 2;

VACUUM tst;

INSERT INTO tst SELECT i%10, to_tsvector('simple', substr(md5(i::text), 1, 1)) FROM generate_series(11001,12000) i;

DELETE FROM tst WHERE i = 3;

VACUUM tst;

INSERT INTO tst SELECT i%10, to_tsvector('simple', substr(md5(i::text), 1, 1)) FROM generate_series(12001,13000) i;

DELETE FROM tst WHERE i = 4;

VACUUM tst;

INSERT INTO tst SELECT i%10, to_tsvector('simple', substr(md5(i::text), 1, 1)) FROM generate_series(13001,14000) i;

DELETE FROM tst WHERE i = 5;

VACUUM tst;

INSERT INTO tst SELECT i%10, to_tsvector('simple', substr(md5(i::text), 1, 1)) FROM generate_series(14001,15000) i;