Re: Do not scan index in right table if condition for left join evaluates to false using columns in left table

On 2024-12-08 15:44:23 +0700, Andrei Lepikhov wrote:
> On 8/12/2024 09:52, Andres Freund wrote:
> > > I think avoiding touching a hash table and an index under MergeJoin can also
> > > be beneficial.
> >
> > How would you get significant wins for mergejoins? You need to go through both
> > inner and outer anyway?

> In my mind, this trick can be designed for specific cases like sales tables,
> as illustrated before and used by well-rounded developers.

I'm not saying that the optimization isn't useful, just that i don't think it
makes much sense for mergeoins.

Besides, at least as far as I can see, fundamentally not optimizing mergejoins
in any substantial manner, it also just doesn't seem likely that queries where
this optimization would come up are likely to be planned as mergejoins. If you
have a leftjoin to a category-style table, you're very rarely going to scan
the "main" table with an ordered index scan on the category key, which would
be required for a merge join. And sorting the main table once for each
to-be-joined-category-table isn't a desirable path most of the time either.

I don't know what it means that it's to be "used by well-rounded
developers". We have to write the implementation in way it works regardless of
what kind of developer is using postgres.

> I'm not sure that such optimisation would be profitable in general.

Are you suggesting it would only be enabled by a GUC?

> My point is that the sales database has lots of categories, and when
> requesting product descriptions, we will not necessarily touch all the
> categories - in that case, the one-sided clause could allow us to avoid
> scanning some tables at all. Am I wrong?

No, I don't think you're wrong. I just don't think it's worthwhile for
mergejoins, because I don't see relevant query patterns where it would
help.

> BTW, may it be used in SEMI JOIN cases?

Seems possible.

Greetings,

Andres Freund