Re: tuning SQL

Thanks Peter Darley, Ross J. Reedstrom and Tom lane!!
How silly am I! Your messages reminds me. Actually I want to insert rows of
contact_discard table which are not exists in contact table to contact table
(some duplicates in two tables), first I run

insert into contact
select * from contact_discard a
where not exists ( select 1 from contact b where b.contacthandle =
a.contacthandle);

It seems takes forever, I killed it after hours(note: contact table has 4
indexes). Then I tried to figure out how many rows that are not duplicated.
Now my problem turns to insert performance, in oracle it takes only a few
minues.

Thanks!

Anna Zhang


-----Original Message-----
From: Ross J. Reedstrom [mailto:reedstrm@rice.edu]
Sent: Tuesday, January 29, 2002 11:39 AM
To: Zhang, Anna
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 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