Re: why choosing an hash index instead of the btree version even if the cost is lower?

On Fri, Nov 18, 2022 at 2:23 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> That is, when comparing costs, we require the cost to be at least 1%,
> because we have a cheapest path, and we're checking if it's worth
> building another one (which is not free - we have to allocate stuff
> etc.). And if the difference is tiny, it's not worth it.
>
> In this case we have the indexscan for the hash index, with cost
> 8971.95, and we're considering to build indexacan path for the btree
> index. We haven't built it yet, we only calculate cost 8891.65. But
>
> 8971.95/8891.65 = 1.009
>
> So it's close to 1.01, but just a little bit less. So we conclude it's
> not worth building the second path, and we keep the hash index scan.
>

An excellent explanation, it totally does make sense to me and
explains what I felt (i.e., similar costs lead to a kind of equality
in choosing the index).

Thanks,
Luca