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:38:08 |
Message-ID: | CAOBaU_Y9wO+Y-ihwCxQ=nDpjv301zEYKuiGPS8KrUjp1KieB7A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Oct 26, 2020 at 12:19 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Julien Rouhaud <rjuju123(at)gmail(dot)com> writes:
> > On Mon, Oct 26, 2020 at 5:03 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> 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.
>
> But "does it get the right answers" isn't the only figure of merit.
> If the index scan visits far more rows than necessary, that's bad.
> Maybe it's OK given that we only make trigrams from alphanumerics,
> but I'm not quite sure.
Ah, yes this might lead to bad performance if the "fake wildcard"
matches too many rows, but this shouldn't be a very common use case,
and the only alternative for that might be to create trigrams for non
alphanumerics characters. I didn't try to do that because it would
mean meaningful overhead for mainstream usage of pg_trgm, and would
also mean on-disk format break. In my opinion supporting = should be
a best effort, especially for such corner cases.
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2020-10-26 07:18:00 | Re: [doc] remove reference to pg_dump pre-8.1 switch behaviour |
Previous Message | Tom Lane | 2020-10-26 04:19:48 | Re: Supporting = operator in gin/gist_trgm_ops |