pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate table performance problem
3+ messages / 2 participants
[nested] [flat]
* Update table performance problem
@ 2007-06-13 17:13 Mark Makarowsky <bedrockconstruction@yahoo.com>
2007-06-13 18:15 ` Re: Update table performance problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Mark Makarowsky @ 2007-06-13 17:13 UTC (permalink / raw)
To: 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
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"
Thanks,
Fred
____________________________________________________________________________________
Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.
http://farechase.yahoo.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Update table performance problem
2007-06-13 17:13 Update table performance problem Mark Makarowsky <bedrockconstruction@yahoo.com>
@ 2007-06-13 18:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-13 19:47 ` Re: Update table performance problem Mark Makarowsky <bedrockconstruction@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-06-13 18:15 UTC (permalink / raw)
To: Mark Makarowsky <bedrockconstruction@yahoo.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Update table performance problem
2007-06-13 17:13 Update table performance problem Mark Makarowsky <bedrockconstruction@yahoo.com>
2007-06-13 18:15 ` Re: Update table performance problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-06-13 19:47 ` Mark Makarowsky <bedrockconstruction@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Mark Makarowsky @ 2007-06-13 19:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
The version is:
"PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC
gcc.exe (GCC) 3.4.2 (mingw-special)"
Here is the table definition for co and fco. There
aren't any rules constraints, triggers, etc. on the
tables. Only an index on each table for the xno
field. Any other thoughts?
CREATE TABLE co
(
xno character(10),
longitude double precision,
latitude double precision,
firest_id character(8),
fire_dist double precision,
polst_id character(8),
pol_dist double precision,
fnew_id character(10),
fnew_dist double precision,
pnew_id character(10),
pnew_dist double precision,
seihazm020 bigint,
acc_val integer,
valley integer,
flood_id bigint,
chance character varying
)
WITHOUT OIDS;
ALTER TABLE co OWNER TO postgres;
-- Index: co_xno
-- DROP INDEX co_xno;
CREATE UNIQUE INDEX co_xno
ON co
USING btree
(xno);
CREATE TABLE fco
(
firest_id character(8),
fire_dist double precision,
xno character(10)
)
WITHOUT OIDS;
ALTER TABLE fco OWNER TO postgres;
-- Index: fco_xno
-- DROP INDEX fco_xno;
CREATE UNIQUE INDEX fco_xno
ON fco
USING btree
(xno);
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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
>
____________________________________________________________________________________
Got a little couch potato?
Check out fun summer activities for kids.
http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-06-13 19:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-13 17:13 Update table performance problem Mark Makarowsky <bedrockconstruction@yahoo.com>
2007-06-13 18:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-13 19:47 ` Mark Makarowsky <bedrockconstruction@yahoo.com>
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