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

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