Re: 'Expensive' column in result set

"Harvey, Allan AC" <HarveyA@OneSteel.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