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