From: Rosser Schwarz <rschwarz@totalcardinc.com>
To: pgsql-performance@postgresql.org
Subject: Re: atrocious update performance
Date: Mon, 5 Apr 2004 12:05:37 -0500
Message-ID: <009801c41b30$37872380$2500fa0a@CardServices.TCI.com> (raw)
In-Reply-To: <004301c411be$74004650$2500fa0a@CardServices.TCI.com>
After deinstalling and scrubbing PostgreSQL from my server and doing
a clean build using a vanilla 7.4.2 tree, I'm rather more confident
that foreign key validation is at cause in my performance problems.
I recreated my schemas and ran the original update, with foreign
keys referring to the identity column of the target table. The
update took roughly two days, as I'd predicted based on my analysis
of the previous installation. (I can't say how long with certainty,
beyond that it finished some time between when I left work one night
and came in the next morning, the second day after starting the
query.) I'm not sure what was wrong with the previous install, such
that the update took several days; two-ish days is long enough.
Just this morning, however, I created a copy of the target table (all
4.7M rows), with absolutely no foreign keys referring to it, and ran
the update against the copy. That update took 2300 seconds. The
join columns were indexed in both cases.
I'm in the process of migrating the machine to run kernel 2.6.4,
following the thread started by Gary, though I suspect that the
kernel revision is moot with respect to whether or not foreign keys
are being incorrectly validated. I can keep the 2.4 kernel and
modules around to run using the current versions for testing
purposes, though any such work would necessarily be off-hours.
Please advise of anything I can do to help narrow down the specific
cause of the issue; I know just enough C to be mildly dangerous.
/rls
--
Rosser Schwarz
Total Card, Inc.
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: rschwarz@totalcardinc.com
Subject: Re: atrocious update performance
In-Reply-To: <009801c41b30$37872380$2500fa0a@CardServices.TCI.com>
* 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