Tom,
Thank you.
I shall experiment.
> "Harvey, Allan AC" <[EMAIL PROTECTED]> 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
>
The material contained in this email may be confidential, privileged or
copyrighted. If you are not the intended recipient, use, disclosure or copying
of this information is prohibited. If you have received this document in error,
please advise the sender and delete the document. Neither OneSteel nor the
sender accept responsibility for any viruses contained in this email or any
attachments.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
