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.
