Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> Are you sure that postgres evaluates that subselect more than once?
> It looks to me like it returns a constant result for every row and
> hence it will be evaluated once per statement, not once per row. I'm
> no expert tho. Can someone answer this?
>
> And if the subselect changes for each row (ie. it's a correlated
> subquery) then you cannot use the variable anyway.
>
> It seems to me that if postgres doesn't consider count(*) as a
> constant then perhaps it should be taught to? Should be safe
> shouldn't it? I guess if a function in your select statemnt is
> inserting a row then there's trouble. But if there is, then the
> sum/count(*) is nonsensical anyway.
>
> Chris
>
It looks like it (7.2.x):
# time psql genline -c "select id from xxxx" > /dev/null
real 0m0.694s
user 0m0.147s
sys 0m0.025s
# time psql genline -c "select id,id||'/'||(select count(*) from xxxx)
as x from xxxx" > /dev/null
real 0m2.202s
user 0m0.263s
sys 0m0.040s
# time psql genline -c "select id,(select count(*) from bildsekvens) as
x from xxxx" > /dev/null
real 0m1.479s
user 0m0.254s
sys 0m0.047s
They were taken from a busy system, but i ran the several times showing
about the same result.
Magnus