Re: problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7

Thanks! That's also a good idea.

Heather

----- Original Message -----
From: "Peralta Miguel-MPERALT1" <Miguel(dot)Peralta(at)motorola(dot)com>
To: "Heather Johnson" <hjohnson(at)nypost(dot)com>
Sent: Monday, December 10, 2001 5:43 PM
Subject: RE: [ADMIN] problems doing sub-selects on PostgreSQL 7.1.3 and
Solaris 7

> I would try creating a view of the second select (SELECT users_id FROM
> users_demographics) and then issuing the first select on this view. This
> should provide you with what you want.
>
> The answer to the mystery of why the query fails on the Sun might not be
> worth the cost of the investigation effort for this simple query.
>
> -----Original Message-----
> From: Heather Johnson [mailto:hjohnson(at)nypost(dot)com]
> Sent: Monday, December 10, 2001 4:57 PM
> To: pgsql-admin(at)postgresql(dot)org
> Cc: Harry Ford; Serge Canizares; Marie Musacchio
> Subject: [ADMIN] 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
>
>
>
>