Re: similarity and operator '%'

On Mon, May 30, 2016 at 10:53 AM, Volker Boehm <volker(at)vboehm(dot)de> wrote:

> The reason for using the similarity function in place of the '%'-operator is
> that I want to use different similarity values in one query:
>
> select name, street, zip, city
> from addresses
> where name % $1
> and street % $2
> and (zip % $3 or city % $4)
> or similarity(name, $1) > 0.8

I think the best you can do through query writing is to use the
most-lenient setting in all places, and then refilter to get the less
lenient cutoff:

select name, street, zip, city
from addresses
where name % $1
and street % $2
and (zip % $3 or city % $4)
or (name % $1 and similarity(name, $1) > 0.8)

If it were really important to me to get maximum performance, what I
would do is alter/fork the pg_trgm extension so that it had another
operator, say %%%, with a hard-coded cutoff which paid no attention to
the set_limit(). I'm not really sure how the planner would deal with
that, though.

Cheers,

Jeff