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