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