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

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 (view raw or flat)
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

pgsql-performance by date

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

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