From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Makarowsky <bedrockconstruction@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Update table performance problem
Date: Wed, 13 Jun 2007 14:15:20 -0400
Message-ID: <14901.1181758520@sss.pgh.pa.us> (raw)
In-Reply-To: <886101.35974.qm@web32203.mail.mud.yahoo.com>
References: <886101.35974.qm@web32203.mail.mud.yahoo.com>
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
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: tgl@sss.pgh.pa.us, bedrockconstruction@yahoo.com
Subject: Re: Update table performance problem
In-Reply-To: <14901.1181758520@sss.pgh.pa.us>
* 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