[postgis-users] need better performance

[Chris Hermansen]

AREA (Intersection... might be faster if you use AREA(ST_Intersection...

where clause you want ST_Intersects(g.the_geom, b.the_geom)

also check to make sure you have spatial indexes on your geometries in g 
and b

Beyond that, this kind of read / compose / insert of data can cause poor 
backend performance depending on your configuration.  I seem to recall 
us having a similar problem and needing to increase some PostgreSQL 
parameter related to write buffer size or number of write buffers.

Check here http://www.varlena.com/GeneralBits/Tidbits/perf.html for example.

vollbotz wrote:
> Hallo,
> i would like to use the following sql statement:
>
> create table einzellagen.alk_kombi as
> SELECT  g.oid, g.code, b.wlg_nr, b.wlg_name,
> AREA (Intersection ( g.the_geom, b.the_geom )) as f_alk_teilfläche,
> AREA (g.the_geom) as f_alk_gesamt,
> g.the_geom
> FROM  alk.v_al001 AS g, einzellagen.el_alle AS b
> where INTERSECTION ( g.the_geom, b.the_geom ) = TRUE;
>
>   
>> it should calculate the area of intersecting geometries
>>     
>>> syntax is ok
>>> number of Geometries:   6.500.000  zu 2000
>>>       
>
> Problem:  Quest need a few day - and then i stop with no result....
>   
>>> In ArcMap it works within hours....
>>>       
>
> Is there something i could change to get a better performance (hardware is
> ok)  ??
>
> Thanks / Greets
>
>   


-- 
Regards,

Chris Hermansen · chris.hermansen at timberline.ca · skype:clhermansen
tel+1.604.714.2878   ·  fax+1.604.733.0631   ·   mob+1.778.840.4625
Timberline Natural Resource Group Ltd   ·  http://www.timberline.ca
401  ·  958 West 8th Avenue  ·  Vancouver BC  ·  Canada  ·  V5Z 1E5