From: Mikkel Lauritsen <renard@tala.dk>
To: pgsql-performance@postgresql.org
Subject: Re: Different execution plans for semantically equivalent queries
Date: Sun, 06 Feb 2011 23:03:18 +0100
Message-ID: <ceef13d62cc11f34e221b688db527bb0@localhost> (raw)
In-Reply-To: <15642.1297027741@sss.pgh.pa.us>
References: <bbe20a8f4ef774a3f221f637b1d1cac1@localhost>
<15642.1297027741@sss.pgh.pa.us>
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
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-performance@postgresql.org
Cc: renard@tala.dk
Subject: Re: Different execution plans for semantically equivalent queries
In-Reply-To: <ceef13d62cc11f34e221b688db527bb0@localhost>
* 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