Message-ID: <42F6BEC0.9090504@comcast.net>
Date: Sun, 07 Aug 2005 19:09:04 -0700
From: Patrick Hatcher <pathat@comcast.net>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Slow update statement
References: <42F4B812.7070501@comcast.net> <17916.1123337563@sss.pgh.pa.us>
In-Reply-To: <17916.1123337563@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 <pathat@comcast.net> 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
>
>  
>