Search Postgresql Archives

bpchar, text and indexes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux