Why do slow queries and resource contention occur when querying float data in an int column?

I'm seeing some performance issues with my database when querying for data that matches a float data type in an int column. This seems to be causing slow queries and resource contention, as multiple sessions are trying to do the same thing at the same time.

I'm curious to understand the underlying reason for this issue. Why does querying for float data in an int column cause slow queries and resource contention?Is there a way to avoid this issue without modifying the schema or optimizing the queries? Any explanation or advice would be greatly appreciated.

my query is as below:

>  `sql_command = f"SELECT a, b, c, d, " \
>                 f"timestamp FROM {schema}.table " \
>                 f"where timestamp = {ref_ts * 1000}"`

where "timestamp" is the int column.

ENV: python3.10, postgresql,
connecting via SQLAlchemy version 2.0.3,
using, pd.to_sql(sql_command, connection) method.
I've tried in the Dbeaver client, but the same issue occred.

I've changed the timestamp in the command as int type and the query performance issue resolved.

If the column is defined as int and has an index on it, you have to use a predicate with an int datatype to be able to use the index.

While using a numeric value a data type conversion of the indexed column is performed that disables the index use and you fall back to the Seq Scan.

Demonstration

Correct usage

explain (analyze, verbose, buffers)
select * from tab where ID = 1;

leads to Index Scan (output cut for brevity)

Index Scan using tab_idx on jdbn.tab  (cost=0.14..8.16 rows=1 width=1008) (actual time=0.005..0.009 rows=2 loops=1)
  Output: id, pad
  Index Cond: (tab.id = 1)

While the incorrect usage

explain (analyze, verbose, buffers)
select * from tab where ID = 1.0;

triggers a Seq Scan

Seq Scan on jdbn.tab  (cost=0.00..32.00 rows=1 width=1008) (actual time=0.012..0.094 rows=2 loops=1)
  Output: id, pad
  Filter: ((tab.id)::numeric = 1.0)

Note the cast in the filter that inhibits the index usage (tab.id)::numeric

But of course you should use bind variable (instead of a concatenated literal value) as pointed out in a comment.

The correct predicate would cast the parameter to int to assure the index usage:

where ID = :1::int;

Setup

CREATE TABLE tab (
     id int NOT null,
     pad text
);
    
insert into tab select id, repeat('x',1000) pad from generate_series(1,100) t(id);

CREATE INDEX tab_idx ON tab USING btree (id);

analyze tab;