Re: Patch to support SEMI and ANTI join removal

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 to
makethe subqueries useless (ie: SELECT id FROM b WHERE 1=1), or where the condition could be passed through (ie: SELECT
idFROM b WHERE id=42). Another possibility would be if there's a condition in the subquery that could trigger
constraintelimination.
 

Those are the real world cases I'd expect to see from anything reasonably sane (an adjective that doesn't always apply
tosome of the users I have to support...) My $0.01 on the burden of carrying the "useless" tests and code around is
thatit doesn't seem like all that much overhead...
 
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net