From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Retiring is_pushed_down
In-reply-to: <CAMbWs49pDTmHbgjXa4xjxbA+pY=7ebZ=YUg1waQqQw8DERgQ-g@mail.gmail.com>
References: <CAMbWs49pDTmHbgjXa4xjxbA+pY=7ebZ=YUg1waQqQw8DERgQ-g@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Tue, 25 Jul 2023 15:39:58 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <357071.1727381211.1@sss.pgh.pa.us>
Date: Thu, 26 Sep 2024 16:06:51 -0400
Message-ID: <357072.1727381211@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/357072.1727381211%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> When forming an outer join's joinrel, we have the is_pushed_down flag in
> RestrictInfo nodes to distinguish those quals that are in that join's
> JOIN/ON condition from those that were pushed down to the joinrel and
> thus act as filter quals.  Since now we have the outer-join-aware-Var
> infrastructure, I think we can check to see whether a qual clause's
> required_relids reference the outer join(s) being formed, in order to
> tell if it's a join or filter clause.  This seems like a more principled
> way.  (Interesting that optimizer/README actually describes this way in
> section 'Relation Identification and Qual Clause Placement'.)

Sorry for being so slow to look at this patch.  The idea you're
following is one that I spent a fair amount of time on while working
on what became 2489d76c4 ("Make Vars be outer-join-aware").  I failed
to make it work though.  Digging in my notes from the time:

-----
How about is_pushed_down?

Would really like to get rid of that, because it's ugly/sloppily defined,
and it's hard to determine the correct value for EquivClass-generated
clauses once we allow derivations from OJ clauses.  However, my original
idea of checking for join's ojrelid present in clause's required_relids
has issues:
* fails if clause is not pushed as far down as it can possibly be (and
lateral refs mean that that's hard to do sometimes)
* getting the join's ojrelid to everywhere we need to check this is messy.
I'd tolerate the mess if it worked nicely, but ...
-----

So I'm worried that the point about lateral refs is still a problem
in your version.  To be clear, the hazard is that if a WHERE clause
ends up getting placed at an outer join that's higher than any of
the OJs specifically listed in its required_relids, we'd misinterpret
it as being a join clause for that OJ although it should be a filter
clause.

The other thing I find in my old notes is speculation that we could
use the concept of JoinDomains to replace is_pushed_down.  That is,
we'd have to label every RestrictInfo with the JoinDomain of its
syntactic source location, and then we could tell if the RI was
"pushed down" relative to a particular join by seeing if the JD was
above or below that join.  This ought to be impervious to
not-pushed-down-all-the-way problems.  The thing I'd not figured
out was how to make this work with quals of full joins: they don't
belong to either the upper JoinDomain or either of the lower ones.
We could possibly fix this by giving a full join its very own
JoinDomain that is understood to be a parent of both lower domains,
but I ran out of energy to pursue that.

If we went this route, we'd basically be replacing the is_pushed_down
field with a JoinDomain field, which is surely not simpler.  But it
seems more crisply defined and perhaps more amenable to my long-term
desire to be able to use the EquivalenceClass machinery with outer
join clauses.  (The idea being that an EC would describe equalities
that hold within a JoinDomain, but not necessarily elsewhere.)

			regards, tom lane