Re: 'Expensive' column in result set

"Harvey, Allan AC" <HarveyA(at)OneSteel(dot)com> writes:
> select
> b.block,
> b.p_code,
> p.description,
> p.blk_speed as "set",
> blk_speed( b.block ) as "actual",
> blk_speed( b.block ) / ( p.blk_speed + 0.0001 ) * 100 as "Speed %"
> from block b, product p
> where b.p_code = p.p_code;
>
> OR is postgres smart enough to know it only needs to get blk_speed() once.

No, PG will not stop to notice the common subexpression. (Searching for
such would eat more cycles than it saves, on average.) What you can do
is use a two-level select:

select
block,
p_code,
description,
col as "set",
func as "actual",
func / ( col + 0.0001 ) * 100 as "Speed %"
from
(select
b.block,
b.p_code,
p.description,
p.blk_speed as col,
blk_speed( b.block ) as func
from block b, product p
where b.p_code = p.p_code) as ss;

(The example would've been clearer if you'd not used the same name for
both a column and a function; but I digress.)

Now as this is written, the optimizer is likely to flatten the two-level
select into one level and thereby copy the blk_speed function call into
two places, which you don't want. The best workaround for that is to
add "offset 0" to the sub-select. Another possibility (as of PG 8.2) is
to mark the function as volatile --- but that might prevent some
optimizations that you would like to happen, so it's probably not the
best answer.

regards, tom lane