[postgis-users] Query performance really slow..

[Pietro Rossin]

[image: Immagine in linea 1]


2014-03-13 15:18 GMT+01:00 Pietro Rossin <pierigis at gmail.com>:

> "GroupAggregate  (cost=324001.58..329989.92 rows=21516 width=28454)"
> "  ->  Sort  (cost=324001.58..324055.53 rows=21582 width=28454)"
> "        Sort Key: bacini.codice_bac, bacini.nome, bacini.codice_cor,
> particellepac2006.descrizione"
> "        ->  Nested Loop  (cost=59426.24..59835.94 rows=21582 width=28454)"
> "              ->  Seq Scan on bacini_elementari bacini
> (cost=0.00..138.27 rows=327 width=28397)"
> "              ->  Materialize  (cost=59426.24..59428.06 rows=66 width=57)"
> "                    ->  HashAggregate  (cost=59426.24..59427.07 rows=66
> width=1169)"
> "                          ->  Seq Scan on particellepac2006
> (cost=0.00..56355.83 rows=614083 width=1169)"
> Thanks!
>
>
> 2014-03-13 15:15 GMT+01:00 Marcelo Soares Souza [via PostGIS] <
> ml-node+s17n5005873h87 at n6.nabble.com>:
>
>> 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
>> > [hidden email] <http://user/SendEmail.jtp?type=node&node=5005873&i=0>
>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> >
>> _______________________________________________
>> postgis-users mailing list
>> [hidden email] <http://user/SendEmail.jtp?type=node&node=5005873&i=1>
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>> ------------------------------
>>  If you reply to this email, your message will be added to the
>> discussion below:
>>
>> http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872p5005873.html
>>  To unsubscribe from Query performance really slow.., click here<http://postgis.17.x6.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5005872&code=cGllcmlnaXNAZ21haWwuY29tfDUwMDU4NzJ8MjMwODY1MTQ5>
>> .
>> NAML<http://postgis.17.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>
>
>


ShotGenius_2014_03_13 15_11_07.jpg (28K) <http://postgis.17.x6.nabble.com/attachment/5005875/0/ShotGenius_2014_03_13%2015_11_07.jpg>




--
View this message in context: http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872p5005875.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140313/36611b5c/attachment.html>