[postgis-users] memory problems

[Till Kirchner]

Hi together,
I have a memory problem when executing a Point-In-Polygon function.
Actually I think it is a configuration problem.

The executed query is:
CREATE TABLE temp.tbl_c AS
SELECT ...
FROM
tbl_b AS b,
tbl_a  AS a
WHERE
b.the_geom && a.the_geom AND ST_Within(b.the_geom, a.the_geom)
;

The Log says following:

-- LOG
...

PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
     PortalHeapMemory: 1024 total in 1 blocks; 896 free (0 chunks); 128 used
       ExecutorState: 2085793336 total in 278 blocks; 26808 free (10 
chunks); 2085766528 used
       -- identisch zur Fehlermeldung ErrorContext
         GiST temporary context: 8192 total in 1 blocks; 8176 free (0 
chunks); 16 used
         ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
         ExprContext: 40960 total in 3 blocks; 40912 free (21 chunks); 
48 used
         ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
         ExprContext: 40960 total in 3 blocks; 32440 free (16 chunks); 
8520 used
Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used

...

MdSmgr: 8192 total in 1 blocks; 7024 free (0 chunks); 1168 used
LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
2010-11-11 11:48:33 : FEHLER:  Speicher aufgebraucht
2010-11-11 11:48:33 : DETAIL:  Fehler bei Anfrage mit Größe 16.
2010-11-11 11:48:33 : ANWEISUNG:  CREATE TABLE ...
-- 
------------------------------------------------------------------------------------------------------------------------
So the system runs out of memory for a request of size 16?
Frankly speaking I have no idea what that means.
The values shown in the "ErrorContext" are repeated in "GiST temporary 
context".
So I guess it's a problem about temporary files belonging to the used 
spatial indexes!?

The tbl_a has a fulltblsize of 10165MB (POLYGONS (Index-toast-table: 
1532MB)).
The tbl_b has a fulltblsize of 47MB (POINTS (Index-toast-table: less 
than 1MB)).

The Postgres-Version is: PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled 
by GCC gcc-4.3.real (Debian 4.3.3-5) 4.3.3
The PostGIS-Version is: POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" 
PROJ="Rel. 4.6.1, 21 August 2008" USE_STATS

The machine is a 32bit Debian with 4GB RAM.

Main settings are:
checkpoint_segments: 32
checkpoint_timeout: 120s
effective_cache_size: 2GB
maintenance_work_mem: 200MB
max_connections: 50
max_fsm_pages: 153600
shared_buffers: 131072 (1GB)
wal_buffers: 16MB
work_mem: 40MB

Thanks for any help in adcvance

Till