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
> apply c.z = 10 while scanning c.  (The reason we disallow
> clauses from forming EquivalenceClasses is exactly that we want to be
> to push any derived clauses as far down as possible.)  But once above
> outer join it's no longer necessarily the case that b.y = 10, and thus
> cannot use such EquivalenceClasses to conclude that sorting is
> (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
> the mistake of concluding b.y = 42, even though we do have an
> class for {a.x 42}.

I am not sure I understand the logic behind the above restriction
Although b.y cannot be in the EquivalenceClass as described, it still
important/possible to push down b.y = 42 into ss. In above query ss can
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)):
          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
(or rather copy) down a restriction but keep the upperlevel part of it
outer joins.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?


Reply via email to