Re: Supporting = operator in gin/gist_trgm_ops

On Sat, Nov 14, 2020 at 6:07 PM Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
>
> 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).

Thanks everyone for the review, and thanks Alexander for the modifications!

I agree that it's important to document that those indexes may be less
performant than btree indexes. I also agree that there's an
extraneous "the" in the documentation. Maybe this rewrite could be
better?

Note that those indexes may not be as afficient as regulat B-tree indexes
for equality operator.

While at it, there's a small grammar error:

case EqualStrategyNumber:
- /* Wildcard search is inexact */
+ /* Wildcard and equal search is inexact */

It should be /* Wildcard and equal search are inexact */