To: Mark Makarowsky <bedrockconstruction@yahoo.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Update table performance problem 
In-reply-to: <886101.35974.qm@web32203.mail.mud.yahoo.com> 
References: <886101.35974.qm@web32203.mail.mud.yahoo.com>
Comments: In-reply-to Mark Makarowsky <bedrockconstruction@yahoo.com>
	message dated "Wed, 13 Jun 2007 10:13:50 -0700"
Date: Wed, 13 Jun 2007 14:15:20 -0400
Message-ID: <14901.1181758520@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Mark Makarowsky <bedrockconstruction@yahoo.com> writes:
> "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"

According to the top line, the actual scanning and joining took 43 sec;
so the rest of the time went somewhere else.  Possibilities include
the actual data insertion (wouldn't take 5 minutes), index updates
(what indexes are on this table?), constraint checks, triggers, ...

You failed to mention which PG version this is.  8.1 and up would show
time spent in triggers separately, so we could eliminate that
possibility if it's 8.1 or 8.2.  My suspicion without any data is
a lot of indexes on the table.

			regards, tom lane