To: <pgsql-performance@postgresql.org>
Subject: Re: Different execution plans for semantically equivalent queries
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Date: Sun, 06 Feb 2011 23:03:18 +0100
From: Mikkel Lauritsen <renard@tala.dk>
In-Reply-To: <15642.1297027741@sss.pgh.pa.us>
References: <bbe20a8f4ef774a3f221f637b1d1cac1@localhost>
	<15642.1297027741@sss.pgh.pa.us>
Message-ID: <ceef13d62cc11f34e221b688db527bb0@localhost>
User-Agent: RoundCube Webmail/0.3.1

Hi Tom et al,

Many thanks for your prompt reply - you wrote:

>> SELECT * FROM table t1 WHERE 0 = (SELECT COUNT(*) FROM table t2 WHERE
>>     t2.type = t1.type AND t2.timestamp > t1.timestamp)
> 
> I suspect that *any* database is going to have trouble optimizing that.

Okay, I expected that much.

Just out of curiosity I've been looking a bit at the optimizer code
in PostgreSQL, and it seems as if it would be at least theoretically
possible to add support for things like transforming the query at
hand into the NOT EXISTS form; a bit like how = NULL is converted
to IS NULL.

Would a change like that be accepted, or would you rather try to
indirectly educate people into writing better SQL?

> You'd be well advised to lobby the persistence framework's authors to
> produce less brain-dead SQL.  The NOT EXISTS formulation seems to
> express what's wanted much less indirectly.

Will do :-)

For now I guess I'll hack it by wrapping a proxy around the JDBC
driver and rewriting the SQL on the fly; I encounter other bugs in
the persistence layer that are probably best handled that way as
well.

Best regards & thanks,
  Mikkel