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@rice.edu
Executive Director                                  phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
Rice University MS-39
Houston, TX 77005