Search Postgresql Archives

Re: Find similar records (compare tsvectors)

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

 



On Fri, Mar 6, 2015 at 5:05 PM, Patrick Dung <patrick_dkt@xxxxxxxxxxxx> 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@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
>
>


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[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