From: Mark Makarowsky <bedrockconstruction@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Update table performance problem
Date: Wed, 13 Jun 2007 12:47:36 -0700 (PDT)
Message-ID: <602932.76858.qm@web32201.mail.mud.yahoo.com> (raw)
In-Reply-To: <14901.1181758520@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bedrockconstruction@yahoo.com, tgl@sss.pgh.pa.us
Subject: Re: Update table performance problem
In-Reply-To: <602932.76858.qm@web32201.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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