Shulou(Shulou.com)06/01 Report--

No zuo no die series, from wiki of pg.

This section is about: do not use Don't use char (n) even for fixed-length identifiers.

The reason is:

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.

The reason is:

1. Can not achieve the desired fixed length effect, you can use check to achieve

two。 There is no performance advantage with char (n)

3. No index is needed when comparing char (n) and varchar (n)

Construct test data

[local]: 5432 pg12@testdb=# drop table if exists tasking charting drop TABLETime: 52.970 ms [local]: 5432 pg12@testdb=# create table t_char (id int,c1 char (10), c2 varchar (10)); CREATE TABLETime: 4.746 ms [local]: 5432 pg12@testdb=# create index idx_t_char_c1 on t_char (C1) CREATE INDEXTime: 6.712 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# insert into t_char values (1 1Time: 1.279 ms [local]: 5432 pg12@testdb=# insert into t_char values); INSERT 0 1Time: 0.770 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# insert into t_char values INSERT 0 1Time: 0.713 ms [local]: 5432 pg12@testdb=# insert into t_char values; INSERT 0 1Time: 0.722 ms [local]: 5432 pg12@testdb=# insert into t_char select x Magi | |'c1 from generate_series x | | 'c2' from generate_series (510 000) as x insertion 0 9996Time: 190.456 ms [local]: 5432 pg12@testdb=#

Query data

[local]: 5432 pg12@testdb=# analyze tasking charting: 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=#

If the query condition is text,char, you need to convert it to text, so you don't need index, but the varchar type is OK.

[local]: 5432 pg12@testdb=# create index idx_t_char_c2 on t_char (c2); CREATE INDEXTime: 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=#

Create check constraints and test performance impact

[local]: 5432 pg12@testdb=# drop table if exists tasking chartered checksetterDROP TABLETime: 6.303 ms [local]: 5432 pg12@testdb=# create table t_char_check (id int,c1 char (10), c2 varchar (10)); CREATE TABLETime: 3.682 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# insert into t_char_check select x | |'c1 magic x | |'c2 'from generate_series (1m 10000000) as x INSERT 0 10000000Time: 26485.287 ms [local]: 5432 pg12@testdb=# truncate table tweets chartered checkbox truncate TABLETime: 188.548 ms [local]: 5432 pg12@testdb=# drop table if exists tasking chartered checkboxes drop TABLETime: 2.059 ms [local]: 5432 pg12@testdb=# create table t_char_check (id int,c1 char (10), c2 varchar (10)) CREATE TABLETime: 1.838 ms [local]: 5432 pg12@testdb=# alter table t_char_check add constraint cst_t_char_checklength CHECK (length (C1))

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.