Sorry went out of town for the weekend. The update did occur, but I have no idea when it finished.

Here's the actual query and the explain Update:
cdm.bcp_ddw_ck_cus = 12.7 M
cdm.cdm_ddw_customer = 12.8M

explain
update cdm.cdm_ddw_customer
                                      set indiv_fkey = b.indiv_fkey
                                      from cdm.bcp_ddw_ck_cus b
where cdm.cdm_ddw_customer.cus_nbr = b.cus_num;


Here's the table layout. It's the first time I noticed this, but there is a PK on the cus_nbr and an index. Does really need to be both and could this be causing the issue? I thought that if a primary key was designated, it was automatically indexed.:

CREATE TABLE cdm.cdm_ddw_customer
(
 cus_nbr int8 NOT NULL,
 ph_home int8,
 ph_day int8,
 email_adr varchar(255),
 name_prefix varchar(5),
 name_first varchar(20),
 name_middle varchar(20),
 name_last varchar(30),
 name_suffix varchar(5),
 addr1 varchar(40),
 addr2 varchar(40),
 addr3 varchar(40),
 city varchar(25),
 state varchar(7),
 zip varchar(10),
 country varchar(16),
 gender varchar(1),
 lst_dte date,
 add_dte date,
 reg_id int4,
 indiv_fkey int8,
 CONSTRAINT ddwcus_pk PRIMARY KEY (cus_nbr)
)
WITH OIDS;

CREATE INDEX cdm_ddwcust_id_idx
 ON cdm.cdm_ddw_customer
 USING btree
 (cus_nbr);


CREATE TABLE cdm.bcp_ddw_ck_cus
(
 cus_num int8,
 indiv_fkey int8 NOT NULL
)
WITHOUT OIDS;

Tom Lane wrote:

Patrick Hatcher <[EMAIL PROTECTED]> writes:
I'm running an update statement on about 12 million records using the
following query:

Update table_A
set F1 = b.new_data
from table_B b
where b.keyfield = table_A.keyfield

What does EXPLAIN show for this?

Do you have any foreign key references to table_A from elsewhere?

                        regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org

Reply via email to