Out of memory error - Mailing list pgsql-general

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/