Performance Tests on Distance

rect_node_distance.sql

-- Large primary geometry

select st_distance(e.geom, v.geom) 
from ed_2017 e, va_ply_17 v 
where e.ed_abbrev = 'KLA' and v.ed_abbrev != 'KLA';
-- 25s

select _ST_DistanceRectTree(e.geom, v.geom) 
from ed_2017 e, va_ply_17 v 
where e.ed_abbrev = 'KLA' and v.ed_abbrev != 'KLA';
-- 6s

select _ST_DistanceRectTreeCached(e.geom, v.geom) 
from ed_2017 e, va_ply_17 v 
where e.ed_abbrev = 'KLA' and v.ed_abbrev != 'KLA';
-- 2.7s

---------------------------------------------------------------------

-- small primary geometry (5pt) 

select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v 
where e.edva_code = 'NEW030' and v.ed_abbrev != 'NEW030';
-- 320ms

select Sum(_ST_DistanceRectTree(e.geom, v.geom)) 
from va_ply_17 e, va_ply_17 v 
where e.edva_code = 'NEW030' and v.ed_abbrev != 'NEW030';
-- 454ms

select Sum(_ST_DistanceRectTreeCached(e.geom, v.geom)) 
from va_ply_17 e, va_ply_17 v 
where e.edva_code = 'NEW030' and v.ed_abbrev != 'NEW030';
-- 432ms

---------------------------------------------------------------------

-- medium primary geometry (21pt)

select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v 
where e.edva_code = 'VFA064' and v.ed_abbrev != 'VFA064';
-- 480ms

select Sum(_ST_DistanceRectTree(e.geom, v.geom)) 
from va_ply_17 e, va_ply_17 v 
where e.edva_code = 'VFA064' and v.ed_abbrev != 'VFA064';
-- 480ms

select Sum(_ST_DistanceRectTreeCached(e.geom, v.geom)) 
from va_ply_17 e, va_ply_17 v 
where e.edva_code = 'VFA064' and v.ed_abbrev != 'VFA064';
-- 470ms

---------------------------------------------------------------------

-- All pairs between 25 and 30 pts

select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 25 and 30)
and (st_npoints(v.geom) between 25 and 30);
-- 2.5s

select Sum(_ST_DistanceRectTree(e.geom, v.geom)) 
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 25 and 30)
and (st_npoints(v.geom) between 25 and 30);
-- 8.7s


select Sum(_ST_DistanceRectTreeCached(e.geom, v.geom)) 
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 25 and 30)
and (st_npoints(v.geom) between 25 and 30);
-- 7.6s


---------------------------------------------------------------------

-- All pairs between 100 and 110 pts

select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 100 and 120)
and (st_npoints(v.geom) between 100 and 120);
-- 1s

select Sum(_ST_DistanceRectTree(e.geom, v.geom)) 
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 100 and 120)
and (st_npoints(v.geom) between 100 and 120);
-- 3s


select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 100 and 120)
and (st_npoints(v.geom) between 100 and 120);
-- 1s

select Sum(_ST_DistanceRectTree(e.geom, v.geom)) 
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 100 and 120)
and (st_npoints(v.geom) between 100 and 120);
-- 3s


-- All pairs between 1000 and 2000 pts

select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 1000 and 2000)
and (st_npoints(v.geom) between 1000 and 2000);
-- 25s

select Sum(_ST_DistanceRectTree(e.geom, v.geom)) 
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 1000 and 2000)
and (st_npoints(v.geom) between 1000 and 2000);
-- 7s

select Sum(_ST_DistanceRectTreeCached(e.geom, v.geom)) 
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 1000 and 2000)
and (st_npoints(v.geom) between 1000 and 2000);
-- 6s

---------------------------------------------------------------------

-- All pairs between 2500 and 5000 pts

select sum(st_distance(e.geom, v.geom))
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 2500 and 5000)
and (st_npoints(v.geom) between 2500 and 5000);
-- 96s

select Sum(_ST_DistanceRectTree(e.geom, v.geom)) 
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 2500 and 5000)
and (st_npoints(v.geom) between 2500 and 5000);
-- 9s

select Sum(_ST_DistanceRectTreeCached(e.geom, v.geom)) 
from va_ply_17 e, va_ply_17 v 
where (st_npoints(e.geom) between 2500 and 5000)
and (st_npoints(v.geom) between 2500 and 5000);
-- 8s