Re: optimizing queries using IN and EXISTS

I realize there is a case where IN returns NULL and EXISTS returns FALSE (in case there is a matching bar with bar.value set to NULL)
In that case however, this would result in the foo row not being included in the resultset, which is the same outcome in both cases.
NOT IN vs NOT EXISTS is another story, I agree.
So even though the subqueries aren't logically equivalent, I still believe the queries in their totality are.

Is this reasoning (knowing the NULL will be treated as FALSE by the WHERE) a bridge too far for the optimizer?
I retested with bar.value declared as NOT NULL but that doesn't seem to help.

Even though this is a bit disappointing, I think it gave me a feel of what the optimizer knows about and takes into consideration and what not ...

With kind regards,

Nick
________________________________________
Van: Peter Geoghegan [peter(at)2ndquadrant(dot)com]
Verzonden: woensdag 18 juli 2012 20:40
Aan: Nick Hofstede
CC: pgsql-performance(at)postgresql(dot)org
Onderwerp: Re: [PERFORM] optimizing queries using IN and EXISTS

On 18 July 2012 17:10, Nick Hofstede <Nick(dot)Hofstede(at)inventivegroup(dot)com> wrote:
> Hi,
>
> I'm surprised at the difference in speed/execution plan between two logically equivalent queries, one using IN, the other using EXISTS. (At least I think they are logically equivalent)

They are not logically equivalent.

http://www.postgresql.org/docs/current/static/functions-subquery.html

See the notes about NULL under IN.

--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

________________________________

Inventive Designers' Email Disclaimer:
http://www.inventivedesigners.com/email-disclaimer