[postgis-users] Slow spatial join with subquery and UNION

[Paragon Corporation]

Don't use UNION
 
Try doing a UNION ALL
 
UNION puts in an implicit DISTINCT which is wrong for geometries anyway
since it would just be bounding box distinct.
 
Leo
http://www.postgis.us
 

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Håvard
Wahl Kongsgård
Sent: Saturday, October 09, 2010 1:34 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Slow spatial join with subquery and UNION


SELECT COUNT(DISTINCT SPATIAL_POINTS_1.id) from SPATIAL_POINTS_1 as
SPATIAL_POINTS_1 Inner join (Select geom from SPATIAL_POINTS_2 UNION SELECT
geom from SPATIAL_POINTS_3) as SPATIAL_POINTS_X on
ST_DWithin(SPATIAL_POINTS_1.the_geom, SPATIAL_POINTS_X.geom, 500)

This UNION subquery is very slow, there are indexes for each geom. If I
skip the UNION it's much faster.

Is there an alternative way to do this?

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101009/16c22031/attachment.html>