Обсуждение: Update table performance problem

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/

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

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