On Mon, Apr 28, 2008 at 06:53:09PM +0100, Heikki Linnakangas wrote: > John Rouillard wrote: > >We are running postgresql-8.1.3 under Centos 4 > You should upgrade, at least to the latest minor release of the 8.1 > series (8.1.11), as there has been a bunch of important bug and security > fixes. Or even better, upgrade to 8.3, which has reduced the storage > size of especially variable length datatypes like text/char/varchar in > particular. As your COPY is I/O bound, reducing storage size will > translate directly to improved performance.
Yup. Just saw that suggestion in an unrelated email. > >dm-6 is where the data files reside and dm-4 is where the WAL archives > >are kept. Note all the DM's are on the same RAID 0 device /dev/sda2. > > Another reason to upgrade to 8.3: if you CREATE or TRUNCATE the table in > the same transaction as you COPY into it, you can avoid WAL logging of > the loaded data, which will in the best case double your performance as > your WAL is on the same physical drives as the data files. We can't do this as we are backfilling a couple of months of data into tables with existing data. > >The only indexes we have to drop are the ones on the primary keys > >(there is one non-primary key index in the database as well). > > > >Can you drop an index on the primary key for a table and add it back > >later? Am I correct in saying: the primary key index is what enforces > >the unique constraint in the table? If the index is dropped and > >non-unique primary key data has been added, what happens when you > >re-add the index? > > Yes, the index is what enforces the uniqueness. You can drop the primary > key constraint, and add it back after the load with ALTER TABLE. If the > load introduces any non-unique primary keys, adding the primary key > constraint will give you an error and fail. That's the part I am worried about. I guess using psql to delete the problem row then re-adding the index will work. > Dropping and recreating the indexes is certainly worth trying. Thanks for the info. -- -- rouilj John Rouillard System Administrator Renesys Corporation 603-244-9084 (cell) 603-643-9300 x 111 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance