'Expensive' column in result set
Hi all,
I have a select that involves a column in the result set that is expensive to get.
I need to use the expensive column in a subsequent calculated column.
Is there a syntax to run the expensive function once only or does it just have to be done,
blk_speed() is the expensive function.
Example of what I would like to do, does not work of course:
select
b.block,
b.p_code,
p.description,
p.blk_speed as "set",
blk_speed( b.block ) as "actual",
actual / set * 100 as "Speed %"
from block b, product p
where b.p_code = p.p_code;
This does work
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.
Thanks
Allan
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.
"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
Tom,
Thank you.
I shall experiment.
"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 ) * 100as "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.