problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7

Hello--

I have been having trouble getting subselect queries to complete on a Sun E-450 running Solaris 7 and PostgreSQL 7.1.3. Just about any subselect query I try to run will fail to complete execution---Postgres's serverlog indicates that the process associated with the query gets killed after a few minutes have gone by. I've tried running subselects from an interface to Postgres which uses a socket connection, and I've tried running them directly from the command line, always with the same result. The E-450 has two 400 MHz processors and 1.5 GB of RAM. Here's an example of the kind of query I've tried:

SELECT count(*) FROM users WHERE id NOT IN ( SELECT users_id FROM users_demographics );

What's weird is that I can successfully run the same queries on a Solaris x86 box with a single 233MHz Pentium chip, and 96 MB of RAM, also running PostgreSQL 7.1.3 but with Solaris 8. The database contains about 650,000 records in the users table, and only slightly less than that in the users_demographics table. I dumped the database from the E-450 and re-imported it into the x86 box to be sure that I was working with the same volume of data. The x86 box completes the query in about 10 minutes, but the E-450 can't complete the query at all.

The resources of the E-450 don't seem to be overly taxed by postgres (see results of top over a 15-20 minutes period included below), and yet the comparative performance of the x86 box makes it sound like there is some kind of system problem responsible. Is anyone aware of any problems with doing subselects in PostgreSQL with Solaris 7? Might there be other system-related issues or Postgres-related issues responsible for this? I would appreciate any suggestions you might have!

Thank you!
Heather Johnson

load averages: 0.31, 0.11, 0.10
49 processes: 47 sleeping, 2 on cpu
CPU states: 25.5% idle, 25.2% user, 4.0% kernel, 45.2% iowait, 0.0% swap
Memory: 1536M real, 24M free, 1987M swap in use, 693M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
24283 postgres 1 21 0 14M 6768K cpu/1 0:37 23.01% postgres
24352 postgres 1 0 0 2168K 1432K cpu/3 0:00 0.78% top
4846 nobody 1 60 4 1782M 1006M sleep 380:20 0.21% impress
12047 nobody 1 54 0 148M 145M sleep 0:04 0.08% httpd
239 root 1 58 0 0K 0K sleep 14:54 0.01% sysedge.sol27-s
258 root 6 58 0 14M 3192K sleep 91:41 0.00% mysqld
1 root 1 58 0 752K 152K sleep 9:00 0.00% init
201 root 15 59 0 3376K 808K sleep 8:08 0.00% syslogd
28712 postgres 1 58 0 13M 664K sleep 0:30 0.00% postgres
210 root 1 58 0 1784K 400K sleep 0:21 0.00% cron
192 root 5 58 0 3408K 840K sleep 0:17 0.00% automountd
221 root 1 58 0 1016K 240K sleep 0:08 0.00% utmpd
3220 root 1 58 0 2528K 448K sleep 0:05 0.00% sshd
233 root 1 59 -12 2080K 456K sleep 0:05 0.00% xntpd
225 root 1 58 0 2376K 504K sleep 0:04 0.00% sendmail

load averages: 0.41, 0.23, 0.15
49 processes: 47 sleeping, 2 on cpu
CPU states: 1.7% idle, 49.3% user, 2.0% kernel, 47.1% iowait, 0.0% swap
Memory: 1536M real, 22M free, 1987M swap in use, 693M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
24283 postgres 1 0 0 14M 6728K cpu/1 1:59 32.91% postgres
24358 postgres 1 0 0 2168K 1424K cpu/3 0:02 1.00% top
4846 nobody 1 60 4 1782M 1005M sleep 380:21 0.18% impress
24337 root 1 58 0 2488K 1688K sleep 0:00 0.02% sendmail
258 root 6 58 0 14M 3824K sleep 91:41 0.00% mysqld
239 root 1 58 0 0K 0K sleep 14:54 0.00% sysedge.sol27-s
1 root 1 58 0 752K 144K sleep 9:00 0.00% init
201 root 15 59 0 3376K 832K sleep 8:08 0.00% syslogd
28712 postgres 1 58 0 13M 720K sleep 0:30 0.00% postgres
210 root 1 58 0 1784K 400K sleep 0:21 0.00% cron
192 root 5 58 0 3408K 816K sleep 0:17 0.00% automountd
221 root 1 58 0 1016K 264K sleep 0:08 0.00% utmpd
3220 root 1 58 0 2528K 448K sleep 0:05 0.00% sshd
233 root 1 59 -12 2080K 488K sleep 0:05 0.00% xntpd
12047 nobody 1 58 0 148M 145M sleep 0:04 0.00% httpd

