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
