Re: Different execution plans for semantically equivalent queries

On Mon, Feb 7, 2011 at 00:03, Mikkel Lauritsen <renard(at)tala(dot)dk> 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.

> 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?

There are some reasonable and generic optimizations that could be done
here. Being able to inline subqueries with aggregates into joins would
be a good thing e.g. transform your query into this:

SELECT t1.* FROM table t1 JOIN table t2 ON (t2.type = t1.type)
WHERE t2.timestamp > t1.timestamp
GROUP BY t1.* HAVING COUNT(t2.*)=0

However, this is probably still worse than a NOT EXISTS query.

I am less excited about turning "COUNT(x)=0" query to NOT EXISTS
because that's just a bad way to write a query.

Regards,
Marti