Running out of memory while making a join

Hi,

The following SQL join command runs the PostgreSQL server out of memory. The server runs on a box with Red Hat Enterprise Linux Server release 6.3 (Santiago) and PostgreSQL 8.3.21.

select wm_nfsp from "5611_isarq".wm_nfsp
left join "5611_nfarq".nfe on
wm_nfsp.tpdoc = 7 where 1 = 1 and
wm_nfsp.codpre = 2866 and
wm_nfsp.compet = '10/2012';  

Explain result:
 Nested Loop Left Join  (cost=7356.61..48466.46 rows=346312 width=32)
   Join Filter: (wm_nfsp.tpdoc = 7)
   ->  Bitmap Heap Scan on wm_nfsp  (cost=11.65..1162.37 rows=11 width=34)
         Recheck Cond: (codpre = 2866)
         Filter: ((compet)::text = '10/2012'::text)
         ->  Bitmap Index Scan on idx_wm_nfsp_codpre  (cost=0.00..11.64 rows=714 width=0)
               Index Cond: (codpre = 2866)
   ->  Materialize  (cost=7344.96..8959.47 rows=161451 width=0)
         ->  Seq Scan on nfe  (cost=0.00..7183.51 rows=161451 width=0)
(9 rows)

Once the query starts the top command starts showing an increase of memory use and minutes later vmstat shows the server performing a lot swapping and almost stops everything until the PID is killed.

top command output while join running on PID 29787:
top - 11:26:41 up 10 days,  6:30,  3 users,  load average: 131.05, 74.55, 32.33
Tasks: 499 total,   3 running, 496 sleeping,   0 stopped,   0 zombie
Cpu(s): 10.8%us, 12.7%sy,  0.0%ni,  0.0%id, 75.2%wa,  0.0%hi,  1.3%si,  0.0%st
Mem:  32876756k total, 32677176k used,   199580k free,     4804k buffers
Swap: 16777208k total,  6248980k used, 10528228k free,   485188k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
29787 postgres  20   0 35.5g  29g  98m D 30.8 92.9  15:07.23 postgres: ipm Fisca
   98 root      20   0     0    0    0 D 13.7  0.0  23:46.72 [kswapd0]
31496 postgres  20   0  546m  20m  18m D  4.9  0.1   0:00.29 postgres: ipm Fisca
29024 postgres  20   0  547m  52m  50m D  4.0  0.2   0:03.95 postgres: ipm Fisca

vmstat output showing the server is performing a lot of swapping:
Tue Nov  6 11:27:06 BRST 2012
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1 143 6653476 199076   6368 476356    0    1   694   646    2    0  8  3 88  1  0
 1 79 6700576 204104   6212 453808   64 9438 99833  9503 14213 9477 10  9  0 80  0
 5 74 6813252 199144   5196 488872    7 22540 102592 22704 13770 8762  9 10  2 80  0
 2 58 6855596 199332   4456 462592   70 8474 133870  8509 13527 9242  8 10  0 82  0
 3 90 6907264 199096   5544 472112  102 10403 102617 11136 12764 8497  7  9  0 84  0

Running the same command on a PostgreSQL 9.0.0 server results in an OUT OF MEMORY message stopping the backend but preventing the entire server to stop.

The development team is going to change the SQL join command but my concern is with other similar SQL commands not yet identified.

Is there a way to make PostgreSQL 8.3.21 server stop memory bound backends as PostgreSQL 9.0.0 does?

--
Reimer
47-3347-1724 47-9183-0547 msn: carlos.reimer@opendb.com.br