From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: pgsql-performance@postgresql.org
Subject: Fast tsearch2, trigram matching on short phrases
Date: Wed, 22 Aug 2007 12:02:54 -0400
Message-ID: <fahmm4$ffo$1@news.hub.org> (raw)
I have read that trigram matching (similarity()) performance degrades when
the matching is on longer strings such as phrases. I need to quickly match
strings and rate them by similiarity. The strings are typically one to seven
words in length - and will often include unconventional abbreviations and
misspellings.
I have a stored function which does more thorough testing of the phrases,
including spelling correction, abbreviation translation, etc... and scores
the results - I pick the winning score that passes a pass/fail constant.
However, the function is slow. My solution was to reduce the number of rows
that are passed to the function by pruning obvious mismatches using
similarity(). However, trigram matching on phrases is slow as well.
I have experimented with tsearch2 but I have two problems:
1) I need a "score" so I can decide if match passed or failed. trigram
similarity() has a fixed result that you can test, but I don't know if
rank() returns results that can be compared to a fixed value
2) I need an efficient methodology to create vectors based on trigrams, and
a way to create an index to support it. My tsearch2 experiment with normal
vectors used gist(text tsvector) and an on insert/update trigger to populate
the vector field.
Any suggestions on where to go with this project to improve performance
would be greatly appreciated.
Carlo
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stonec.register@sympatico.ca
Subject: Re: Fast tsearch2, trigram matching on short phrases
In-Reply-To: <fahmm4$ffo$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox