[postgis-users] Query performance really slow..

[Paul Ramsey]

You're doing it wrong.

http://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis/31562#31562

On Thu, Mar 13, 2014 at 6:59 AM, Pietro Rossin <pierigis at gmail.com> wrote:
> Hi all
> I'm trying to query 2 vector layers;
> b) bacini_elementari that is river basins - 327 elements
> b) particellepac2006 colture parcels - 614000 elements
>
> I want to intersect these two layers and get the sum of the area of each
> kind of colture for each river basin.
> To reduce the number of colture vectorial features I made a collect query
> (grouping by kind of colture) and I made a intersection between this
> geometry aggregation and all 327 basins.
>
> My query is:
>
> SELECT sum(st_area(ST_Intersection(bacini.geom,colture.geom))) as
> areacoltura, codice_bac, nome, codice_cor, colture.descrizione
>   FROM idrologia.bacini_elementari as bacini,
>   (SELECT st_collect(geom) as geom, descrizione
>   FROM varie.particellepac2006
>   group by 2) as colture
>   group by 2,3,4,5
>
> it's 5000000ms that the query is running and I don't have any result yet...
>
> The two geometry column have an index (but I think it's not used in this
> query)
>
> Mi pc id dual opteron with 4Gb RAM,
> "PostgreSQL 9.1.3, compiled by Visual C++ build 1500, 32-bit"
>
> "POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March
> 2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8"
> LIBJSON="UNKNOWN" TOPOLOGY RASTER"
>
>
>
> --
> View this message in context: http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users