Update table performance problem

From: Mark Makarowsky <bedrockconstruction(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Update table performance problem
Date: 2007-06-13 17:13:50
Message-ID: 886101.35974.qm@web32203.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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/

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2007-06-13 18:06:28 Re: [PG 8.1.0 / AIX 5.3] Vacuum processes freezing
Previous Message Mark Wong 2007-06-13 17:02:41 Re: dbt2 NOTPM numbers