Re: Suboptimal evaluation of CASE expressions

On Tue, 11 Apr 2006, Martijn van Oosterhout wrote:

> Because there were no non-null rows, the system passed a NULL to the
> final func. Seems you have two ways of dealing with this. Mark the
> finalfunc as STRICT so the system won't call it with NULL. Or give the
> agrregate an INITCOND which is an empty array. This would also avoid
> the NULL.

Ah.  Thanks, this might help for the original problem.

> The problem in your example is that you're using aggrgates in the case
> statement. Which means that as each row is processed, the aggregates
> need to be calculated. It can't shortcut because if it first calculated
> the max() and then the median() it would have to evaluate the entire
> query twice.

A this sounds be reasonable.  So my assumption might have been wrong.

> In the general case, PostgreSQL *may* avoid calculating redundant
> clauses if it doesn't need to, but you can't rely on it.

Just theoretically spoken: Woouldn't it make sense to enforce to
avoid this calculation.

> Fixing your underlying issue with the aggregate should solve everything
> for you.

Sure.  I hope that I was able to trigger some ideas about optimisation
anyway.

Thanks for the quick help
         Andreas.

-- 
http://fam-tille.de