This is a small reminder to be careful with casting one data type to another in your queries when you want to have an index access rather than a sequential scan. Here is a small example of what can happen:

postgres=# create table t ( a int );
CREATE TABLE
postgres=# insert into t select * from generate_series(1,1000000);
INSERT 0 1000000

Creating an index on column “a” will speed up queries like this:

postgres=# create index i1 on t(a);
CREATE INDEX
postgres=# explain select * from t where a = 1;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Index Only Scan using i1 on t  (cost=0.42..4.44 rows=1 width=4)
   Index Cond: (a = 1)
(2 rows)

If you, however, add a cast to your query this will disable the index access:

postgres=# explain select * from t where a::text = '1';
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Gather  (cost=1000.00..13216.67 rows=5000 width=4)
   Workers Planned: 2
   ->  Parallel Seq Scan on t  (cost=0.00..11716.67 rows=2083 width=4)
         Filter: ((a)::text = '1'::text)
(4 rows)

If you really need to cast and you want to have an index access, then you need to create an index for this as well:

postgres=# create index i2 on t ( cast ( a as text ) );
CREATE INDEX
postgres=# explain select * from t where a::text = '1';
                             QUERY PLAN                              
---------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=95.17..4818.05 rows=5000 width=4)
   Recheck Cond: ((a)::text = '1'::text)
   ->  Bitmap Index Scan on i2  (cost=0.00..93.92 rows=5000 width=0)
         Index Cond: ((a)::text = '1'::text)
(4 rows)

Might seem obvious, but we still see this from time to time.