Re: Suboptimal evaluation of CASE expressions - Mailing list pgsql-hackers

From Andreas Tille
Subject Re: Suboptimal evaluation of CASE expressions
Date
Msg-id Pine.LNX.4.62.0604111809380.13764@wr-linux02
Whole thread Raw
In response to Re: Suboptimal evaluation of CASE expressions  (Martijn van Oosterhout <kleptog@svana.org>)
List 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.

-- 
http://fam-tille.de


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Suboptimal evaluation of CASE expressions
Next
From: Gaetano Mendola
Date:
Subject: RH9 postgresql 8.0.7 rpm