The CREATE INDEX documentation states:
"For hash indexes, the value of effective_cache_size is also relevant
to index creation time: PostgreSQL will use one of two different hash
index creation methods depending on whether the estimated index size
is more or less than effective_cache_size. For best results, make sure
that this parameter is also set to something reflective of available
memory, and be careful that the sum of maintenance_work_mem and
effective_cache_size is less than the machine's RAM less whatever
space is needed by other programs."
The hash index build process does not actually care about
effective_cache_size at all -- this extract may have been written with
the intent of describing the threshold at which a hash tuplesort is
used to build a hash index, something that is based on shared_buffers
(or, in 9.6, maintenance_work_mem). OTOH, GiST index builds do
consider effective_cache_size, as noted elsewhere on the CREATE INDEX
page.
I was sure that the hash index behavior with regard to using tuplesort
was not described in the documentation (this came up recently during
discussion of what became commit 9563d5b5), and have said so on list.
I'm now not so sure that that's actually the case.
--
Peter Geoghegan
