[postgis-users] Query performance really slow..

[Sandro Santilli]

On Thu, Mar 13, 2014 at 06:59:50AM -0700, Pietro Rossin wrote:
> 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)

There's no way to use indexes because there's no WHERE condition.
Also, I guess your index is on varie.particellepac2006.geom, not
on the collection grouped by "descrizione", so you should avoid
the collection. After all you're summing the areas so it doesn't
matter to you if you sum the areas of the intersection of each
pair of intersecting polygons.

Try this one:

 SELECT sum(st_area(ST_Intersection(b.geom,c.geom))) as
  b.areacoltura, b.codice_bac, b.nome, b.codice_cor, c.descrizione
 FROM
  idrologia.bacini_elementari b,
  varie.particellepac2006 c
 WHERE
  ST_Intersects(b.geom,c.geom)
 GROUP BY
   2,3,4,5,6

Check EXPLAIN of it.

I'm not sure about the GROUP BY part, but you get the idea.

--strk;