Re: index not used for bigint without explicit cast

On Tue, Jan 17, 2023 at 5:44 PM Sam.Mesh <Sam(dot)Mesh(at)gmail(dot)com> wrote:
> The problem arrises with the other case, where the values from the table are to be converted to match the datatype of the query (i.e., we give numeric in query and the column is of bigint type). As Postgres can only cast a bigint to numeric, the only option it has is to convert every row in the table to numeric and then compare. Thus in this case, the index won’t be used.
> ***
>
> Probably, somebody knows the current state of this limitation?

Not all numeric values can be converted to int8 without loss of
precision. If it was allowed, it would create subtle problems. The
same is not true for (say) int4 and int8, which can be mixed in the
way that you would expect.

Internally, int4 and int8 are part of the same btree operator family,
and so follow certain rules which are described here:

https://www.postgresql.org/docs/current/btree-behavior.html

--
Peter Geoghegan