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

[Håvard Wahl Kongsgård]

Thanks much better performance with UNION ALL

-Håvard

On Sat, Oct 9, 2010 at 7:40 PM, Paragon Corporation <lr at pcorp.us> wrote:

>  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?
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101009/f527a6e0/attachment.html>