Saturday, November 27, 2021

Impact on trailing white space and implicit data type cast between char, varchar and text in PostgresSQL

 Summary: 

  • Comparasion between char and varchar, implicitly cast varchar to bpchar (blank padded char, which is an internal type for char)
  • Comparison between char and text, implicitly cast char to text
  • Comparison between varchar and text, implicitly cast varchar to text.
Test Environment: PostgreSQL 13.4

create table t1 (id int, c_char char(10),c_varchar varchar(10), c_text text);
insert into t1 values(1,'a','a','a');
insert into t1 values(2,'a  ','a  ','a  ');

mytest=> \d t1
                         Table "public.t1"
  Column   |         Type          | Collation | Nullable | Default 
-----------+-----------------------+-----------+----------+---------
 id        | integer               |           |          | 
 c_char    | character(10)         |           |          | 
 c_varchar | character varying(10) |           |          | 
 c_text    | text                  |           |          | 

mytest=> select id, c_char||'*' c_char, c_varchar||'*' c_varchar, c_text||'*' c_text from t1;
 id | c_char | c_varchar | c_text 
----+--------+-----------+--------
  1 | a*     | a*        | a*
  2 | a*     | a  *      | a  *
(2 rows)


-- Test 1: comparing char with char

mytest=> select a.id aid,b.id bid from t1 a, t1 b where a.c_char=b.c_char;
 aid | bid 
-----+-----
   1 |   2
   1 |   1
   2 |   2
   2 |   1
(4 rows)


mytest=> explain
mytest-> select a.id aid,b.id bid from t1 a, t1 b where a.c_char=b.c_char;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Hash Join  (cost=22.38..92.18 rows=1512 width=8)
   Hash Cond: (a.c_char = b.c_char)
   ->  Seq Scan on t1 a  (cost=0.00..15.50 rows=550 width=48)
   ->  Hash  (cost=15.50..15.50 rows=550 width=48)
         ->  Seq Scan on t1 b  (cost=0.00..15.50 rows=550 width=48)

-- Test 2: comparing char with varchar

mytest=> select a.id aid,b.id bid from t1 a, t1 b where a.c_char=b.c_varchar;
 aid | bid 
-----+-----
   1 |   2
   1 |   1
   2 |   2
   2 |   1
(4 rows)

mytest=> explain
mytest-> select a.id aid,b.id bid from t1 a, t1 b where a.c_char=b.c_varchar;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Hash Join  (cost=22.38..92.18 rows=1512 width=8)
   Hash Cond: (a.c_char = (b.c_varchar)::bpchar)
   ->  Seq Scan on t1 a  (cost=0.00..15.50 rows=550 width=48)
   ->  Hash  (cost=15.50..15.50 rows=550 width=42)
         ->  Seq Scan on t1 b  (cost=0.00..15.50 rows=550 width=42)
(5 rows)

-- Test 3: comparing char with text

mytest=> select a.id aid,b.id bid from t1 a, t1 b where a.c_char=b.c_text;
 aid | bid 
-----+-----
   1 |   1
   2 |   1
(2 rows)

mytest=> explain
mytest-> select a.id aid,b.id bid from t1 a, t1 b where a.c_char=b.c_text;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Merge Join  (cost=81.07..111.65 rows=1512 width=8)
   Merge Cond: (((a.c_char)::text) = b.c_text)
   ->  Sort  (cost=40.53..41.91 rows=550 width=48)
         Sort Key: ((a.c_char)::text)
         ->  Seq Scan on t1 a  (cost=0.00..15.50 rows=550 width=48)
   ->  Sort  (cost=40.53..41.91 rows=550 width=36)
         Sort Key: b.c_text
         ->  Seq Scan on t1 b  (cost=0.00..15.50 rows=550 width=36)
(8 rows)


-- Test 4: comparing varchar with varchar

mytest=> select a.id aid,b.id bid from t1 a, t1 b where a.c_varchar=b.c_varchar;
 aid | bid 
-----+-----
   1 |   1
   2 |   2
(2 rows)

mytest=> explain
mytest-> select a.id aid,b.id bid from t1 a, t1 b where a.c_varchar=b.c_varchar;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Hash Join  (cost=22.38..92.18 rows=1512 width=8)
   Hash Cond: ((a.c_varchar)::text = (b.c_varchar)::text)
   ->  Seq Scan on t1 a  (cost=0.00..15.50 rows=550 width=42)
   ->  Hash  (cost=15.50..15.50 rows=550 width=42)
         ->  Seq Scan on t1 b  (cost=0.00..15.50 rows=550 width=42)
(5 rows)


-- Test 5: comparing varchar with text

mytest=> select a.id aid,b.id bid from t1 a, t1 b where a.c_varchar=b.c_text;
 aid | bid 
-----+-----
   1 |   1
   2 |   2
(2 rows)

mytest=> explain
select a.id aid,b.id bid from t1 a, t1 b where a.c_varchar=b.c_text;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Hash Join  (cost=22.38..92.18 rows=1512 width=8)
   Hash Cond: ((a.c_varchar)::text = b.c_text)
   ->  Seq Scan on t1 a  (cost=0.00..15.50 rows=550 width=42)
   ->  Hash  (cost=15.50..15.50 rows=550 width=36)
         ->  Seq Scan on t1 b  (cost=0.00..15.50 rows=550 width=36)
(5 rows)


-- Test 6: comparing text with text

mytest=> select a.id aid,b.id bid from t1 a, t1 b where a.c_text=b.c_text;
 aid | bid 
-----+-----
   1 |   1
   2 |   2
(2 rows)

mytest=> explain
select a.id aid,b.id bid from t1 a, t1 b where a.c_text=b.c_text;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Hash Join  (cost=22.38..92.18 rows=1512 width=8)
   Hash Cond: (a.c_text = b.c_text)
   ->  Seq Scan on t1 a  (cost=0.00..15.50 rows=550 width=36)
   ->  Hash  (cost=15.50..15.50 rows=550 width=36)
         ->  Seq Scan on t1 b  (cost=0.00..15.50 rows=550 width=36)
(5 rows)

No comments:

Post a Comment