From: Thomas F. O'Connell <tfo@monsterlabs.com>
To: pgsql-hackers@postgresql.org
To: pgsql-sql@postgresql.org
Subject: the optimizer and exists
Date: Thu, 29 Aug 2002 11:13:03 -0500
Message-ID: <aklh69$2qt$1@news.hub.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tfo@monsterlabs.com, pgsql-hackers@postgresql.org
Subject: Re: the optimizer and exists
In-Reply-To: <aklh69$2qt$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox