Re: LATERAL quals revisited - Mailing list pgsql-hackers

From Antonin Houska
Subject Re: LATERAL quals revisited
Date
Msg-id 51CAFD49.5080400@gmail.com
Whole thread Raw
In response to Re: LATERAL quals revisited  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: LATERAL quals revisited
List pgsql-hackers
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)



pgsql-hackers by date:

Previous
From: Atri Sharma
Date:
Subject: Re: A better way than tweaking NTUP_PER_BUCKET
Next
From: Markus Wanner
Date:
Subject: Re: Hash partitioning.