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.