From: Patrick Hatcher <pathat@comcast.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Slow update statement
Date: Sun, 07 Aug 2005 19:09:04 -0700
Message-ID: <42F6BEC0.9090504@comcast.net> (raw)
In-Reply-To: <17916.1123337563@sss.pgh.pa.us>
References: <42F4B812.7070501@comcast.net>
<17916.1123337563@sss.pgh.pa.us>
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
>
>
>
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: pathat@comcast.net, tgl@sss.pgh.pa.us
Subject: Re: Slow update statement
In-Reply-To: <42F6BEC0.9090504@comcast.net>
* 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