Re: [pg_trgm] Making similarity(?, ?) < ? use an index

On Fri, Jun 3, 2016 at 3:27 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Fri, Jun 3, 2016 at 12:13 PM, Greg Navis <contact(at)gregnavis(dot)com> wrote:
> > Thanks for answers and sorry for not searching hard enough.
> >
> > I'm curious ... would it be difficult to modify PostgreSQL so that it'd
> use
> > the index for `similarity(lhs, rhs) >= show_limit()` too?
>
> Yes, that would be very difficult. The project has kind of painted
> itself into a corner on that.
>
> If it were easy, I doubt we would have added the % operator with the
> ugly set_limit() wart in the first place (although I was not around at
> the time that was done--maybe there were other considerations).
>

​Can you clarify?

As far pg_trgm goes its only option was/is to use a GUC if it wants the
benefit of indexing.​ The set/show limit API is merely a syntactic
convenience.

The cleanest API I can come up with giving present limitations is:

SELECT * FROM get_restaurants_by_similarity('warsw', 70)
-- you could make the second parameter optional or disallowed depending on
how you want to enforce your selection policy.

The SQL queries in that SQL language function would be:

SET LOCAL .... = 70;
SELECT * FROM restaurants WHERE city % $1;

The later being returned as "SETOF restaurants"

You main problem here, then, is loss of optimization options.

The best solution would depend very much on how you plan to use these
queries. You also have an option to execute dynamic SQL within a pl/pgsql
function.

David J.