Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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
"        ->  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"



Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.


pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group