Re: 7.4 Wishlist

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