Message-ID: <52c1cb75-89bc-61c4-2ec9-42c683ba8f14@enterprisedb.com>
Date: Fri, 18 Nov 2022 14:23:31 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.1
Subject: Re: why choosing an hash index instead of the btree version even if
 the cost is lower?
Content-Language: en-US
To: Luca Ferrari <fluca1978@gmail.com>,
 postgres performance list <pgsql-performance@postgresql.org>
References: 
 <CAKoxK+4ygm7hwRqVNgG9-Zge-sbN5kQfG_Zri9_Nxw7MF+35FA@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: 
 <CAKoxK+4ygm7hwRqVNgG9-Zge-sbN5kQfG_Zri9_Nxw7MF+35FA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/52c1cb75-89bc-61c4-2ec9-42c683ba8f14%40enterprisedb.com>
Precedence: bulk

On 11/18/22 13:15, Luca Ferrari wrote:
> Hi all,
> I'm just having a doubt about the choice of the planner for a small
> example table.
> 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?
> 
> Now, even if the two costs are comparable, why does the optimizer
> chooses to use the larger hash index? What am I missing here and where
> do I have to dig?
> Using EXPLAIN ANALYZE shows that the two indexes are very similar
> timings, so while using the hash index is clearly not a wrong choice,
> I'm wondering why preferring a bigger index.
> 
> Please note that the table has been manually ANALYZEd and is not clustered.
> 

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.

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.

Now, this is based on the idea that

    small cost difference => small runtime difference

And from the timings you shared, this seems to be the case - 6.0 vs. 6.8
ms is fairly close.

I'd say btree/hash estimates this close are not very common in practice,
considering the costing formulas for the index types is quite different.
You may try tweaking the different cost parameters (e.g. operator cost,
random page cost) to make the difference larger.


regards

[1]
https://github.com/postgres/postgres/blob/master/src/backend/optimizer/util/pathnode.c#L51

[2]
https://github.com/postgres/postgres/blob/master/src/backend/optimizer/util/pathnode.c#L166

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company