Search Postgresql Archives

Re: Find similar records (compare tsvectors)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Resend.

How to quickly compare the similarity of two tsvector?


On Monday, March 2, 2015 11:01 PM, Patrick Dung <patrick_dkt@xxxxxxxxxxxx> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux