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;
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
