Efficient way to simultaneously calc AVG and stddev_pop in postgres

stddev_pop() must be calculating AVG() as part of the full calc of standard deviation (unless there’s a shortcut I’m unaware of).

for context, the goal is to test for the difference of means between the these two geom columns.

Is there any way to access that in order to avoid recalculating AVG()?

here’s an example query:

select 
    avg(st_length(cons.geom)) as source_avg_length,
    avg(st_length(csn.geom)) as target_avg_length,
    stddev_pop(st_length(cons.geom)) as source_std_length,
    stddev_pop(st_length(csn.geom)) as target_std_length
from 
    received.conflation_osm_no_service cons,
    received.conflation_stress_network csn ;

and the output of EXPLAIN ANALYZE which makes me think that if I ask for avg() and stddev_pop() it will only do the avg() calc once and reuse it?:

Answer

To combine both tables in a single result you must aggregate before joining:

select *
from 
 (  
   select 
       avg(st_length(geom)) as source_avg_length,
       stddev_pop(st_length(geom)) as source_std_length
   from received.conflation_osm_no_service cons
 ) as src
cross join
 (
   select 
       avg(st_length(geom)) as target_avg_length,
       stddev_pop(st_length(geom)) as target_std_length,
   from 
       received.conflation_stress_network csn ;
 ) as tgt

or to get one row per table:

select 'source' as tablename,
    avg(st_length(geom)) as avg_length,
    stddev_pop(st_length(geom)) as std_length
from 
    received.conflation_osm_no_service cons

union all

select 'target',
    avg(st_length(geom)),
    stddev_pop(st_length(geom)),
from 
    received.conflation_stress_network csn ;