index not used for bigint without explicit cast

From: "Sam(dot)Mesh" <Sam(dot)Mesh(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: index not used for bigint without explicit cast
Date: 2023-01-18 01:43:37
Message-ID: CACi6F2nKR=5Tt21jwXcw2PAF_hfQfV7czvY92NT6onTUgR3=tw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello,

"index not used for bigint without explicit cast" is very old limitation
mentioned in 2003 at
https://www.postgresql.org/message-id/1054043810.93507.1.camel%40jester

Here is more recent mention of similar limitation in 2021
https://www.gojek.io/blog/the-case-s-of-postgres-not-using-index
***
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?

Thanks,
Sam

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Geoghegan 2023-01-18 01:55:36 Re: index not used for bigint without explicit cast
Previous Message Masahiko Sawada 2023-01-18 01:40:39 Re: BUG #17741: vacuum process hangs after pg_surgery manipulations