restore time: sort_mem vs. checkpoing_segments

And the winner is... checkpoint_segments.

Restore of a significanly big database (~19.8GB restored) shows nearly
no time difference depending on sort_mem when checkpoint_segments is
large. There are quite a number of tables and indexes. The restore
was done from a pg_dump -Fc dump of one database.

All tests with 16KB page size, 30k shared buffers, sort_mem=8192, PG
7.4b2 on FreeBSD 4.8.

3 checkpoint_segments restore time: 14983 seconds
50 checkpoint_segments restore time: 11537 seconds
50 checkpoint_segments, sort_mem 131702 restore time: 11262 seconds

There's an initdb between each test.

For reference, the restore with 8k page size, 60k buffers, 8192
sort_mem and 3 checkpoint buffers was 14777 seconds.

It seems for restore that a larger number of checkpoint buffers is the
key, especially when dealing with large numbers of rows in a table.

I notice during the restore that the disk throughput triples during
the checkpoint.

The postgres data partition is on a 14-spindle hardware RAID5 on U320
SCSI disks.