On Tue, 2008-09-23 at 00:48 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > I wasn't very happy with effective_cache_size and not happy with > > shared_buffers either. If building hash indexes is memory critical then > > we just need to say so and encourage others to set memory use correctly. > > People are already aware that maintenance_work_mem needs to be increased > > for large index builds and we will confuse people if we ignore that and > > use another parameter instead. > > I think you've got this completely backwards. The general theory about > maintenance_work_mem is "set it as large as you can stand it". The > issue at hand here is that the crossover point for hash index sort > building seems to be a good deal less than all-the-memory-you-have.
There's an optimal point for btree builds using sorts that is a good deal less also, so I don't get that. Plus, if you give it all-the-memory-you-have there won't be anything left for anything else. You might set it that high for an empty database load but you don't set it that high in production unless you want to see swapping when you create large indexes. maintenance_work_mem is already used for 3 separate operations that bear little resemblance to each other. If it's appropriate for all of those then its appropriate for this usage also. Doing it otherwise is going to mean more than 50% of people do the wrong thing, which would be a shame because what's been done looks very cool. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-patches mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches