From: | Julien Rouhaud <rjuju123(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Supporting = operator in gin/gist_trgm_ops |
Date: | 2020-10-26 04:02:46 |
Message-ID: | CAOBaU_a4Uyie04iMF_Jvxyh86RB1GCeqSF0CKAs1haJWD5Jv=Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Oct 26, 2020 at 5:03 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Julien Rouhaud <rjuju123(at)gmail(dot)com> writes:
> > A french user recently complained that with an index created using
> > gin_trgm_ops (or gist_trgm_ops), you can use the index with a clause
> > like
> > col LIKE 'something'
> > but not
> > col = 'something'
>
> Huh, I'd supposed we did that already.
>
> > even though both clauses are technically identical. That's clearly
> > not a high priority thing to support, but looking at the code it seems
> > to me that this could be achieved quite simply: just adding a new
> > operator = in the opclass, with an operator strategy number that falls
> > back doing exactly what LikeStrategyNumber is doing and that's it.
> > There shouldn't be any wrong results, even using wildcards as the
> > recheck will remove any incorrect one.
>
> I think you may be overoptimistic about being able to use the identical
> code path without regard for LIKE wildcards; but certainly it should be
> possible to do this with not a lot of new code. +1.
Well, that's what I was thinking too, but I tried all the possible
wildcard combinations I could think of and I couldn't find any case
yielding wrong results. As far as I can see the index scans return at
least all the required rows, and all extraneous rows are correctly
removed either by heap recheck or index recheck.
I'm attaching a patch POC pach with regression tests covering those
combinations. I also found a typo in the 1.4--1.5 pg_trgm upgrade
script, so I'm also attaching a patch for that.
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Fix-typo-in-1.4-1.5-pg_trm-upgrade-script.patch | text/x-patch | 809 bytes |
v1-0002-Handle-operator-in-pg_trgm.patch | text/x-patch | 11.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Rouhaud | 2020-10-26 04:10:12 | Re: Supporting = operator in gin/gist_trgm_ops |
Previous Message | Peter Smith | 2020-10-26 03:25:49 | Re: [HACKERS] logical decoding of two-phase transactions |