Обсуждение: Hash indexes and effective_cache_size in CREATE INDEX documentation

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

Peter Geoghegan <pg@heroku.com> writes:
> 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.

Oooh.  That should've been changed by 4adc2f72, and then again in
9563d5b5.  Will fix.
        regards, tom lane

I wrote:
> Peter Geoghegan <pg@heroku.com> writes:
>> 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.

> Oooh.  That should've been changed by 4adc2f72, and then again in
> 9563d5b5.  Will fix.

After looking at that a bit, I'm strongly tempted to just take out
the last two sentences of the para, reducing it to the advice concerning
maintenance_work_mem.  That seems sufficient to describe the current
behavior, and given our awful track record about maintaining this
documentation, I'm not sure that going into more detail is really
a good idea.  Comments?
        regards, tom lane

On Sun, Jul 31, 2016 at 10:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> After looking at that a bit, I'm strongly tempted to just take out
> the last two sentences of the para, reducing it to the advice concerning
> maintenance_work_mem.  That seems sufficient to describe the current
> behavior, and given our awful track record about maintaining this
> documentation, I'm not sure that going into more detail is really
> a good idea.  Comments?

+1

-- 
Peter Geoghegan