From: Mark Makarowsky <bedrockconstruction@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Update table performance problem
Date: Wed, 13 Jun 2007 10:13:50 -0700 (PDT)
Message-ID: <886101.35974.qm@web32203.mail.mud.yahoo.com> (raw)
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bedrockconstruction@yahoo.com
Subject: Re: Update table performance problem
In-Reply-To: <886101.35974.qm@web32203.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox