postmaster growing to consume all memory

Hi,

I recently upgraded to postgresql 7.4 and I am having a problem with
postmaster using lots of memory for a query (keeps growing even up to
400MB+ till I stop postgresql ). I don't recall this ever happening with
7.3 with the exact same query but on different data (just as much data tho,
or even more). Happened on 7.4 so I upgraded to 7.4.1. Is HashAggregate
something new in 7.4? If I have time and disk space I'll downgrade to 7.3.3
and see if it happens there. How do I configure a memory consumption limit
on a 7.4 postgresql without killing it?

I believe postgresql.conf is 7.4.1 default.
md5sum= 75ffabc3e90457bd9d6e4ce649e17b6e postgresql.conf

Problem query:
select ip_saddr,count(*),sum(ip_totlen) from cust_ulog
where true
group by ip_saddr order by sum(ip_totlen) desc limit 10

Results from Explain:
Limit (cost=335158.05..335158.08 rows=10 width=13)
-> Sort (cost=335158.05..335161.65 rows=1440 width=13)
Sort Key: sum(ip_totlen)
-> HashAggregate (cost=335075.31..335082.51 rows=1440 width=13)
-> Seq Scan on cust_ulog (cost=0.00..264115.32
rows=9461332 width=13)
(5 rows)

select count(*) from cust_ulog ;
count
---------
9461332
(1 row)

Time: 51922.612 ms

Table definition:
Table "public.cust_ulog"
Column | Type | Modifiers
--------------+-----------------------------+-----------
id | integer |
oob_time_sec | timestamp without time zone |
oob_prefix | text |
oob_in | text |
oob_out | text |
ip_saddr | inet |
ip_daddr | inet |
ip_totlen | smallint |
ip_ttl | smallint |
ip_id | integer |
ip_protocol | smallint |
ip_tos | smallint |
tcp_sport | integer |
tcp_dport | integer |
tcp_seq | bigint |
tcp_ack_seq | bigint |
tcp_ack | boolean |
tcp_rst | boolean |
tcp_psh | boolean |
tcp_syn | boolean |
tcp_fin | boolean |
tcp_window | integer |
tcp_urgp | integer |
udp_sport | integer |
udp_dport | integer |
udp_len | smallint |
icmp_type | smallint |
icmp_code | smallint |
icmp_echoid | bigint |
icmp_echoseq | bigint |

psql -V
psql (PostgreSQL) 7.4.1
contains support for command-line editing

select version();
version
-------------------------------------------------------------
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

Output from: top
3:13pm up 1:14, 5 users, load average: 0.90, 0.33, 0.36
81 processes: 79 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: 18.5% user, 8.3% system, 0.0% nice, 73.0% idle
Mem: 254572K av, 250124K used, 4448K free, 0K shrd, 288K buff
Swap: 522072K av, 92756K used, 429316K free 21048K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
16084 postgres 18 0 258M 217M 23868 D 24.7 87.6 0:20 postmaster

Output from: vmstat 2
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us
sy id
0 1 0 71720 4388 504 18172 328
154 1629 200 171 123 10 2 87
1 0 0 75560 4352 464 19492 2154 1416
10730 1416 442 401 21 11 67
1 0 2 77992 4444 456 20568 2062
1498 9170 1504 412 373 13 9 78
0 1 0 80552 4372 284 20944 1878 1412
11158 1426 430 485 17 8 75
0 1 0 81704 4456 284 21884 1946
956 9114 956 395 345 13 8 79
1 0 0 83496 4408 292 22420 2096 412
10864 418 423 406 18 8 74
1 0 0 85540 4384 304 21936 1664
1218 9828 1218 400 387 16 9 75
0 1 0 86140 4352 296 22688 1672 522
11016 528 422 370 18 7 75
1 0 0 87696 4376 288 21376 1998 852
11086 856 437 393 19 8 73
0 1 0 89592 4444 288 21900 1686 1132
12118 1132 434 416 21 8 71
1 0 0 92512 4360 296 21396 1792 1262
11456 1268 429 393 18 5 77
0 2 1 94316 4324 292 21640 2172 508
10644 508 407 402 16 11 72
0 1 0 97160 4436 300 23504 1752 1302
12548 1308 427 455 24 9 67
1 0 0 100096 4428 292 23660 1542 1994
10374 1994 409 373 14 8 78
1 0 0 102848 4400 292 23700 2022 1708
10280 1708 406 365 12 9 79
1 0 0 105620 4456 292 25524 2028 1434
12396 1434 431 435 22 8 69
0 1 0 108248 4404 292 26200 2162 1068
12402 1068 437 409 24 8 68
--