Re: TPC-H Scaling Factors X PostgreSQL Cluster Command

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD