<div dir="ltr"><div>Hey, my 2 cents :<br></div><div>_Captain Obvious speaking : of course the postgres postgresql.conf file is correctly configured (shared memory, working memory, etc : <a href="http://www.postgresql.org/docs/9.3/static/runtime-config-resource.html">http://www.postgresql.org/docs/9.3/static/runtime-config-resource.html</a>)<br>
</div><div><br></div><div>_Second :pretty priiiiint :tada! : and now it's obvious why it is slow : cartesian product of 2 big tables<br></div><div>_ third : very bad idea to use this group by syntax, at least on a mailing list ! (use explicit column name)<br>
<br></div><div>SELECT sum(<br><div style="margin-left:80px">st_area(<br></div><div style="margin-left:80px"><div style="margin-left:40px">ST_Intersection(</div></div><div id=":2ym" class=""><div style="margin-left:160px">
bacini.geom<br>,colture.geom)<br></div><div style="margin-left:120px">)<br></div><div style="margin-left:80px">) as areacoltura<br></div><div style="margin-left:40px">, codice_bac, nome, codice_cor<br>, colture.descrizione<br>
</div>
FROM idrologia.bacini_elementari as bacini<br><div style="margin-left:40px">,(<br></div><div style="margin-left:40px"><div style="margin-left:40px">SELECT st_collect(geom) as geom, descrizione<br></div></div><div style="margin-left:80px">
FROM varie.particellepac2006<br></div><div style="margin-left:80px">GROUP BY 2<br></div><div style="margin-left:40px">) as colture<br></div>
GROUP BY 2,3,4,5<br><br></div><div id=":2ym" class="">I vote like Sandro.<br><br></div><div id=":2ym" class="">Cheers,<br>Rémi-C<br></div><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-03-13 16:48 GMT+01:00 Pietro Rossin <span dir="ltr"><<a href="mailto:pierigis@gmail.com" target="_blank">pierigis@gmail.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div><div><div><div>Hello<br></div>Ok I'll try..<br></div>By the way I used the collection (that of course don't have geom index) to reduce the possible combination of rows from <span style="font-family:arial,sans-serif;font-size:13px">bacini and </span><span style="font-family:arial,sans-serif;font-size:13px">colture to 327x</span><span style="font-family:arial,sans-serif;font-size:13px">110 (327 basins and 110 collected features)<br>
<br></span></div><span style="font-family:arial,sans-serif;font-size:13px">By the way<br></span></div><span style="font-family:arial,sans-serif;font-size:13px">I just stopped the query, tomorrow I'll try a new run with && and intersects using indexes this way..<br>
</span></div><span style="font-family:arial,sans-serif;font-size:13px">Thanks to all<br></span></div><span style="font-family:arial,sans-serif;font-size:13px">Pietro<br></span></div><div class="gmail_extra"><br><br><div class="gmail_quote">
2014-03-13 16:17 GMT+01:00 Alexandre Neto [via PostGIS] <span dir="ltr"><<a href="http://user/SendEmail.jtp?type=node&node=5005879&i=0" rel="nofollow" link="external" target="_blank">[hidden email]</a>></span>:<br>
<blockquote style="border-left:2px solid #cccccc;padding:0 1em" class="gmail_quote"><div class="">
<div>
<div dir="ltr">I think Humbert is right.<div><br></div><div>If you don't use any condition, the query will try to preform <span style="font-family:arial,sans-serif;font-size:13px">ST_Intersection</span> in every possible combination of rows from <span style="font-family:arial,sans-serif;font-size:13px">bacini and </span><span style="font-family:arial,sans-serif;font-size:13px">colture, basicly 327x</span><span style="font-family:arial,sans-serif;font-size:13px">614000 combinations</span><span style="font-family:arial,sans-serif;font-size:13px">.</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">I'm not sure if you need both && and the St_intersects() tho.</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">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.</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">Alexandre Neto</span></div></div></div></div><div class="gmail_extra"><br><br>
<div class="gmail_quote"><div><div class="h5">
<div><div>
On Thu, Mar 13, 2014 at 2:42 PM, Humberto Cereser Ibanez <span dir="ltr"><<a href="http://user/SendEmail.jtp?type=node&node=5005878&i=0" rel="nofollow" link="external" target="_blank">[hidden email]</a>></span> wrote:<br>
</div></div></div></div><blockquote style="border-left:2px solid #cccccc;padding:0 1em" class="gmail_quote"><div><div class="h5"><div><div>
Hi Pietro Rossin,<br>
<br>
<br>
Em Qui, 2014-03-13 às 06:59 -0700, Pietro Rossin escreveu:<br>
<div>> Hi all<br>
> I'm trying to query 2 vector layers;<br>
> b) bacini_elementari that is river basins - 327 elements<br>
> b) particellepac2006 colture parcels - 614000 elements<br>
><br>
> I want to intersect these two layers and get the sum of the area of each<br>
> kind of colture for each river basin.<br>
> To reduce the number of colture vectorial features I made a collect query<br>
> (grouping by kind of colture) and I made a intersection between this<br>
> geometry aggregation and all 327 basins.<br>
><br>
> My query is:<br>
><br>
> SELECT sum(st_area(ST_Intersection(bacini.geom,colture.geom))) as<br>
> areacoltura, codice_bac, nome, codice_cor, colture.descrizione<br>
> FROM idrologia.bacini_elementari as bacini,<br>
> (SELECT st_collect(geom) as geom, descrizione<br>
> FROM varie.particellepac2006<br>
> group by 2) as colture<br>
> group by 2,3,4,5<br>
><br>
> it's 5000000ms that the query is running and I don't have any result yet...<br>
><br>
> The two geometry column have an index (but I think it's not used in this<br>
> query)<br>
><br>
</div>my guess is to include && and st_intersects operators as a condition on<br>
your query:<br>
<br>
where bacini.geom && colture.geom and st_intersects(bacini.geom,<br>
colture.geom)<br>
<br>
I have done this on a similar work.<br>
lgsc.geom_buffer_0 && lgp.geom_buffer_0 and<br>
st_intersects(lgsc.geom_buffer_0, lgp.geom_buffer_0)<br>
<br>
<br>
Best regards,<br>
<br>
Humberto Cereser Ibanez<br>
</div></div></div></div><div><div><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="http://user/SendEmail.jtp?type=node&node=5005878&i=1" rel="nofollow" link="external" target="_blank">[hidden email]</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="nofollow" link="external" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></div></div></blockquote></div><br></div>
<div class="">
<div>
<br>_______________________________________________
<br>postgis-users mailing list
<br><a href="http://user/SendEmail.jtp?type=node&node=5005878&i=2" rel="nofollow" link="external" target="_blank">[hidden email]</a>
<br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="nofollow" link="external" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a>
<br>
<br>
<hr color="#cccccc" noshade size="1">
</div></div><div style="color:#444;font:12px tahoma,geneva,helvetica,arial,sans-serif"><div class=""><div>
<div style="font-weight:bold">If you reply to this email, your message will be added to the discussion below:</div>
</div></div><a href="http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872p5005878.html" rel="nofollow" link="external" target="_blank">http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872p5005878.html</a>
</div><div class=""><div><div>
<div style="color:#666;font:11px tahoma,geneva,helvetica,arial,sans-serif;margin-top:.4em;line-height:1.5em">
To unsubscribe from Query performance really slow.., <a rel="nofollow" link="external">click here</a>.<br>
<a href="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" rel="nofollow" style="font:9px serif" link="external" target="_blank">NAML</a>
</div></div></div></div></blockquote></div><br></div><div class="im HOEnZb">
<br><hr align="left" width="300">
View this message in context: <a href="http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872p5005879.html" target="_blank">Re: Query performance really slow..</a><br></div><div class="HOEnZb"><div class="h5">
Sent from the <a href="http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html" target="_blank">PostGIS - User mailing list archive</a> at Nabble.com.<br></div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>