Tom Lane writes:
> Attached is some material from an updated src/backend/optimizer/README
> that describes the optimization principles that the EquivalenceClass
> rewrite is depending on. Can anyone see any holes in the logic?
Sounds good, I can see no holes.
> SELECT *
> FROM a LEFT JOIN
> (SELECT * FROM b JOIN c ON b.y = c.z WHERE b.y = 10) ss
> ON a.x = ss.y
> WHERE a.x = 42;
>
> We can form the below-outer-join EquivalenceClass {b.y c.z 10} and
thereby
> apply c.z = 10 while scanning c. (The reason we disallow
outerjoin-delayed
> clauses from forming EquivalenceClasses is exactly that we want to be
able
> to push any derived clauses as far down as possible.) But once above
the
> outer join it's no longer necessarily the case that b.y = 10, and thus
we
> cannot use such EquivalenceClasses to conclude that sorting is
unnecessary
> (see discussion of PathKeys below). In this example, notice also that
> a.x = ss.y (really a.x = b.y) is not an equivalence clause because its
> applicability to b is restricted by the outer join; thus we do not
make
> the mistake of concluding b.y = 42, even though we do have an
equivalence
> class for {a.x 42}.
I am not sure I understand the logic behind the above restriction
though.
Although b.y cannot be in the EquivalenceClass as described, it still
seems
important/possible to push down b.y = 42 into ss. In above query ss can
then
even be const false (b.y=10 and b.y=42). Because of the outer join ss
can be
null. Put another way (changing ss.y to ss.w (w col in table b)):
SELECT * FROM a LEFT JOIN (SELECT * FROM b JOIN c ON b.y = c.z WHERE b.y = 10) ss ON a.x =
ss.w WHERE a.x = 42;
You can inject ss.w=42 into the ss where clause.
It seems what we want in addition to EquivalenceClasses, is logic to
push
(or rather copy) down a restriction but keep the upperlevel part of it
for
outer joins.
Andreas