Re: bpchar, text and indexes

Victor Yegorov schrieb am 22.02.2016 um 16:45:
> 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
>

I assume that this has to do with the fact that char(n) is blank padded to 20 character.

To be able to correctly compare that to a text value, sn_c has to be casted to text and then the index (which contains blank padded values) can not be used any more.

Another very good example why the dreaded char() should not be used ;)

If you use varchar(20) instead of char(20) both queries yield the same execution plan (at least on my local 9.5)

Thomas