PostgreSQL DBA(114) - pgAdmin(Don't use char(n) even for fixed-length id)

no zuo no die系列，来自于pg的wiki。
这一节的内容是：不要使用Don’t use char(n) even for fixed-length identifiers。
理由是:

Because char(n) doesn’t reject values that are too short, it just silently pads them with spaces. So there’s no actual benefit over using text with a constraint that checks for the exact length. As a bonus, such a check can also verify that the value is in the correct format.
Remember, there is no performance benefit whatsoever to using char(n) over varchar(n). In fact the reverse is true. One particular problem that comes up is that if you try and compare a char(n) field against a parameter where the driver has explicitly specified a type of text or varchar, you may be unexpectedly unable to use an index for the comparison. This can be hard to debug since it doesn’t show up on manual queries.

原因是：
1.达不到期望的固定长度效果，可改用check实现
2.使用char(n)没有任何性能优势
3.比较char(n)和varchar(n)时，用不上索引

构造测试数据

[local]:5432 pg12@testdb=# drop table if exists t_char;
DROP TABLE
Time: 52.970 ms
[local]:5432 pg12@testdb=# create table t_char(id int,c1 char(10),c2 varchar(10));
CREATE TABLE
Time: 4.746 ms
[local]:5432 pg12@testdb=# create index idx_t_char_c1 on t_char(c1);
CREATE INDEX
Time: 6.712 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_char values(1,'测试123','123123');
0);INSERT 0 1
Time: 1.279 ms
[local]:5432 pg12@testdb=# insert into t_char values(2,'abc123','123123');
INSERT 0 1
Time: 0.770 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_char values(3,'a','a ');
INSERT 0 1
Time: 0.713 ms
[local]:5432 pg12@testdb=# insert into t_char values(4,E'a\n',E'a\n');
INSERT 0 1
Time: 0.722 ms
[local]:5432 pg12@testdb=# insert into t_char select x,x||'c1',x||'c2' from generate_series(5,10000) as x;
INSERT 0 9996
Time: 190.456 ms
[local]:5432 pg12@testdb=#

查询数据

[local]:5432 pg12@testdb=# analyze t_char;
ANALYZE
Time: 83.740 ms
[local]:5432 pg12@testdb=# explain verbose select * from t_char where c1 = 'abc123'::varchar;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Index Scan using idx_t_char_c1 on public.t_char  (cost=0.29..8.30 rows=1 width=21)
   Output: id, c1, c2
   Index Cond: (t_char.c1 = 'abc123'::bpchar)
(3 rows)
Time: 3.980 ms
[local]:5432 pg12@testdb=# explain verbose select * from t_char where c1 = 'abc123'::text;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Seq Scan on public.t_char  (cost=0.00..214.00 rows=50 width=21)
   Output: id, c1, c2
   Filter: ((t_char.c1)::text = 'abc123'::text)
(3 rows)
Time: 2.014 ms
[local]:5432 pg12@testdb=#

如查询条件为text，char需转换为text，因此用不上index，而varchar类型是可以的。

[local]:5432 pg12@testdb=# create index idx_t_char_c2 on t_char(c2);
CREATE INDEX
Time: 56.200 ms
[local]:5432 pg12@testdb=# explain verbose select * from t_char where c2 = 'abc123'::text;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Index Scan using idx_t_char_c2 on public.t_char  (cost=0.29..8.30 rows=1 width=21)
   Output: id, c1, c2
   Index Cond: ((t_char.c2)::text = 'abc123'::text)
(3 rows)
Time: 4.248 ms
[local]:5432 pg12@testdb=#

创建check约束，并测试性能影响

[local]:5432 pg12@testdb=# drop table if exists t_char_check;
DROP TABLE
Time: 6.303 ms
[local]:5432 pg12@testdb=# create table t_char_check(id int,c1 char(10),c2 varchar(10));
CREATE TABLE
Time: 3.682 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_char_check select x,x||'c1',x||'c2' from generate_series(1,10000000) as x;
INSERT 0 10000000
Time: 26485.287 ms (00:26.485)
[local]:5432 pg12@testdb=# truncate table t_char_check;
TRUNCATE TABLE
Time: 188.548 ms
[local]:5432 pg12@testdb=# drop table if exists t_char_check;
DROP TABLE
Time: 2.059 ms
[local]:5432 pg12@testdb=# create table t_char_check(id int,c1 char(10),c2 varchar(10));
CREATE TABLE
Time: 1.838 ms
[local]:5432 pg12@testdb=# alter table t_char_check add constraint cst_t_char_checklength CHECK(length(c1) <= 10);
ALTER TABLE
Time: 3.775 ms
[local]:5432 pg12@testdb=# insert into t_char_check(id,c1,c2) select x,x||'c1',x||'c2' from generate_series(1,10000000) as x;
INSERT 0 10000000
Time: 26963.331 ms (00:26.963)
[local]:5432 pg12@testdb=#

Time: 26485.287 ms (00:26.485) vs Time: 26963.331 ms (00:26.963)，性能几乎没有什么影响。

参考资料
Don’t Do This