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

On Fri, Mar 18, 2016 at 3:37 PM, Artur Zakirov <a.zakirov@postgrespro.ru> wrote:
> Hello,
>
> Unfortunately, pg_trgm can not support '=' operator. If I am not mistaken it
> is GiST and GIN limitation.

I think it is actually quite trivial to support '='.  In fact I think
that all you have to do is tap into the same code that LIKE already
uses, and let the recheck remove things which match on the LIKE
interpretation of the query string but not the equality
interpretation.

Attached is a crude patch I put together which seems to do the job,
but I haven't thoroughly tested it.

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?

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.

But in any case, it isn't a bug that pg_trgm doesn't do everything it
theoretically could do.

Cheers,

Jeff