Out of memory error

We have a query that's producing an "out of memory" error
consistently. The detail of the error message is "Failed on request of
size 16." We have 16 GB of RAM in our database server running 32-bit
Debian lenny. Here's the query:

INSERT INTO db_newsitemlocation (news_item_id, location_id)
SELECT ni.id, loc.id
FROM db_newsitem ni, db_location loc
WHERE NOT EXISTS
(SELECT 1
FROM db_newsitemlocation
WHERE news_item_id=ni.id AND location_id=loc.id) AND
intersects(loc.location, ni.location) AND
ni.pub_date > '2009-6-25' AND
ni.pub_date < '2009-7-3';

db_newsitem and db_newsitemlocation are both on the order of a few
million rows. db_location has a few hundred.

Details about the server:

| uname -a | Linux lincoln 2.6.26-2-686-bigmem #1 SMP Sun Jun 21
05:40:38 UTC 2009 i686 GNU/Linux |
| psql -V | psql (PostgreSQL) 8.3.7
|
| lsb_release -d | Description: Debian GNU/Linux 5.0.1 (lenny)
|
| free -m | 16244 (mem) 7632 (swap)
|

Relevant postgres.conf:

max_connections = 300
shared_buffers = 2560MB
work_mem = 2MB
maintenance_work_mem = 128MB
effective_cache_size = 8122MB

Here's the EXPLAIN on the main SELECT part of the statement. EXPLAIN
ANALYZE is impossible because of the "out of memory" error.

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..3448789.73 rows=65642 width=8)
Join Filter: (intersects(loc.location, ni.location) AND (NOT (subplan)))
-> Index Scan using db_newsitem_pub_date on db_newsitem ni
(cost=0.00..5479.14 rows=1815 width=104)
Index Cond: ((pub_date > '2009-06-25 00:00:00-07'::timestamp
with time zone) AND (pub_date < '2009-07-03 00:00:00-07'::timestamp
with time zone))
-> Seq Scan on db_location loc (cost=0.00..17.17 rows=217 width=47281)
SubPlan
-> Index Scan using db_newsitemlocation_news_item_id_key on
db_newsitemlocation (cost=0.00..8.65 rows=1 width=0)
Index Cond: ((news_item_id = $0) AND (location_id = $1))
(8 rows)

And here's the error from stderr:

TopMemoryContext: 82576 total in 9 blocks; 9016 free (10 chunks); 73560 used
CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
Operator lookup cache: 24576 total in 2 blocks; 14072 free (6
chunks); 10504 used
Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344 used
TopTransactionContext: 8192 total in 1 blocks; 7856 free (1 chunks); 336 used
MessageContext: 65536 total in 4 blocks; 13768 free (4 chunks); 51768 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: 460390096 total in 431 blocks; 8400 free (2
chunks); 460381696 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 240972 total in 2 blocks; 7984 free (3 chunks); 232988 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used
CacheMemoryContext: 667472 total in 20 blocks; 236496 free (1
chunks); 430976 used
pg_toast_157541_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
db_location_slug: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
db_location_normalized_name: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
db_location_location_type_id: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
db_location_slug_key: 1024 total in 1 blocks; 280 free (0 chunks); 744 used
db_location_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
db_newsitem_schema_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
db_newsitem_pub_date: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
db_newsitem_location_object_id: 1024 total in 1 blocks; 344 free
(0 chunks); 680 used
db_newsitem_location: 1024 total in 1 blocks; 136 free (0 chunks); 888 used
db_newsitem_item_date: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
db_newsitem_block_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
db_newsitem_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
db_newsitemlocation_news_item_id: 1024 total in 1 blocks; 344 free
(0 chunks); 680 used
db_newsitemlocation_location_id: 1024 total in 1 blocks; 344 free
(0 chunks); 680 used
db_newsitemlocation_pkey: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
db_newsitemlocation_news_item_id_key: 1024 total in 1 blocks; 240
free (0 chunks); 784 used
pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 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; 280 free (0
chunks); 744 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; 280 free
(0 chunks); 744 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; 280 free (0
chunks); 744 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; 7696 free (0 chunks); 496 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 (3 chunks); 16 used
2009-07-06 09:15:52 PDT ERROR: out of memory
2009-07-06 09:15:52 PDT DETAIL: Failed on request of size 16.
2009-07-06 09:15:52 PDT STATEMENT: INSERT INTO db_newsitemlocation
(news_item_id, location_id)
SELECT ni.id, loc.id FROM db_newsitem ni, db_location loc
WHERE NOT EXISTS (SELECT 1 FROM db_newsitemlocation WHERE
news_item_id=ni.id AND location_id=loc.id)
AND intersects(loc.location, ni.location)
AND ni.pub_date > '2009-6-25' AND ni.pub_date < '2009-7-3';

We're aware that the OS cannot fully take advantage of the available
RAM for processes like PostgreSQL but we aren't able to take the
server down currently to re-install a 64-bit OS.

--
Paul Smith
http://www.pauladamsmith.com/