Re: Update table performance problem

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>

____________________________________________________________________________________
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