<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7600.16625"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=933423817-09102010><FONT color=#0000ff
size=2 face=Arial>Don't use UNION</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=933423817-09102010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=933423817-09102010><FONT color=#0000ff
size=2 face=Arial>Try doing a UNION ALL</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=933423817-09102010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=933423817-09102010><FONT color=#0000ff
size=2 face=Arial>UNION puts in an implicit DISTINCT which is wrong for
geometries anyway since it would just be bounding box
distinct.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=933423817-09102010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=933423817-09102010><FONT color=#0000ff
size=2 face=Arial>Leo</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=933423817-09102010><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.us">http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=933423817-09102010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Håvard Wahl Kongsgård<BR><B>Sent:</B> Saturday, October 09, 2010 1:34
PM<BR><B>To:</B> postgis-users@postgis.refractions.net<BR><B>Subject:</B>
[postgis-users] Slow spatial join with subquery and UNION<BR></FONT><BR></DIV>
<DIV></DIV>SELECT COUNT(DISTINCT SPATIAL_POINTS_1.id) from SPATIAL_POINTS_1
as<BR>SPATIAL_POINTS_1 Inner join (Select geom from SPATIAL_POINTS_2 UNION
SELECT<BR>geom from SPATIAL_POINTS_3) as SPATIAL_POINTS_X
on<BR>ST_DWithin(SPATIAL_POINTS_1.the_geom, SPATIAL_POINTS_X.geom,
500)<BR><BR>This UNION subquery is very slow, there are indexes for each geom.
If I<BR>skip the UNION it's much faster.<BR><BR>Is there an alternative way to
do this?<BR></BODY></HTML>