Re: Suboptimal evaluation of CASE expressions

On Tue, Apr 11, 2006 at 11:22:53PM +0200, Andreas Tille wrote:
> On Tue, 11 Apr 2006, Tom Lane wrote:
>
> >We could maybe change things so that the finalfunc isn't run unless the
> >result value is actually demanded in the SELECT list or HAVING clause,
> >but for 99.99% of applications checking that would be a waste of cycles,
> >so I'm disinclined to do it.
>
> I'm lacking experience here so I perfectly trust you that keeping
> the default case as it is.  The question is, whether adding an
> option to change the default might make sense.

Can you give an example of a simple case where PostgreSQL doesn't do
this already. For the really obvious cases without aggregates, it works
already:

test=# select case when true then 5 else 1/0 end;case
------   5
(1 row)

test=# select case when false then 5 else 1/0 end;
ERROR:  division by zero

What we're saying is that as long as the SQL standard doesn't require
it, we're not going to write large chunks of code to avoid a small
amount of processing that nobody is going to notice anyway. i.e. you
can't *rely* on this behaviour, but improvement is merely an
optimisation, not a feature or a bug fix.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.