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(dot)reimer(at)opendb(dot)com(dot)br