On 17 June 2014 11:04, David Rowley <dgrowle...@gmail.com> wrote: > On Wed, Jun 4, 2014 at 12:50 AM, Noah Misch <n...@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 -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
