[postgis-users] Query performance really slow..

[Rémi Cura]

Again:
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)

It is very well explained in the doc linked...
Cheers,
Rémi-C


2014-03-18 14:28 GMT+01:00 Humberto Cereser Ibanez <
humberto at pastoraldacrianca.org.br>:

> Hello Pietro,
> Em Ter, 2014-03-18 às 05:46 -0700, Pietro Rossin escreveu:
> > 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.
> I suggest you take advantage of your 64bit processor.
> http://h18000.www1.hp.com/products/quickspecs/12357_na/12357_na.PDF
> My DBMS PostgreSql/PostGis runs over a Debian 64bit.
> >
> >
> > Thanks!
> >
> > Pietro
> >
> >
> >
> Good luck,
>
> 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/20140318/33d985a7/attachment.html>