How Implicit Type Conversion Can Kill PostgreSQL Query Performance by 10,000×

Introduction

In production we often encounter performance problems caused by implicit type conversion in PostgreSQL. Even a seemingly harmless cast can make a query thousands of times slower by invalidating indexes and distorting row‑count estimates.

Simple Demonstration

Creating a table with an integer primary key and running a query that casts the column to text shows a sequential scan with a default selectivity of 0.005, causing massive row‑count misestimation.

# create table test1(id int primary key);
# insert into test1 values(generate_series(1,100000));
# analyze test1;
# explain select * from test1 where id::text = '999';
Seq Scan on test1  (cost=0.00..2193.00 rows=500 width=4)
  Filter: ((id)::text = '999'::text)

The planner assumes a very low selectivity, leading to an inefficient plan.

Default Selectivity Constants

#define DEFAULT_EQ_SEL 0.005
#define DEFAULT_RANGE_INEQ_SEL 0.005
#define DEFAULT_MULTIRANGE_INEQ_SEL 0.005
#define DEFAULT_MATCH_SEL 0.005

These defaults explain why the planner often chooses sub‑optimal strategies when implicit casts are present.

Char vs Varchar in PostgreSQL

PostgreSQL treats character , char , and bpchar as aliases, while varchar and text are preferred for variable‑length data. The blank‑padded char type incurs extra storage and CPU overhead.

When migrating from other databases, using char can unintentionally trigger implicit conversion to bpchar, breaking index usage.

Real‑World Example

Two tables are created: t1 with varchar(20) and t2 with char(20). An index on t1.info cannot be used because the join condition casts t1.info to bpchar, forcing a hash join.

# create table t1(id int,info varchar(20));
# create table t2(id int,info char(20));
# create index on t1(info);
# create index on t2(id);
# explain select info from t1 where info in (select info from t2 where id = 99);
Gather  (cost=1008.46..13621.97 rows=1 width=21)
  ->  Hash Join ...

After altering the column types so that both sides use the same data type, the planner switches to a nested‑loop join with an index scan, reducing execution time from several seconds to a few milliseconds.

# alter table t1 alter column info type char(20);
# alter table t2 alter column info type varchar(20);
# analyze t1,t2;
# explain select info from t1 where info in (select info from t2 where id = 99);
Nested Loop  (cost=8.87..16.91 rows=1 width=21)
  ->  Index Scan using t2_id_idx on t2 ...
  ->  Index Scan using t1_info_idx on t1 ...

Impact of Changing Types

Changing the column type forces a table rewrite and index rebuild, which can be observed via pg_relation_filepath output before and after the alteration.

# select pg_relation_filepath('t1');
# select pg_relation_filepath('t1_info_idx');
# alter table t1 alter column info type char(20);
# select pg_relation_filepath('t1');   -- new file path
# select pg_relation_filepath('t1_info_idx'); -- new index file path

Post‑optimization, query latency drops from ~400 ms to ~0.1 ms—a 4,000× improvement—highlighting the importance of matching data types in join conditions.

Additional Experiments

Further tests with a table containing both char and varchar columns demonstrate how different casts ( ::char, ::bpchar, ::varchar) affect the planner's choice between index scans, sequential scans, and parallel scans.

# create table t3(info char(20),info2 varchar(20));
# create index on t3(info);
# create index on t3(info2);
# explain select info from t3 where info = 'hello';
Index Only Scan using t3_info_idx ... (Index Cond: (info = 'hello'::bpchar))

Conclusion

Implicit type conversion can invalidate indexes, distort row‑count estimates, and lead to costly join strategies. Ensuring that join columns share the same data type—preferably varchar or text —is essential for optimal PostgreSQL performance.