[postgis-users] Query performance really slow..

Tue Mar 18 05:46:25 PDT 2014

Hello everybody
my query came to a result!
I followed the example by Paul Ramsey and I used a select case/else

This is the query
*******************************
SELECT sum(st_area(geom)) as areacoltura, descrizione, codice_bac, nome,
codice_cor FROM
(SELECT
a.descrizione,
b.codice_bac,
b.nome,
b.codice_cor,
CASE
WHEN ST_Within(a.geom,b.geom)
THEN a.geom
ELSE ST_Multi(ST_Intersection(a.geom,b.geom))
END AS geom
FROM varie.particellepac2006 a
JOIN idrologia.bacini_elementari b
ON a.geom && b.geom) as foo
group by descrizione, codice_bac, nome, codice_cor
*****************************

Execution time was: 3865564 ms (still really slow...)
6922 rows result.

Explain is:
"GroupAggregate  (cost=31670075.75..32322147.79 rows=20522 width=29555)"
"  Output: sum(st_area(CASE WHEN ((a.geom && b.geom) AND
_st_contains(b.geom, a.geom)) THEN a.geom ELSE
st_multi(st_intersection(a.geom, b.geom)) END)), a.descrizione,
b.codice_bac, b.nome, b.codice_cor"
"  ->  Sort  (cost=31670075.75..31673194.73 rows=1247592 width=29555)"
"        Output: a.geom, b.geom, a.descrizione, b.codice_bac, b.nome,
b.codice_cor"
"        Sort Key: a.descrizione, b.codice_bac, b.nome, b.codice_cor"
"        ->  Nested Loop  (cost=0.00..5497.61 rows=1247592 width=29555)"
"              Output: a.geom, b.geom, a.descrizione, b.codice_bac, b.nome,
b.codice_cor"
"              ->  Seq Scan on idrologia.bacini_elementari b
(cost=0.00..140.32 rows=332 width=28397)"
"                    Output: b.id, b.geom, b.objectid, b.codice_bac,
b.nome, b.ordine, b.quota_medi, b.area_kmq, b.codice_cor"
"              ->  Index Scan using sidx_particellepac2006_geom on
varie.particellepac2006 a  (cost=0.00..16.10 rows=3 width=1158)"
"                    Output: a.id_pac, a.geom, a.cod_nazion, a.foglio,
a.particella, a.sub, a.prog_polig, a.area_colt, a.cod_coltur, a.anno_foto,
a.mese_foto, a.cod_variet, a.istatp, a.stato_colt, a.descrizione,
a.shape_leng, a.shape_area"
"                    Index Cond: (a.geom && b.geom)"

Is there anything I can do to make it perform faster?

Is there some parameter to tune in Server Configuration?
I use Winxp32bit 4Gb ram, dual operon 252.

Thanks!
Pietro

2014-03-14 2:43 GMT+01:00 Paul Ramsey-3 [via PostGIS] <
ml-node+s17n5005881h35 at n6.nabble.com>:

> You're doing it wrong.
>
>
> http://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis/31562#31562
>
> On Thu, Mar 13, 2014 at 6:59 AM, Pietro Rossin <[hidden email]<http://user/SendEmail.jtp?type=node&node=5005881&i=0>>
> 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)
> >
> > Mi pc id dual opteron with 4Gb RAM,
> > "PostgreSQL 9.1.3, compiled by Visual C++ build 1500, 32-bit"
> >
> > "POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March
> > 2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8"
> > LIBJSON="UNKNOWN" TOPOLOGY RASTER"
> >
> >
> >
> > --
> > View this message in context:
> http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872.html
> > Sent from the PostGIS - User mailing list archive at Nabble.com.
> > _______________________________________________
> > postgis-users mailing list
> > [hidden email] <http://user/SendEmail.jtp?type=node&node=5005881&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=5005881&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-tp5005872p5005881.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-tp5005872p5005908.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/20140318/04429359/attachment.html>