Hash index initial size is too large given NULLs or partial indexes

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Hash index initial size is too large given NULLs or partial indexes
Дата
Msg-id CAMkU=1x0k+dRQHDUgp4BjFeSgxyLBBXyKNY5Pt1Yu6YHB0mhKA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Hash index initial size is too large given NULLs or partialindexes
Список pgsql-hackers
Referring to this thread:


When a hash index is created on a populated table, it estimates the number of buckets to start out with based on the number of tuples returned by estimate_rel_size.  But this number ignores both the fact that NULLs are not stored in hash indexes, and that partial indexes exist.  This can lead to much too large hash indexes.  Doing a re-index just repeats the logic, so doesn't fix anything.  Fill factor also can't fix it, as you are not allowed to increase that beyond 100.

This goes back to when the pre-sizing was implemented in 2008 (c9a1cc694abef737548a2a).  It seems to be an oversight, rather than something that was considered.

Is this a bug that should be fixed?  Or if getting a more accurate estimate is not possible or not worthwhile, add a code comment about that?

Cheers,

Jeff

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Pavel Stehule
Дата:
Сообщение: Re: PostgreSQL vs SQL/XML Standards
Следующее
От: Tomas Vondra
Дата:
Сообщение: Re: Hash index initial size is too large given NULLs or partialindexes