On Wed, 19 Jan 2005, Richard Huxton wrote:
Nothing wrong with what you're doing, however, you are running a transaction that touches 142 million rows (expiring the old rows and adding new ones). Still, unless you are particularly short of memory, or haven't tuned PostgreSQL it should be fine.
Some questions: 1. Is the table particularly wide (i.e. number/size of columns)?
Well 28 columns, one varchar, one box, rest 4-byte real/integer.
Nothing terribly dramatic.
2. Do you have any foreign keys/triggers on the table?
No triggers, but there's one R-tree on the box, and one B-tree on an integer column. I guess I should have dropped those and re-created afterwards, but the recreation takes some time, so I hoped to avoid the need to do that.
Hmm - wonder if there might be some memory leak in updates to the R-tree - it gets used less than B-tree, so it could be. If you reply to this, make sure you mention your version of PG - one of the developers might know more. Probably also worth looking in the documentation for 8.0 and check the release notes section to see if any changes look applicable to your problem.
Perhaps a quick search of the -bugs mailing list archives too. -- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
