Re: optimizing queries using IN and EXISTS - Mailing list pgsql-performance

From Nick Hofstede
Subject Re: optimizing queries using IN and EXISTS
Date
Msg-id BC885F9E3DB48248A4C9FC7F2C57215C2DAB00AD@Hoefnix.dc.intranet
Whole thread Raw
In response to Re: optimizing queries using IN and EXISTS  (Peter Geoghegan <peter@2ndquadrant.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Robert Haas
Date:
Subject: Re: Sequencial scan in a JOIN
Next
From: Tom Lane
Date:
Subject: Re: optimizing queries using IN and EXISTS