Cleanup query takes along time

Hi,

Can someone provide some insight for me as to why this query takes so
long on 6655 rows of data. I'm beginning to think something is seriously
wrong with my config wrt to memory settings. I note vmstats (at the
bottom) indicates no swapping so I'm not running out system wide but I
don't know whether I am within postgres.

sql> explain ANALYZE SELECT MIN(ref), h_message_id, COUNT(h_message_id)
FROM mail_969 GROUP BY h_message_id HAVING COUNT(h_message_id) > 25;

Aggregate (cost=85031.01..87894.10 rows=28631 width=44) (actual
time=185449.57..185449.57 rows=0 loops=1)
-> Group (cost=85031.01..85746.78 rows=286309 width=44) (actual
time=185374.92..185413.32 rows=6655 loops=1)
-> Sort (cost=85031.01..85031.01 rows=286309 width=44)
(actual time=185374.91..185379.23 rows=6655 loops=1)
-> Seq Scan on mail_969 (cost=0.00..59081.09
rows=286309 width=44) (actual time=179.65..185228.19 rows=6655 loops=1)
Total runtime: 185451.08 msec

To put this into perspective, we see similar results on a table with
over 300,000 rows :

sql> explain ANALYZE SELECT MIN(ref), h_message_id, COUNT(h_message_id)
FROM mail_650 GROUP BY h_message_id HAVING COUNT(h_message_id) > 25;
NOTICE: QUERY PLAN:

Aggregate (cost=88503.85..91596.83 rows=30930 width=48) (actual
time=142483.52..149102.57 rows=244 loops=1)
-> Group (cost=88503.85..89277.10 rows=309298 width=48) (actual
time=142444.19..148477.99 rows=309245 loops=1)
-> Sort (cost=88503.85..88503.85 rows=309298 width=48)
(actual time=142444.17..142652.83 rows=309245 loops=1)
-> Seq Scan on mail_650 (cost=0.00..60297.98
rows=309298 width=48) (actual time=445.75..105818.97 rows=309245 loops=1)
Total runtime: 149181.30 msec

These selects are part of a cleanup operation on a 70GB DB (normal
conditions are around 25GB). They find dupes, preserve one primary key,
and delete the rest. Currently I have an issue that the DB requires a
full vacuum from prior runs of the above however this is another
problem, detailed on the 'admin' list.

Perhaps this is due to the dataset still being so big (70GB) on
effectively one disk, but I just thought I'd check with you guys.

Hardware:

1GB Ram, SMP 1GHz P3, SvrWks OSB4 chipset, Adaptec aic7899 with 2
SCSI-160 disks split between DB and pg_xlog. (I know disks should be
better laid out for a busy db, but this hasn't been my decision :)

Config:

max_fsm_relations = 1000
max_fsm_pages = 20000
vacuum_mem = 65536
effective_cache_size = 95694
random_page_cost = 2
sort_mem=65536
max_connections = 128
shared_buffers = 15732
wal_buffers = 64 # need to determin
wal_files = 64 # range 0-64
wal_sync_method = fsync # the default varies across platforms:
wal_debug = 0 # range 0-16

# hopefully this should see less LogFlushes per LogInsert - use more WAL
though.
commit_delay = 10000 # range 0-100000
commit_siblings = 2 # range 1-1000

checkpoint_segments = 32 # in logfile segments (16MB each), min 1
checkpoint_timeout = 600 # in seconds, range 30-3600
fsync = false
#fsync = true

vmstats whilst running (indicating no swaping) :

procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us
sy id
1 2 1 45592 10868 11028 853700 0 0 7 3 1 0 5
5 7
2 2 0 45592 10288 11236 849312 8 0 732 888 1980 3516 63
12 26
8 2 0 45592 11208 11304 849224 0 0 4438 286 2696 3758 66
15 19
10 2 3 45592 10284 11332 848872 0 0 6344 664 2888 3614 71
18 11
2 8 1 45592 10408 11388 845140 0 0 4622 402 2216 2306 70
11 19
3 7 2 45592 10416 11440 845972 0 0 3538 68 2052 2079 66
9 25
10 5 1 45592 10916 11496 846676 0 0 4428 444 2968 4385 75
17 8
2 4 0 45592 10380 11592 848348 0 0 5940 184 2609 3421 69
15 16

Cheers,

--

Rob Fielding
rob(at)dsvr(dot)net

www.dsvr.co.uk Development Designer Servers Ltd