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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Zhang, Anna Sent: Tuesday, January 29, 2002 7:57 AM To: [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])