Re: BUG #19031: pg_trgm infinite loop on certain cases

On Wed, Aug 27, 2025 at 10:17 PM Arseniy Mukhin
<arseniy(dot)mukhin(dot)dev(at)gmail(dot)com> wrote:
> On Wed, Aug 27, 2025 at 5:32 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Arseniy Mukhin <arseniy(dot)mukhin(dot)dev(at)gmail(dot)com> writes:
> > > Good point, thanks for the explanation. I forgot that there can be
> > > many attributes. And I agree, the more determinism in the system, the
> > > easier it is to work with it and the less room for bugs. OTOH it seems
> > > from the performance POV we want to have the stricter keys to be the
> > > first so we do less work and fail fast on the first keys. It looks
> > > like these two rules (excludeOnly keys LAST and more restrictive keys
> > > FIRST) are kind of in conflict with each other. I tried to do some
> > > experiments and it's seems GIN quite sensitive to it, at least in this
> > > artificial example:
> >
> > Yeah, it is. I recall seeing some comments to the effect that
> > optimizing the order of scan keys would be a good thing, but if there
> > is any code in there that tries to do so, I'm not seeing where.
> > Seems like a fertile area for future research.
> >
> > > With applying patch both queries show the same time (second one). So
> > > currently the user can tune the query by defining more restrictive
> > > keys first. With the proposed fix it looks like users will have less
> > > freedom here.
> >
> > I think most people would consider it a bug if they have to tune the
> > order of the WHERE clauses manually. The original statement of the
> > current bug was basically that: it worked in one order and not the
> > other.
> >
>
> Ok. I checked the patches. The bug is gone. Everything looks correct.

+1
Sorry for being late to the party. I skim through the thread and read
the patches. Looks correct for me.

------
Regards,
Alexander Korotkov
Supabase