[postgis-users] Query performance really slow..

[Alexandre Neto]

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 <
humberto at pastoraldacrianca.org.br> 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
> 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/3bc79c50/attachment.html>