To: Patrick Hatcher <pathat@comcast.net>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Slow update statement 
In-reply-to: <42F6BEC0.9090504@comcast.net> 
References: <42F4B812.7070501@comcast.net> <17916.1123337563@sss.pgh.pa.us>
	<42F6BEC0.9090504@comcast.net>
Comments: In-reply-to Patrick Hatcher <pathat@comcast.net>
	message dated "Sun, 07 Aug 2005 19:09:04 -0700"
Date: Mon, 08 Aug 2005 11:18:53 -0400
Message-ID: <28597.1123514333@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Patrick Hatcher <pathat@comcast.net> writes:
> 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.:

The duplicate index is certainly a waste, but it's no more expensive to
maintain than any other index would be; it doesn't seem likely that that
would account for any huge slowdown.

A long-shot theory occurs to me upon noticing that your join keys are
int8: 7.4 had a pretty bad hash function for int8, to wit it took the
low order half of the integer and ignored the high order half.  For
ordinary distributions of key values this made no difference, but I
recall seeing at least one real-world case where the information was
all in the high half of the key, and so the hash join degenerated to a
sequential search because all the entries went into the same hash
bucket.  Were you assigning cus_nbrs nonsequentially by any chance?

			regards, tom lane