load averages: 0.47, 0.26, 0.16
49 processes: 47 sleeping, 2 on cpu
CPU states: 20.1% idle, 24.1% user, 4.0% kernel, 51.8% iowait, 0.0% swap
Memory: 1536M real, 23M free, 1987M swap in use, 693M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
24283 postgres 1 30 0 14M 6568K cpu/3 2:17 32.27% postgres
24358 postgres 1 0 0 2168K 1424K cpu/1 0:02 0.99% top
4846 nobody 1 60 4 1782M 1004M sleep 380:21 0.20% impress
239 root 1 58 0 0K 0K sleep 14:54 0.01% sysedge.sol27-s
24337 root 1 58 0 2488K 1688K sleep 0:00 0.00% sendmail
258 root 6 58 0 14M 3816K sleep 91:41 0.00% mysqld
1 root 1 58 0 752K 136K sleep 9:00 0.00% init
201 root 15 59 0 3376K 832K sleep 8:08 0.00% syslogd
28712 postgres 1 58 0 13M 696K sleep 0:30 0.00% postgres
210 root 1 58 0 1784K 400K sleep 0:21 0.00% cron
192 root 5 58 0 3408K 840K sleep 0:17 0.00% automountd
221 root 1 58 0 1016K 264K sleep 0:08 0.00% utmpd
3220 root 1 58 0 2528K 448K sleep 0:05 0.00% sshd
233 root 1 59 -12 2080K 480K sleep 0:05 0.00% xntpd
12047 nobody 1 58 0 148M 145M sleep 0:04 0.00% httpd

load averages: 0.79, 0.48, 0.28
56 processes: 52 sleeping, 1 running, 1 zombie, 2 on cpu
CPU states: 18.7% idle, 23.5% user, 17.5% kernel, 40.3% iowait, 0.0% swap
Memory: 1536M real, 24M free, 1990M swap in use, 690M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
24283 postgres 1 20 0 14M 6408K cpu/1 4:24 17.16% postgres
258 root 7 58 0 14M 5472K sleep 91:48 1.35% mysqld
24358 postgres 1 0 0 2168K 1424K cpu/3 0:07 1.04% top
24383 nobody 1 34 0 6664K 4400K sleep 0:01 0.55% press.cgi
24525 nobody 1 0 0 2648K 1912K run 0:00 0.20% ssh
4846 nobody 1 60 4 1782M 1012M sleep 380:22 0.15% impress
24337 root 1 58 0 2512K 1704K sleep 0:00 0.06% sendmail
24386 nobody 1 46 0 1088K 808K sleep 0:00 0.03% push.sh
24524 nobody 1 46 0 1760K 1120K sleep 0:00 0.02% rsync
201 root 15 59 0 3376K 880K sleep 8:08 0.02% syslogd
221 root 1 58 0 1016K 264K sleep 0:08 0.01% utmpd
13305 nobody 1 58 0 148M 145M sleep 0:02 0.01% httpd
239 root 1 58 0 0K 0K sleep 14:54 0.01% sysedge.sol27-s
12048 nobody 1 58 0 148M 145M sleep 0:04 0.01% httpd
24385 nobody 1 24 0 1040K 744K sleep 0:00 0.01% reload.sh

load averages: 0.55, 0.60, 0.45
49 processes: 47 sleeping, 2 on cpu
CPU states: 15.4% idle, 24.4% user, 4.6% kernel, 55.6% iowait, 0.0% swap
Memory: 1536M real, 23M free, 1987M swap in use, 693M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
24283 postgres 1 10 0 14M 6136K cpu/1 8:45 17.21% postgres
24358 postgres 1 0 0 2168K 1424K cpu/3 0:19 0.96% top
4846 nobody 1 60 4 1782M 1011M sleep 380:24 0.21% impress
239 root 1 58 0 0K 0K sleep 14:54 0.02% sysedge.sol27-s
24271 root 1 58 0 2560K 872K sleep 0:00 0.01% sshd
258 root 6 58 0 14M 5352K sleep 91:48 0.00% mysqld
1 root 1 58 0 752K 152K sleep 9:00 0.00% init
201 root 15 59 0 3376K 880K sleep 8:08 0.00% syslogd
28712 postgres 1 58 0 13M 720K sleep 0:30 0.00% postgres
210 root 1 58 0 1784K 400K sleep 0:21 0.00% cron
192 root 5 58 0 3408K 840K sleep 0:17 0.00% automountd
221 root 1 58 0 1016K 264K sleep 0:08 0.00% utmpd
3220 root 1 58 0 2528K 440K sleep 0:05 0.00% sshd
233 root 1 59 -12 2080K 472K sleep 0:05 0.00% xntpd
12053 nobody 1 58 0 148M 145M sleep 0:04 0.00% httpd