From: "Thomas F. O'Connell" <tfo@monsterlabs.com>
Subject: the optimizer and exists
Date: Thu, 29 Aug 2002 11:13:03 -0500
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 49
Message-ID: <aklh69$2qt$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-US;
	rv:1.1) Gecko/20020826
To: pgsql-hackers@postgresql.org, pgsql-sql@postgresql.org

i think i might've stumbled across a tiny defect in the optimizer. 
unfortunately, i haven't the knowledge of the code to know where to 
begin looking at how to address this problem.

anyway, consider the following:

create table foo(
	id int2
);

create table bar(
	id int2
	foo_id int2 references foo( id )
);

imagine that the tables are populated.

now, consider the query

select b.foo_id
from bar b
where b.id = <some id>
and
exists(
	select *
	from foo f
	where b.foo_id = f.id
	and b.id = <some id, as above>
);

now consider the same query with "select <constant>" in place of "select 
*" in the EXISTS subquery.

explain analyze indicates that the constant version always runs a little 
bit faster. shouldn't the optimizer be able to determine that it isn't 
necessary actually to read a row in the case of EXISTS? i'm assuming 
that's where the overhead is coming into play.

i realize this is minutiae in comparison to other aspects of 
development, but it is another small performance boost that could be 
added since i imagine many people, myself included, find it more natural 
to throw in "select *" rather than "select <constant>".

i didn't see this on the current lists or TODO, but if it's a dupe, i 
apologize for the noise. i also apologize for not being able to patch 
it, myself!

-tfo