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

On 20.03.2016 04:06, Jeff Janes wrote:
>
> 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?

Maybe 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.
>
> But in any case, it isn't a bug that pg_trgm doesn't do everything it
> theoretically could do.

Yes. And so this changes can be applied only for PostgreSQL 9.7.

>
> Cheers,
>
> Jeff
>


--
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company