[postgis-users] Query performance really slow..

[Humberto Cereser Ibanez]

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