Re: hash index improving v3

Simon Riggs <simon@2ndQuadrant.com> 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.

Perhaps there is a case for giving this behavior its very own
configuration parameter; but seeing that we still don't have all that
much of a use case for hash indexes at all, I don't feel a need to do
that yet.  In any case, tying it to maintenance_work_mem is certainly
wrong.

            regards, tom lane