Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 4E9DF9FB1D9
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 10 Jan 2007 10:54:17 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 01956-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 10 Jan 2007 10:53:59 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id E04359FA39B
	for <pgsql-performance@postgresql.org>;
	Wed, 10 Jan 2007 10:54:05 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l0AErgVG007072;
	Wed, 10 Jan 2007 09:53:42 -0500 (EST)
To: "Gregory S. Williamson" <gsw@globexplorer.com>
cc: "Plugge, Joe R." <JRPlugge@west.com>, pgsql-performance@postgresql.org
Subject: Re: Horribly slow query/ sequential scan 
In-reply-to: <374.1168408529@sss.pgh.pa.us> 
References: 
 <32CC45C5BDC57241B9BCEF37AC0C5DB5021844E6@OMAEXMB02.corp.westworlds.com>
	<71E37EF6B7DCC1499CEA0316A256832802B3EB9B@loki.wc.globexplorer.net>
	<374.1168408529@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Wed, 10 Jan 2007 00:55:29 -0500"
Date: Wed, 10 Jan 2007 09:53:42 -0500
Message-ID: <7071.1168440822@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/130
X-Sequence-Number: 22731

I wrote:
> ... What seems to be happening is that Informix is willing to
> flatten the sub-SELECT into an IN join even though the sub-SELECT is
> correlated to the outer query (that is, it contains outer references).

I did some googling this morning and found confirmation that recent
versions of Informix have pretty extensive support for optimizing
correlated subqueries:
http://www.iiug.org/waiug/archive/iugnew83/FeaturesIDS73.htm

This is something we've not really spent much time on for Postgres,
but it might be interesting to look at someday.  Given that the problem
with your query was really a mistake anyway, I'm not sure that your
example is compelling evidence for making it a high priority.

			regards, tom lane