postmaster growing to consume all memory - Mailing list pgsql-general

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
--