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

On Fri, Jun 3, 2016 at 3: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?
>

​Not in a way that would be useful.
​

> Or even add `is_similar(lhs, rhs, threshold)` that'd allow to change the
> threshold on a per-query basis. I might be able to block some time to
> contribute.
>

​I can see that being a useful API to add to pg_trgm. While it wouldn't
solve your indexing problem - it would at least make using cases that are
already un-indexable easier to write and comprehend. The particular
problem for the other poster was wanting two different values within the
same query - which is impossible in the current setup but would be made
possible with such a function.

I'm not sure how much effort the following would take but if we cannot
change the tie between indexes and operators maybe we can introduce ternary
operators that can be assigned to index opclasses.

Something like:

lhs % rhs # 40 => similarity(lhs, rhs, 70)
lhs % rhs # 70 => similarity(lhs, rhs, 70)

It would have the added benefit of allowing us to add the main ternary
operator <?:> instead of convoluted CASE statements for verbose functional
forms.

David J.