Обсуждение: slow update

Pg 7.4.5
RH 7.3
Quad Xeon 3Gz
12G ram

Trying to do a update of fields on 23M row database.
Is it normal for this process to take 16hrs and still clocking?  Both join
fields are indexed and I have removed any indexes on the updated columns.
Also both tables are vacuumed regularly.
I'm weary to cancel the job for fear that it is just slow and I'll have to
repeat the 16hr job.
Any suggestions of what I can check for the bottleneck?

Below is my update statement and table structure:

update cdm.cdm_ddw_tran_item
set dept_id = dept,
vend_id = vend,
mkstyl = mstyle
from flbasics
where flbasics.upc = cdm.cdm_ddw_tran_item.item_upc;


CREATE TABLE cdm.cdm_ddw_tran_item
(
  appl_xref varchar(22),
  intr_xref varchar(13),
  tran_typ_id char(1),
  tran_ship_amt numeric(8,2),
  fill_store_div int4,
  soldto_cust_id int8,
  soldto_cust_seq int4,
  shipto_cust_id int8,
  shipto_cust_seq int4,
  itm_qty int4,
  itm_price numeric(8,2),
  item_id int8,
  item_upc int8,
  item_pid varchar(20),
  item_desc varchar(30),
  nrf_color_name varchar(10),
  nrf_size_name varchar(10),
  dept_id int4,
  vend_id int4,
  mkstyl int4,
  ddw_tran_key bigserial NOT NULL,
  price_type_id int2 DEFAULT 999,
  last_update date DEFAULT ('now'::text)::date,
  CONSTRAINT ddw_tritm_pk PRIMARY KEY (ddw_tran_key)
)
WITHOUT OIDS;

CREATE TABLE flbasics
(
  upc int8,
  dept int4,
  vend int4,
  mstyle int4,
  xcolor int4,
  size int4,
  owned float8,
  cost float8,
  xclass int2,
  firstticket float8,
  status char(2),
  last_receipt date,
  description varchar(50),
  pack_qty int2,
  discontinue_date date,
  std_rcv_units int4,
  std_rcv_cost float8,
  std_rcv_retail float8,
  first_receipt date,
  last_pchange varchar(9),
  ticket float8,
  std_mkd_units int4,
  std_mkd_dollars float8
)
WITHOUT OIDS;

Patrick Hatcher
Development Manager  Analytics/MIO
Macys.com

Patrick Hatcher <PHatcher@macys.com> writes:
> Pg 7.4.5

> Trying to do a update of fields on 23M row database.
> Is it normal for this process to take 16hrs and still clocking?

Are there foreign keys pointing at the table being updated?  If so,
failure to index the referencing columns could create this sort of
performance problem.  Also, in 7.4 you'd better be sure the referencing
columns are the same datatype as the referenced column.

            regards, tom lane

Thanks.  No foreign keys and I've been bitten by the mismatch datatypes and
checked that before sending out the message :)

Patrick Hatcher
Development Manager  Analytics/MIO
Macys.com





             Tom Lane
             <tgl@sss.pgh.pa.u
             s>                                                         To
                                       Patrick Hatcher
             10/13/2005 11:34          <PHatcher@macys.com>
             AM                                                         cc
                                       postgres performance list
                                       <pgsql-performance@postgresql.org>
                                                                   Subject
                                       Re: [PERFORM] slow update










Patrick Hatcher <PHatcher@macys.com> writes:
> Pg 7.4.5

> Trying to do a update of fields on 23M row database.
> Is it normal for this process to take 16hrs and still clocking?

Are there foreign keys pointing at the table being updated?  If so,
failure to index the referencing columns could create this sort of
performance problem.  Also, in 7.4 you'd better be sure the referencing
columns are the same datatype as the referenced column.

                                     regards, tom lane