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

Thanks! That's also a good idea.

Heather

----- Original Message -----
From: "Peralta Miguel-MPERALT1" <Miguel.Peralta@motorola.com>
To: "Heather Johnson" <hjohnson@nypost.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@nypost.com]
> Sent: Monday, December 10, 2001 4:57 PM
> To: pgsql-admin@postgresql.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
>
>
>
>