Re: Find similar records (compare tsvectors)

From: Oleg Bartunov <obartunov(at)gmail(dot)com>
To: Patrick Dung <patrick_dkt(at)yahoo(dot)com(dot)hk>
Cc: Pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Find similar records (compare tsvectors)
Date: 2015-03-06 16:06:26
Message-ID: CAF4Au4yG8nMDMHPBO5v=mH3M2mOTQsu2dHEqycOyt2O16dg73w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, Mar 6, 2015 at 5:05 PM, Patrick Dung <patrick_dkt(at)yahoo(dot)com(dot)hk> wrote:
> Resend.
>
> How to quickly compare the similarity of two tsvector?
>

check http://www.sai.msu.su/~megera/postgres/talks/pgcon-2012.pdf

>
> On Monday, March 2, 2015 11:01 PM, Patrick Dung <patrick_dkt(at)yahoo(dot)com(dot)hk>
> wrote:
>
>
> Hello,
>
> I had a database with articles or attachment stored in bytea format.
> I also had a trigger: it insert/update the tsv column when a record is
> added/updated.
> The tsv column had a GIN index.
> With this setting, I can do very fast keyword search on the tsv.
>
> Suppose I had a specific record (id=100000).
> How to list similar records based on ranking?
> In that case, I had to compare a tsvector with another tsvector.
>
> I had this SQL which make the original tsv as a text and then to tsquery,
> Then I can compare a tsv and a tsquery.
> SELECT ts_rank(i.tsv, replace(strip(original.tsv)::text, ' ', '|')::tsquery)
> as similarity, i.company, i.industry, i.post_timestamp, i.id FROM items i,
> (SELECT tsv, id FROM items WHERE id=100000) AS original WHERE i.id !=
> original.id ORDER BY similarity;
>
> items table:
> id bigint
> company varchar
> industry varchar
> description varchar
> post_timestamp timestamp
> attachment bytea
> tsv tsvector
>
> The problem is that this is very slow.
> Any comment?
>
> Thank and regards,
> Patrick
>
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Philippe EMERIAUD 2015-03-06 16:27:49 Problem JDBC, AutoCommit ON and SELECT FOR UPDATE
Previous Message Adrian Klaver 2015-03-06 14:55:20 Re: can postgresql supported utf8mb4 character sets?