Re: tuning SQL

On Tue, Jan 29, 2002 at 10:57:01AM -0500, Zhang, Anna wrote:
> Hi,
> I am running a query on postgres 7.1.3 at Red Hat 7.2 (2 CPUs, 1.5G RAM, 2
> drive disk array).
> select count(*) from contact a, contact_discard b where a.contacthandle <>
> b.contacthandle;

What are you trying to do with this query? As written, it'll give you
a (roughly) cartesian product between the two tables. Here's a (small) example
from one of my dbs:

bioinfo=# select count(*) from people;
count
-------
91
(1 row)

bioinfo=# select count(*) from new_people;
count
-------
70
(1 row)

bioinfo=# select count(*) from people p, new_people n where p.peid=n.peid;
count
-------
69
(1 row)

bioinfo=# select count(*) from people p, new_people n where p.peid <> n.peid;
count
-------
6301
(1 row)

if what you want is the number of contacts not in contact_discard, that'd
be something like:

bioinfo=# select count(*) from people p where not exists (select peid from new_people where peid=p.peid);
count
-------
22
(1 row)

Ross

--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005