Re: BUG #17594: conditional hash indexes size (hash index ignore WHERE condition during CREATE INDEX?)

On Thu, Aug 25, 2022 at 11:49 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > I found very weird situation with size of highly selective partial hash
> > indexes (they has almost same size as full table hash index).
>
> hashbuild() sets up the initial hash index size based on estimating
> the current number of rows in the table, without any correction for
> partial-index selectivity. I don't find this to be a bug particularly.
> The odds of making things worse via a bad estimate seem at least as
> high as the odds of making things better.
>
> regards, tom lane
>

Sometimes it lead to unexpected results, for hash indexes most common
(imho) use case is indexing long text/varchar columns on big tables where
they provide substantial size reduction over usual btree indexes (or in
extreme case of very long test columns - btree cannot be used at all). Now
in the case of a partial hash index over a huge table (with highly
selective conditions thus covering only a tiny part of rows) - size of
resulting hash index could be a huge surprise (in the practical case where
I found this issue it was 200MB hash index with only 5 rows in it).
Are most pages of hash index in that case will be completely empty and
almost never touched by the database (thus wasting only disk space) or the
resulting hash index will have very low rows per index page ratio (thus
inefficiently use shared buffers as well)?

--
Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/

Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk

"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно
когда я так делаю ещё раз?"