Re: tuning SQL

Anna,
    I'm not sure that this query is doing what you think it's doing.
    Since your tables aren't linked you'll end up with count(contact) *
count(contact_discard) rows to evaluate, or
9,000,000*259,00=2,331,000,000,000 (2.3 trillion) rows.

    Assuming that you want to find the number of records in contact without a
corrisponding record in contact_diacard, you'll probably want something
like:

select count(*) from contact where contacthandle not in (select
contacthandle from contact_discard);

    This will also be painfully slow (but way faster than your first query),
since it's using in, but I'm sure that someone can suggest something faster.

Thanks,
Peter Darley


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Zhang, Anna
Sent: Tuesday, January 29, 2002 7:57 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] tuning SQL


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;
Table contact has over 9 million rows, contact_diacard has around 259,000
rows, both tables define contacthandle as primary key.
Here is the query execution plan:
Aggregate (cost=732021.97..732021.97 rows=1 width=24)
 -> Hash Join (cost=10035.10..731397.95 rows=249608 width=24)
      -> Seq Scan on contact a (cost=0.00..345002.95 rows=9330995 width=12)
      -> Hash (cost=9411.08..9411.08 rows=249608 width=12)
         -> Seq Scan on contact_disacrd b (cost=0.00..9411.08 rows=249608
width=12)
 I started to run this query at 5:00pm yesterday, it still running!!! My
question is Why query plan doesn't use index scan for join, Can we force it
to use index? Or any idea to improve the performance? We have more tables
bigger than contact, and need to join them among? Am I pushing the postgres
to the limit? Help!!!
Shared_buffer = 65536
sort_mem = 32768

Anna Zhang


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org