We frequently recommend to people that they increase sort_mem while
creating btree indexes. It is reasonable to have a larger setting
for that purpose, since (1) a single backend isn't going to be doing
multiple index creations in parallel (whereas complex queries could
easily be doing multiple sorts or hashes in parallel), and (2) in most
installations you won't have a large number of backends doing index
creations in parallel. So while sort_mem has to be set on the
assumption that you might need quite a few times the nominal setting,
this isn't true for index creation.
It strikes me that we ought to revise the configuration options to
reflect this fact: index creation's memory limit should be driven by
a separate parameter instead of using sort_mem.
We already have a memory-usage parameter that is larger than sort_mem,
and for exactly the same reasons sketched above. It's vacuum_mem.
VACUUM is also an operation that you don't expect to be running lots of
instances of in parallel, so it's okay for it to eat more than average
amounts of RAM.
So, what I'd like to do is make btree index creation pay attention to
vacuum_mem instead of sort_mem, and rename the vacuum_mem parameter to
some more-generic name indicating that it's used for more than just
VACUUM. Any objections so far?
Now, what should we call it instead? I haven't come up with any
compelling thoughts --- the best I can do is "big_sort_mem" or
"single_sort_mem". Surely someone out there has a better idea.
BTW, does anyone want to lobby for renaming sort_mem at the same time?
Since it's used for sizing hash tables as well as sort workspace, it's
rather misnamed. I hesitate to rename it because of the potential for
confusion though. People are pretty used to the existing name.
regards, tom lane
