Hey, my 2 cents : _Captain Obvious speaking : of course the postgres postgresql.conf file is correctly configured (shared memory, working memory, etc : http://www.postgresql.org/docs/9.3/static/runtime-config-resource.html)
_Second :pretty priiiiint :tada! : and now it's obvious why it is slow : cartesian product of 2 big tables _ third : very bad idea to use this group by syntax, at least on a mailing list ! (use explicit column name) 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 I vote like Sandro. Cheers, Rémi-C 2014-03-13 16:48 GMT+01:00 Pietro Rossin <pieri...@gmail.com>: > Hello > Ok I'll try.. > By the way I used the collection (that of course don't have geom index) to > reduce the possible combination of rows from bacini and colture to 327x110 > (327 basins and 110 collected features) > > By the way > I just stopped the query, tomorrow I'll try a new run with && and > intersects using indexes this way.. > Thanks to all > Pietro > > > 2014-03-13 16:17 GMT+01:00 Alexandre Neto [via PostGIS] <[hidden > email]<http://user/SendEmail.jtp?type=node&node=5005879&i=0> > >: > >> I think Humbert is right. >> >> If you don't use any condition, the query will try to preform >> ST_Intersection in every possible combination of rows from bacini and >> colture, >> basicly 327x614000 combinations. >> >> I'm not sure if you need both && and the St_intersects() tho. >> >> If I'm not wrong, && operator check if the boundary boxes of two features >> interect, but if you are using indexes in both columns St_Intersects will >> preform the same with the boundaries before doing it with the real >> geometries. >> >> Alexandre Neto >> >> >> On Thu, Mar 13, 2014 at 2:42 PM, Humberto Cereser Ibanez <[hidden >> email]<http://user/SendEmail.jtp?type=node&node=5005878&i=0> >> > wrote: >> >>> Hi Pietro Rossin, >>> >>> >>> Em Qui, 2014-03-13 às 06:59 -0700, Pietro Rossin escreveu: >>> > 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) >>> > >>> my guess is to include && and st_intersects operators as a condition on >>> your query: >>> >>> where bacini.geom && colture.geom and st_intersects(bacini.geom, >>> colture.geom) >>> >>> I have done this on a similar work. >>> lgsc.geom_buffer_0 && lgp.geom_buffer_0 and >>> st_intersects(lgsc.geom_buffer_0, lgp.geom_buffer_0) >>> >>> >>> Best regards, >>> >>> Humberto Cereser Ibanez >>> >>> >>> _______________________________________________ >>> postgis-users mailing list >>> [hidden email] <http://user/SendEmail.jtp?type=node&node=5005878&i=1> >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>> >> >> >> _______________________________________________ >> postgis-users mailing list >> [hidden email] <http://user/SendEmail.jtp?type=node&node=5005878&i=2> >> 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-tp5005872p5005878.html >> To unsubscribe from Query performance really slow.., click here. >> 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> >> > > > ------------------------------ > View this message in context: Re: Query performance really > slow..<http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872p5005879.html> > Sent from the PostGIS - User mailing list > archive<http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html>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