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

no zuo no die系列，來自于pg的wiki。
這一節(jié)的內(nèi)容是：不要使用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實現(xiàn)
2.使用char(n)沒有任何性能優(yōu)勢
3.比較char(n)和varchar(n)時，用不上索引

構(gòu)造測試數(shù)據(jù)

[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=#

查詢數(shù)據(jù)

[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需轉(zhuǎn)換為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=#

創(chuàng)建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