bpchar, text and indexes

From: Victor Yegorov <vyegorov(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: bpchar, text and indexes
Date: 2016-02-22 15:45:04
Message-ID: CAGnEbohnFkRWrW1rAiZhobGhabwUNGBJxFwGMAVDGzQfpp5bDw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2016-02-22 15:58:34 Re: Why does query planner choose slower BitmapAnd ?
Previous Message Seamus Abshere 2016-02-22 15:18:41 Why does query planner choose slower BitmapAnd ?