Re: index not used for bigint without explicit cast

I've forgotten to say thanks. Thank you everybody for clarifications,
especially to Tom Lane.

On Wed, Jan 18, 2023 at 9:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Noah Misch <noah@leadboat.com> writes:
> > On Wed, Jan 18, 2023 at 01:43:43PM -0800, Sam.Mesh wrote:
> >> Could you please double check the following reasoning based on
> >> https://www.postgresql.org/docs/current/btree-behavior.html?
> >> - Index search by bigint column requires conversion of limiting
> >> expressions to bigint type.
> >> - Conversion from number(19,0) to bigint may cause overflow.
> >> - So, index search is not possible.
>
> > Essentially, yes.  This is a query planner limitation, not a fundamental
> > property of the search problem.  "bigintcol = '5.1'::numeric" is equivalent to
> > "bigintcol <> bigintcol" or "CASE WHEN bigintcol IS NULL THEN NULL ELSE false
> > END".  In contexts that don't distinguish "false" from NULL, it's equivalent
> > to constant "false".  Those observations apply to any search value such that
> > 'search_value'::numeric::bigint::numeric <> 'search_value'::numeric does not
> > return true, including overflow-error cases.  Like many datatype-specific
> > tricks, the planner isn't aware at this time.
>
> Hmm ... I don't think the planner would be the place to try to change
> this.  The fundamental thing to do if you wanted to improve this case
> would be to invent the "bigint = numeric" operator (and its whole
> family, such as "numeric = bigint", "bigint < numeric", "smallint <
> numeric", etc etc) and make those part of the integer_ops opfamily.
> Which'd probably lead to merging integer_ops and numeric_ops into
> a single opfamily.
>
> As far as I can think at the moment, there are not any insurmountable
> semantic obstacles to doing that.  There are good reasons not to try
> to merge integer and float opfamilies, namely that transitivity of
> equality would fail because of inexact conversions; but I can't see
> how that would occur between integer-family types and numeric, with
> all of those being exact types.
>
> Nonetheless, I'm not eager to try to do it.  The practical hazards
> are at least two: integer vs. numeric comparison operators will be
> unpleasantly slow, and adding dozens more identically-named operators
> will increase the risks of getting "ambiguous operator" errors in the
> parser.
>
> In the end I'd ask why is this a problem.  If you converted your
> tables from Oracle-ish number(19,0) to bigint, and did not convert
> your application to use bigint instead of number(19,0), that sounds
> like self-inflicted damage.  Do both or neither.
>
>                         regards, tom lane