[postgis-users] Query performance really slow..

[Pietro Rossin]

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] <
ml-node+s17n5005878h42 at n6.nabble.com>:

> 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<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>
>




--
View this message in context: http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872p5005879.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/ec48662c/attachment.html>