postgresql – 当所有值都是36个字符时,char和varchar的索引查找

Tip: There is no performance difference among these three types,apart
from increased storage space when using the blank-padded type,and a
few extra CPU cycles to check the length when storing into a
length-constrained column. While character(n) has performance
advantages in some other database systems,there is no such advantage
in PostgreSQL; in fact character(n) is usually the slowest of the
three because of its additional storage costs. In most situations text
or character varying should be used instead.

大胆强调我的.

char(n)是一种基本上过时的,无用的类型.坚持varchar(n).如果您不需要强制执行长度,则varchar或text会更快一点.
你将无法衡量差异.

此外,如果所有字符串的长度恰好为36个字符,则无论如何都不会保存存储空间,即使是非常小的存储空间也是如此.两者在磁盘和RAM中的大小完全相同.您可以使用pg_column_size()进行测试(在表达式和表格列上).

有关：

> Any downsides of using data type “text” for storing strings?

你没有要求其他选择,但我会提到两个：

> COLLATION – 除非您使用“C” collation运行数据库.整理经常被忽视并且可能很昂贵.由于您的字符串在自然语言中似乎没有意义,因此遵循COLLATION规则可能没有意义.有关：

> How do I efficiently get “the most recent corresponding row”?
> EXECUTE within function not using index?

广泛的基准比较(以及其他)COLLATE“C”对性能的影响：

> Slow query ordering by a column in a joined table

> UUID,显然.您的字符串可疑地看起来像UUID(32位十六进制数字加上4个分隔符).将这些作为实际的uuid数据类型存储会更加有效,它在多种方式下更快并且仅占用16个字节 – 而在RAM(37)或varchar(36)中存储37个字节(存储没有分隔符,只是32个定义的char),或磁盘上的33个字节.但在许多情况下,alignment padding将导致40字节.)COLLATION也与uuid数据类型无关.

SELECT '922475bb-ad93-43ee-9487-d2671b886479'::uuid

这可能会有所帮助(最后几章)：

> Convert hex in text representation to decimal number

也可以看看：

> What is the overhead for varchar(n)?
> What is the optimal data type for an MD5 field?