Re: Patch to support SEMI and ANTI join removal

Jim Nasby <jim@nasby.net> writes:
> On 8/26/14, 8:40 AM, Heikki Linnakangas wrote:
>> Just so everyone is on the same page on what kind of queries this helps with, here are some examples from the added
regressiontests:
 
>> 
> -- Test join removals for semi and anti joins
> CREATE TEMP TABLE b (id INT NOT NULL PRIMARY KEY, val INT);
> CREATE TEMP TABLE a (id INT NOT NULL PRIMARY KEY, b_id INT REFERENCES b(id));
> -- should remove semi join to b
> EXPLAIN (COSTS OFF)
> SELECT id FROM a WHERE b_id IN(SELECT id FROM b);
> <snip>
> SELECT id FROM a WHERE EXISTS(SELECT 1 FROM b WHERE a.b_id = id);

> I also fail to see a use for examples that are that silly *unless* we're talking machine-generated SQL, but I suspect
thatnormally uses JOINS.
 

> Where I would expect this to be useful is in cases where we can pre-evaluate some other condition in the subqueries
tomake the subqueries useless (ie: SELECT id FROM b WHERE 1=1), or where the condition could be passed through (ie:
SELECTid FROM b WHERE id=42). Another possibility would be if there's a condition in the subquery that could trigger
constraintelimination.
 

Unless I'm misunderstanding something, pretty much *any* WHERE restriction
in the subquery would defeat this optimization, since it would no longer
be certain that there was a match to an arbitrary outer-query row.  So
it seems unlikely to me that this would fire in enough real-world cases
to be worth including.  I am definitely not a fan of carrying around
deadwood in the planner.

If the majority of the added code is code that will be needed for
less-bogus optimizations, it might be all right; but I'd kind of want to
see the less-bogus optimizations working first.
        regards, tom lane