On Mon, Oct 26, 2020 at 12:19 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > Julien Rouhaud <rjuju...@gmail.com> writes: > > On Mon, Oct 26, 2020 at 5:03 AM Tom Lane <t...@sss.pgh.pa.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.
