Re: Supporting = operator in gin/gist_trgm_ops

Hi, Erik!

On Sat, Nov 14, 2020 at 11:37 AM Erik Rijkers <er(at)xs4all(dot)nl> wrote:
> On 2020-11-14 06:30, Alexander Korotkov wrote:
>
> > [v4-0001-Handle-equality...in-contrib-pg_trgm.patch (~]
> >
> > I'm going to push this if no objections.
> >
>
> About the sgml, in doc/src/sgml/pgtrgm.sgml :
>
>
> Beginning in <productname>PostgreSQL</productname> 14, these indexes
> also support equality operator (simple comparison operators are not
> supported).
>
> should be:
>
> Beginning in <productname>PostgreSQL</productname> 14, these indexes
> also support the equality operator (simple comparison operators are not
> supported).
>
> (added 'the')
>
>
> And:
>
> Although these indexes might have lower the performance of equality
> operator
> search than regular B-tree indexes.
>
> should be (I think - please check the meaning)
>
> Although these indexes might have a lower performance with equality
> operator
> search than with regular B-tree indexes.
>
>
> I am not sure I understood this last sentence correctly. Does this mean
> the slower trgm index might be chosen over the faster btree?

Thank you for your review.

I mean searching for an equal string with pg_trgm GiST/GIN might be
slower than the same search with B-tree. If you need both pg_trgm
similarity/pattern search and equal search on your column, you have
choice. You can run with a single pg_trgm index, but your search for
an equal string wouldn't be as fast as with B-tree. Alternatively you
can have two indexes: pg_trgm index for similarity/pattern search and
B-tree index for equality search. Second option gives you a fast
equality search, but the second B-tree index would take extra space
and maintenance overhead. For equality search, the B-tree index
should be chosen by the planner (and that was tested).

------
Regards,
Alexander Korotkov