Use EXPLAIN to show the execution plan of a statement, and post here. EXPLAIN SELECT sum(st_area(ST_Intersection(bacini.geom,colture.geom))) as areacoltura, codice_bac, nome, codice_cor, colture.descrizione FROM drologia.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;
March 13 2014 11:00 AM, "Pietro Rossin" 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@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users