Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
> On 11/18/22 13:15, Luca Ferrari wrote:
>> I've a table with a numeric column (integer), and I've created two
>> indexes on such column, one btree and one hash. The hash results much
>> larger as the btree, but what puzzles me is that executing an equality
>> simple query, the system chooses the hash index (that has a final cost
>> of 8984.08 while the btree index would have a final cost a little
>> lower (8901.94).
>>
>> The only difference I can spot in the EXPLAIN plans is that the btree
>> index has an initial cost, but I don't think this is the reason, since
>> it should be the final cost what matters, right?
> My guess is this is due to STD_FUZZ_FACTOR, see [1] and [2].
> 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.
Even more to the point: if the total costs are fuzzily the same,
then the next point of comparison will be the startup costs,
which is where the hash index wins. I'm not sure if it's quite
fair to give hash a zero startup cost; but it doesn't have to
descend a search tree, so it is fair that its startup cost is
less than btree's.
regards, tom lane
