[postgis-users] Query performance really slow..

[Humberto Cereser Ibanez]

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