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.
Advertising
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. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
