DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=p3GBiPdp37TbVzd5ZLLC2e/Ja9Xtps05ULudd+J29nBPokQUm/CeOpfdx4kM9E0XPROzjg7UX6+cx/9rkRrURm6oVzGY1lASICr6MJ54vyh90RqXZgnTHK62daaJnoaQUD+aD7d7xKssUIbwM9Tq3rjlpk7Y4xyj5FEsBFUw3V4=;
Date: Wed, 13 Jun 2007 10:13:50 -0700 (PDT)
From: Mark Makarowsky <bedrockconstruction@yahoo.com>
Subject: Update table performance problem
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <886101.35974.qm@web32203.mail.mud.yahoo.com>

I am trying to update a field in one table with a
field from another table like:

update co set
firest_id=fco.firest_id,fire_dist=fco.fire_dist from
fco where co.xno=fco.xno

Table co has 384964 records
Table fco has 383654 records

The xno fields in both tables are indexed but they
don't seem to be used.  I would expect the update to
be faster than 6.3 minutes or is that expectation
wrong?  Here is the results of Explain Analyze:

"Hash Join  (cost=15590.22..172167.03 rows=383654
width=215) (actual time=1473.297..43032.178
rows=383654 loops=1)"
"  Hash Cond: (co.xno = fco.xno)"
"  ->  Seq Scan on co  (cost=0.00..123712.64
rows=384964 width=195) (actual time=440.196..37366.682
rows=384964 loops=1)"
"  ->  Hash  (cost=7422.54..7422.54 rows=383654
width=34) (actual time=995.651..995.651 rows=383654
loops=1)"
"        ->  Seq Scan on fco  (cost=0.00..7422.54
rows=383654 width=34) (actual time=4.641..509.947
rows=383654 loops=1)"
"Total runtime: 378258.707 ms"

Thanks, 

Fred


____________________________________________________________________________________
Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.
http://farechase.yahoo.com/