CODEWITHSUNDEEP
postgresqlfull-text-searchstemmingtsvector
Nicola Lepetit
Nicola Lepetit

Reputation: 795

PostgreSQL - Compare ts_vector fields

I have two tables in which I have data coming from two different sources. One of the field of each table contains the title of a movie, but for some reason out of my control, the titles are not always exactly the same.

So I use the ts_vector to get rid of all the minor differences (stop words, plurals and so on).

See an example here: http://sqlfiddle.com/#!17/5ccbc/3

My problem is how to compare the two ts_vector without taking into account the numberic values, but just the text content. If I compare directly the two fields, I only get the exact match between values, including position of each word. The only solution I have found is using the strip() function, that remove positions and weights from tsvector, leaving only the text content.

I was wondering if there is a fastest way to compare ts_vectors.

Upvotes: 0

Views: 540

Answers (1)

jjanes
jjanes

Reputation: 44305

You could create in index on the stripped vector:

create index on tbl1 (strip(ts_title));
create index on tbl2 (strip(ts_title));

But given that your query has to fetch every row of each table, it is unlikely this would serve much of a point. Doing a merge join between the precomputed stripped vectors could be faster, but probably not once you include the overhead of building and maintaining the indexes. If the real WHERE clause is more restrictive (selecting only a few rows from one or the other of the tables) then please share the real query.

Upvotes: 0

Related Questions