out of memory problem - Mailing list pgsql-performance

Hello together,

I get an out of memory problem I don't understand.
The installed 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
It is running on a 32bit Debian machine with 4GB RAM.

Thanks for any help in advance

Till

--

-----------------------------------------------------------------------------------------------------------------------------

Main settings are as follows:
checkpoint_segments 16
checkpoint_timeout 120s
effective_cache_size 128MB
maintenance_work_mem 128MB
max_fsm_pages 153600
shared_buffers 1GB
wal_buffers 256MB
work_mem 256MB

--

-----------------------------------------------------------------------------------------------------------------------------

Used query is:
     CREATE TABLE temp.bwi_atkis0809_forestland AS
     SELECT
     b.gid AS bwi_gid,
     a.dlm0809id,
     a.objart_08,
     a.objart_09
     FROM
     bwi.bwi_pkt AS b,
     atkis.atkis0809_forestland AS a
     WHERE
     b.the_geom && a.the_geom AND ST_Within(b.the_geom, a.the_geom)
     ;
     COMMIT;

(The JOIN is a Spatial one using PostGIS-Functions)

--

-----------------------------------------------------------------------------------------------------------------------------

Full Table Sizes:
atkis0809_forestland 2835mb
bwi_pkt 47mb

--

-----------------------------------------------------------------------------------------------------------------------------

Error Message is:
FEHLER:  Speicher aufgebraucht
DETAIL:  Fehler bei Anfrage mit Größe 32.

********** Fehler **********

FEHLER: Speicher aufgebraucht
SQL Status:53200
Detail:Fehler bei Anfrage mit Größe 32.

in english:
ERROR: out of memory
detail: error for request with size 32

--

-----------------------------------------------------------------------------------------------------------------------------

The LOG looks as follows:

TopMemoryContext: 42800 total in 5 blocks; 4816 free (5 chunks); 37984 used
   CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
   TopTransactionContext: 8192 total in 1 blocks; 5520 free (0 chunks);
2672 used
   Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks);
4344 used
   Operator lookup cache: 24576 total in 2 blocks; 14072 free (6
chunks); 10504 used
   MessageContext: 65536 total in 4 blocks; 35960 free (10 chunks);
29576 used
   smgr relation table: 8192 total in 1 blocks; 2808 free (0 chunks);
5384 used
   TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0
chunks); 16 used
   Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
   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: 1833967692 total in 230 blocks; 9008 free (3
chunks); 1833958684 used
         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: 8192 total in 1 blocks; 8176 free (9 chunks); 16 used
         ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
         ExprContext: 8192 total in 1 blocks; 3880 free (4 chunks); 4312
used
   Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used
   CacheMemoryContext: 667472 total in 20 blocks; 195408 free (3
chunks); 472064 used
     pg_toast_12241534_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_shdepend_depender_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
     pg_shdepend_reference_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_depend_depender_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
     pg_depend_reference_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
     idx_atkis0809_forestland_the_geom_gist: 1024 total in 1 blocks; 136
free (0 chunks); 888 used
     atkis0809_forestland_pkey: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     btree_bwi_pkt_enr: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
     btree_bwi_pkt_tnr: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
     rtree_bwi_pkt: 1024 total in 1 blocks; 136 free (0 chunks); 888 used
     bwi_pkt_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
     pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_constraint_conrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_database_datname_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
     pg_aggregate_fnoid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_language_name_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_statistic_relid_att_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
     pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
     pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
     pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
     pg_cast_source_target_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_auth_members_role_member_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
     pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
     pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0
chunks); 896 used
     pg_language_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
     pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
     pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
     pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_database_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
     pg_class_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
     pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
     pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
     pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_auth_members_member_role_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
     pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
     pg_namespace_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_type_typname_nsp_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_amop_opr_fam_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
     pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
     pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
     pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
   MdSmgr: 8192 total in 1 blocks; 7312 free (0 chunks); 880 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 (4 chunks); 16 used
2010-11-09 11:36:10 CET FEHLER:  Speicher aufgebraucht
2010-11-09 11:36:10 CET DETAIL:  Fehler bei Anfrage mit Größe 32.
2010-11-09 11:36:10 CET ANWEISUNG:  BEGIN;
     CREATE TABLE temp.bwi_atkis0809_forestland AS
     SELECT
     b.gid AS bwi_gid,
     a.dlm0809id,
     a.objart_08,
     a.objart_09
     FROM
     bwi.bwi_pkt AS b,
     atkis.atkis0809_forestland AS a
     WHERE
     b.the_geom && a.the_geom AND ST_Within(b.the_geom, a.the_geom)
     ;
     COMMIT;