Re: BUG #14032: trigram index is not used for '=' operator

On Thu, Mar 24, 2016 at 8:11 AM, Emre Hasegeli <emre(at)hasegeli(dot)com> wrote:
>> The main problem is likely to be that there is already a really good
>> index type for speeding up equality queries (btree), and adding
>> another (generally much worse) alternative is likely to confuse the
>> planner more than anything. Is it really worth taking the performance
>> hit on executing the equality query in order to avoid just keeping a
>> second btree index?
>
> I don't think it is a problem. I think btree would usually have lower
> cost than GIN and therefore would be preferred by the planner. The
> other opclasses are providing operators for such cases. For example,
> range_ops(GiST) supports =, inet_ops(GiST) supports all basic
> comparisons.

You are quite likely correct that the planner is smart enough to not
be fooled by this. However, I don't know how to convince myself of
this to a high enough certainty. While the risk may be low in
probability, any negative result that does occur is high in magnitude.
While the benefit, to those who would benefit, is high probability,
but low in magnitude.

>
> It is likely that pg_trgm index created for pattern matching would be
> sufficient for equality for some users. We shouldn't force them to
> create an additional btree index.

True, but we also shouldn't force people with no interest in this
equality operator to take some unknown risk that the planner will be
seduced into unfavorable plans. Which is why an extension for those
who want it seems like the optimal solution. So perhaps we could add
the C code to the pg_trgm extension itself, but leave out the SQL
which enables it to be used. Then have that enabling SQL be part of a
separate extension, so people get to choose whether to use it or not.

>
>> If I could somehow turn this into an extension module that installed
>> with pg_trgm as a dependency, rather than reaching into pg_trgm's
>> internals, then it might be worthwhile putting something like this on
>> PGXN. But I don't know how to do that. And it doesn't seem
>> worthwhile to change pg_trgm itself in this way.
>
> I don't think it is useful to have such a small extension, even if it
> would be possible.
>
>> But in any case, it isn't a bug that pg_trgm doesn't do everything it
>> theoretically could do.
>
> I agree. Maybe we can add it to the next commitfest?

I won't do that myself, but anyone is welcome to polish my code and
submit it if they want to. They should also consider whether there is
more to be gained by enforcing not only that all necessary trigrams
exist, but also that no unnecessary ones do--I had not spent much time
pondering that.

Still, I would want there to be a way to turn it off. Maybe someone
has a better way of doing that than my idea of it being a separate
extension.

Cheers,

Jeff