Greetings. I'd like to understand why Postgres behaves the way it does. I was not able to find relevant mail thread myself, if one exists — please, point at it.
Test setup: PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit create table t(t_id int4, sn_c char(20)); insert into t select id, chr((random()*26)::int4+65)||chr((random()*26)::int4+65)||((random()*99999)::int4+1) from generate_series(1, 10000) id; create index i_t_sn_c on t(sn_c); vacuum analyze t; Now, if I do a typical query, all is good: postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c = 'AB1234'; QUERY PLAN ------------------------------------------------------------------------------- Index Only Scan using i_t_sn_c on t (actual time=0.015..0.015 rows=0 loops=1) Index Cond: (sn_c = 'AB1234'::bpchar) Heap Fetches: 0 If I explicitly cast constant to `text`, then Postgres will add `(sn_c)::text` cast, which disables index: postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c = 'AB1234'::text; QUERY PLAN --------------------------------------------------------- Seq Scan on t (actual time=5.729..5.729 rows=0 loops=1) Filter: ((sn_c)::text = 'AB1234'::text) Rows Removed by Filter: 10000 Although, if I will use LIKE instead of equality, then index is used: postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c ~~ 'AB1234'::text; QUERY PLAN ------------------------------------------------------------------------------- Index Only Scan using i_t_sn_c on t (actual time=0.012..0.012 rows=0 loops=1) Index Cond: (sn_c = 'AB1234'::bpchar) Filter: (sn_c ~~ 'AB1234'::text) Heap Fetches: 0 And what I also see is — `varchar` has no such effect: postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c = 'AB1234'::varchar; QUERY PLAN ------------------------------------------------------------------------------- Index Only Scan using i_t_sn_c on t (actual time=0.041..0.041 rows=0 loops=1) Index Cond: (sn_c = 'AB1234'::bpchar) Heap Fetches: 0 My questions are: 1. according to `pg_cast`, `text` => `bpchar` is binary coercible. Why Postgres is casting `sn_c` to `text` here, disabling index usage? 2. as I can see in `pg_cast`, setup for `varchar` is pretty much the same: `varchar` => `bpchar` is also binary coercible. So why for `varchar` behaviour is different? Thanks in advance. -- Victor Y. Yegorov