Re: Update table performance problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mark Makarowsky <bedrockconstruction(at)yahoo(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Update table performance problem
Date: 2007-06-13 18:15:20
Message-ID: 14901.1181758520@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Mark Makarowsky <bedrockconstruction(at)yahoo(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Vivek Khera 2007-06-13 19:13:19 Re: Performance Testing Utility
Previous Message Tom Lane 2007-06-13 18:06:28 Re: [PG 8.1.0 / AIX 5.3] Vacuum processes freezing