Re: Allowing join removals for more join types

On 17 June 2014 11:04, David Rowley <dgrowleyml@gmail.com> wrote:
> On Wed, Jun 4, 2014 at 12:50 AM, Noah Misch <noah@leadboat.com> wrote:
>>
>> As a point of procedure, I recommend separating the semijoin support into
>> its
>> own patch.  Your patch is already not small; delaying non-essential parts
>> will
>> make the essential parts more accessible to reviewers.
>>
>
> In the attached patch I've removed all the SEMI and ANTI join removal code
> and left only support for LEFT JOIN removal of sub-queries that can be
> proved to be unique on the join condition by looking at the GROUP BY and
> DISTINCT clause.

Good advice, we can come back for the others later.

> Example:
>
> SELECT t1.* FROM t1 LEFT OUTER JOIN (SELECT value,COUNT(*) FROM t2 GROUP BY
> value) t2 ON t1.id = t2.value;

Looks good on initial look.

This gets optimized...

EXPLAIN (COSTS OFF)
SELECT a.id FROM a
LEFT JOIN (SELECT b.id,1 as dummy FROM b INNER JOIN c ON b.id = c.id
GROUP BY b.id) b ON a.id = b.id AND b.dummy = 1;

does it work with transitive closure like this..

EXPLAIN (COSTS OFF)
SELECT a.id FROM a
LEFT JOIN (SELECT b.id,1 as dummy FROM b INNER JOIN c ON b.id = c.id
GROUP BY c.id) b ON a.id = b.id AND b.dummy = 1;

i.e. c.id is not in the join, but we know from subselect that c.id =
b.id and b.id is in the join