On 06/26/2013 12:52 AM, Tom Lane wrote:
>> Instead of setting it aside, can we (mis)use placeholder var (PHV), to
>> ensure that the WHERE clause is evaluated below the OJ; instead of
>> combining it with the ON clause?
> No, that doesn't help; it has to be part of the joinquals at the join
> node, or you don't get the right execution semantics.
When I wrote 'below the OJ' I meant to retain something of the original
semantics (just like the subquery applies the WHERE clause below the OJ).
However that's probably too restrictive and your next arguments
> Plus you could
> lose some optimization opportunities, for example if we fail to see
> that there's a strict join clause associated with the outer join
> (cf lhs_strict). Worse, I think wrapping a PHV around an otherwise
> indexable clause would prevent using it for an indexscan.
>
also confirm the restrictiveness. So I can forget.
One more concern anyway: doesn't your proposal make subquery pull-up a
little bit risky in terms of cost of the resulting plan?
IMO the subquery in the original query may filter out many rows and thus
decrease the number of pairs to be evaluated by the join the ON clause
belongs to.
If the WHERE clause moves up, then the resulting plan might be less
efficient than the one we'd get if the subquery hadn't been pulled-up at
all.
However at the time of cost evaluation there's no way to get back (not
even to notice the higher cost) because the original subquery has gone
at earlier stage of the planning.
Regards,
Antonin Houska (Tony)