[postgis-users] Query performance really slow..

[Rémi Cura]

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 <pierigis at 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 at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140313/c98d3bb5/attachment.html>