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.
Questions about your own setup? A dbi services consultant can look at your case.
Talk to an expert (opens in a new tab)