Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 2DD8A9FB9A0;
	Fri, 23 Mar 2007 19:27:26 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 03205-03; Fri, 23 Mar 2007 19:27:14 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from svana.org (unknown [125.62.87.134])
	by postgresql.org (Postfix) with ESMTP id 28DA59FB7BF;
	Fri, 23 Mar 2007 19:27:18 -0300 (ADT)
Received: from kleptog by svana.org with local (Exim 4.50)
	id 1HUsDZ-00055g-QX; Sat, 24 Mar 2007 09:26:41 +1100
Date: Fri, 23 Mar 2007 23:26:41 +0100
From: Martijn van Oosterhout <kleptog@svana.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org,
	Andrea Olson <Andrea.Olson@wicourts.gov>,
	Bill Severson <Bill.Severson@wicourts.gov>,
	John Hutchins <John.Hutchins@wicourts.gov>,
	Randy Peterson <Randy.Peterson@wicourts.gov>,
	Shannon Spranger <Shannon.Spranger@wicourts.gov>
Subject: Re: EXISTS optimization
Message-ID: <20070323222641.GD9623@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
References: <4603C1A3.EE98.0025.0@wicourts.gov>
	<25339.1174686582@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="76DTJ5CE0DCVQemd"
Content-Disposition: inline
In-Reply-To: <25339.1174686582@sss.pgh.pa.us>
X-PGP-Key-ID: Length=1024; ID=0x0DC67BE6
X-PGP-Key-Fingerprint: 295F A899 A81A 156D B522  48A7 6394 F08A 0DC6 7BE6
X-PGP-Key-URL: <http://svana.org/kleptog/0DC67BE6.pgp.asc>
User-Agent: Mutt/1.5.9i
X-SA-Exim-Connect-IP: <locally generated>
X-SA-Exim-Mail-From: kleptog@svana.org
X-SA-Exim-Scanned: No (on svana.org); SAEximRunCond expanded to false
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200703/1427
X-Sequence-Number: 100911


--76DTJ5CE0DCVQemd
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Fri, Mar 23, 2007 at 05:49:42PM -0400, Tom Lane wrote:
> We don't currently try to flatten EXISTS into a unique/join plan as we
> do for IN.  I seem to recall not doing so when I rewrote IN planning
> because I didn't think it would be exactly semantically equivalent,
> but that was awhile ago.  Right at the moment it seems like it ought
> to be equivalent as long as the comparison operators are strict.

Wasn't it due to the fact that IN needs to scan through all
possibilites anyway because of its interaction with NULL, whereas
EXISTS can stop at the first row?

That would mean the subquery to be materialised would not be equivalent
if it called any non-immutable functions. It's also much less clear to
be a win in the EXISTs case. But then, that's a costs issue the planner
can deal with...

Have a nice day,
--=20
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to l=
itigate.

--76DTJ5CE0DCVQemd
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFGBFQhIB7bNG8LQkwRAmPjAJ497gV2RMuAR8/teCufxkXg0ndYvACggMPQ
4R2NOurkfXngXW4lJS38icw=
=91bh
-----END PGP SIGNATURE-----

--76DTJ5CE0DCVQemd--