Re: Anti join confusion

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Feb 26, 2025 at 4:58 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Yeah. The key problem blocking doing something about it in the
>> planner is that at the time we want to do join tree restructuring,
>> we haven't yet collected the per-relation data that would allow
>> us to know about NOT NULL constraints, nor run expression
>> simplification that would be important for detecting all but the
>> most basic cases. Doing either of those things twice seems like
>> a nonstarter from a planner-speed viewpoint. It's possible that
>> we could rearrange things to collect catalog data sooner.
>> (I'm vaguely imagining that we'd collect that stuff by relation
>> OID to begin with, and then when we build RelOptInfos later on
>> we could link to the per-rel-OID catalog data.) Moving expression
>> simplification up seems considerably more fraught, but maybe we could
>> handle the cases of common interest without having done that.

> Oh, this is very helpful! I didn't realize that there were specific
> technical obstacles standing in the way of making this happen; I was
> assuming it was just a case of nobody having been interested enough to
> write the code. That's good to know.

Well, there's that too ;-). I would not be suggesting something
requiring significant planner restructuring if the only benefit
were to be able to turn some instances of NOT IN into antijoins.
But I think having NOT NULL info available earlier might allow
removal of some nasty kluges (I'm looking at you,
restriction_is_always_true).

regards, tom lane