I realize there is a case where IN returns NULL and EXISTS returns FALSE (in case there is a matching bar with
bar.valueset to NULL)
In that case however, this would result in the foo row not being included in the resultset, which is the same outcome
inboth 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
considerationand what not ...
With kind regards,
Nick
________________________________________
Van: Peter Geoghegan [peter@2ndquadrant.com]
Verzonden: woensdag 18 juli 2012 20:40
Aan: Nick Hofstede
CC: pgsql-performance@postgresql.org
Onderwerp: Re: [PERFORM] optimizing queries using IN and EXISTS
On 18 July 2012 17:10, Nick Hofstede <Nick.Hofstede@inventivegroup.com> wrote:
> Hi,
>
> I'm surprised at the difference in speed/execution plan between two logically equivalent queries, one using IN, the
otherusing 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