Roberto Cornacchia wrote:
2. The restore is almost unusable for large databases, as it is extremely slow. I suspect the main reasons being: - COPY INTO .. FROM STDIN (used in the dump) is much much slower than copying from a file, regardless of whether the number or record to copy is given or not.
This is my conclusion as well.
- Column constraints, as primary key, not null, etc, are issued upon table creation, before the COPY INTO, rather than being postponed with an ALTER TABLE after the copy (FOREIGN KEYS are instead postponed to the end of the script).
I have interesting results; it seems to be actually ~5% faster to *insert* with a primary key, opposed to postpone it (!) This even excludes the time to enforce the primary key afterwards. I have verified in different runs that it is not related to the 'NOT NULL' constraints. So this is a pretty surprising results. The data was highly structural and had a multicolumn primary key. Stefan