On Sat, 21 Apr 2007, Nelson Kotowski wrote:
I identified that the cluster command over the lineitem table (cluster idx_lineitem on lineitem) is the responsible. I got to this conclusion because when i run it in the 1GB and 2GB database i am able to complete this script in 10 and 30 minutes each. But when i run this command over the 5GB database, it simply seems as it won't end.
Have you looked in the database log files for messages? Unless you changed some other parameters from the defaults that you didn't mention, I'd expect you've got a constant series of "checkpoint occuring too frequently" errors in there, which would be a huge slowdown on your index rebuild. Slowdowns from checkpoints would get worse with an increase of shared_buffers, as you report.
The default setting for checkpoint_segments of 3 is extremely low for even a 1GB database. Try increasing that to 30, restart the server, and rebuild the index to see how much the 1GB case speeds up. If it's significantly faster (it should be), try the 5GB one again.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD
