From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Luca Ferrari <fluca1978@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: why choosing an hash index instead of the btree version even if the cost is lower?
Date: Fri, 18 Nov 2022 09:55:12 -0500
Message-ID: <2186064.1668783312@sss.pgh.pa.us> (raw)
In-Reply-To: <52c1cb75-89bc-61c4-2ec9-42c683ba8f14@enterprisedb.com>
References: <CAKoxK+4ygm7hwRqVNgG9-Zge-sbN5kQfG_Zri9_Nxw7MF+35FA@mail.gmail.com>
<52c1cb75-89bc-61c4-2ec9-42c683ba8f14@enterprisedb.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, tomas.vondra@enterprisedb.com, fluca1978@gmail.com
Subject: Re: why choosing an hash index instead of the btree version even if the cost is lower?
In-Reply-To: <2186064.1668783312@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox