From: | Andreas Tille <tillea(at)rki(dot)de> |
---|---|
To: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
Cc: | PostgreSQL Hacker Liste <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Suboptimal evaluation of CASE expressions |
Date: | 2006-04-11 16:12:40 |
Message-ID: | Pine.LNX.4.62.0604111809380.13764@wr-linux02 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Gaetano Mendola | 2006-04-11 16:31:43 | RH9 postgresql 8.0.7 rpm |
Previous Message | Tom Lane | 2006-04-11 15:55:11 | Re: Suboptimal evaluation of CASE expressions |