Postgres Pro
Downloads
Home   >   mailing lists

problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7 - Mailing list pgsql-admin

From Heather Johnson
Subject problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7
Date
Msg-id 01d701c181c5$910e2bd0$510b10ac@sephie
Whole thread Raw
In response to How to get database schema without pg_dump?  ("Boban Acimovic" <acim@mbl.is>)
Responses Re: problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7
Re: problems doing sub-selects on PostgreSQL 7.1.3 and
List pgsql-admin
Tree view
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
 
 
 

pgsql-admin by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: table name as parameter in pl/psql
Next
From: "Heather Johnson"
Date:
Subject: FOLLOW UP: problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7