MIME-Version: 1.0
In-Reply-To: <574C7E37.7080102@vboehm.de>
References: <574C7E37.7080102@vboehm.de>
Date: Mon, 30 May 2016 13:05:41 -0700
Message-ID: 
 <CAMkU=1wtKJpkjBoL7ubjbZS=rOMAsNKum-BXZUQkpW70gntzSQ@mail.gmail.com>
Subject: Re: similarity and operator '%'
From: Jeff Janes <jeff.janes@gmail.com>
To: Volker Boehm <volker@vboehm.de>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Mon, May 30, 2016 at 10:53 AM, Volker Boehm <volker@vboehm.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


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