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
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 ? |