[postgis-users] Point to Point Distance Query (optimization possible?)

[Andy Colson]

On 2/8/2012 2:10 AM, Andreas Forø Tollefsen wrote:
> Hi,
>
> I am working on a query where i want to measure the distance from a set
> of points, to the nearest point having a spi6 value over 0.
> The priogrid includes the points and the spi6_cell identifies which
> points have a spi6 over 0.
>
> My question is whether i can optimize this for better performance?
> As for now it takes 66 minutes for one year. Having 53 years that would
> take like 58 hours to complete.
> Using my St_DWithin(), does it restrict the distance measures to only
> points within 500 km?
>
> My query:
> SELECT p.gid, s.gridyear, MIN(ST_Distance(p.centroid::geography,
> p2.centroid::geography))/1000 AS distance
> FROM priogrid p, priogrid p2, spi6_cell s
> WHERE p2.gid = s.gid AND s.spi6 >= 0.5 AND s.gridyear = 1952 AND
> ST_DWithin(p.centroid::geography, p2.centroid::geography, 500000)
> GROUP BY p.gid, s.gridyear
> ORDER BY distance;
>
> "POSTGIS="2.0.0SVN" GEOS="3.4.0dev-CAPI-1.8.0" PROJ="Rel. 4.7.1, 23
> September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8"
> USE_STATS (procs from 2.0 r need upgrade)"
> PostGIS Revision 8622
> "PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
> (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit"
>
> Any help would be very appreciated.
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

If you watch this run, is it CPU bound or IO bound?

ST_DWithin will use an index, as long as you have one on 
priogrid.centroid, which I assume you do, right?

Can you run a smaller subset, and post an EXPLAIN ANALYZE?

Does the box you are running on have multiple cores?  One client will 
only use one core, but you could run multiple years at once (assuming 
you are CPU bound.  If you are IO bound this'll only make it worse).


-Andy