>有一个表名称作为一种中央注册表.每一行都有一个文本字段表示和一个唯一键,它是该表示的MD5哈希.1该表目前有数千万条记录,预计在应用程序的生命周期内会增长到数十亿.
>有许多其他表(具有高度变化的模式和记录计数)引用了名称表.其中一个表中的任何给定记录都保证有一个name_key,它在功能上是names表的外键.
1：顺便说一句,正如您所料,此表中的记录一旦写入就是不可变的.
对于除names表之外的任何给定表,最常见的查询将遵循以下模式：
SELECT list,of,fields FROM table WHERE name_key IN (md5a,md5b,md5c...);
我想优化读取性能.我怀疑我的第一站应该是最小化指数的大小(虽然我不介意被证明是错误的).
问题：
key和name_key列的最佳数据类型是什么？
是否有理由在位(128)上使用十六进制(32)？ BTREE还是GIN？
uuid非常适合该任务.对于varchar或文本表示,它仅占用16个字节而不是RAM中的37个字节. (或者在磁盘上有33个字节,但奇数在许多情况下需要填充以使其有效地达到40个字节.)并且uuid类型具有更多优点.
例：
SELECT md5('Store hash for long string,maybe for index?')::uuid AS md5_hash
细节和更多解释：
> Convert hex in text representation to decimal number
> Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
如果你不需要md5的加密组件,你可能会考虑其他(更便宜的)散列函数,但我会使用md5作为你的用例(主要是只读的).
一句警告：对于你的情况(一旦写入不可变),功能依赖(伪自然)PK就可以了.但同样令人痛苦的是文本更新是可能的.考虑纠正一个错字：PK和所有依赖索引,其他几十个表中的FK列和其他引用也必须改变.表和索引膨胀,锁定问题,缓慢更新,丢失引用,…
如果文本在正常操作中可以改变,那么surrogate PK将是更好的选择.我建议使用bigserial列(范围-9223372036854775808到9223372036854775807 – 即nine quintillion two hundred twenty-three quadrillion three hundred seventy-two trillion thirty-six something billion)数十亿行的不同值.在任何情况下都可能是个好主意：对于数十个FK列和索引,8个而不是16个字节！).或random UUID更大的基数或分布式系统.您可以随时存储所述md5(作为uuid)以快速从原始文本中查找主表中的行.有关：
> Default value for UUID column in Postgres
至于你的查询：
> Optimizing a Postgres query with a large IN
要解决@Daniel’s comment：如果您更喜欢不带连字符的表示,请删除显示的连字符：
SELECT replace('90b7525e-84f6-4850-c2ef-b407fae3f271','-','')
但我不会打扰.默认表示就好了.这个问题真的不是这里的代表.
如果其他方应该采用不同的方法并将没有连字符的字符串放入混合中,那也不是问题. Postgres接受几个合理的文本表示作为uuid的输入. The documentation：
Postgresql also accepts the following alternative forms for input: use
of upper-case digits,the standard format surrounded by braces,
omitting some or all hyphens,adding a hyphen after any group of four
digits. Examples are:06002
更重要的是,md5()函数返回文本,你将使用decode()转换为bytea,默认表示为：
SELECT decode(md5('Store hash for long string,maybe for index?'),'hex')
\220\267R^\204\366HP\302\357\264\007\372\343\362q
您必须再次编码()以获取原始文本表示：
SELECT encode(my_md5_as_bytea,'hex');
最重要的是,由于internal varlena overhead,存储为bytea的值将占用RAM中的20个字节(以及磁盘上的17个字节,24 with padding),这对于简单索引的大小和性能特别不利.
一切都在这里支持uuid.
