PostgreSQL中使用bytea作为表索引与关联条件的性能问题问询

• 优先替换bytea类型为更适配关联的字段类型
  PostgreSQL对字符串、数值类型的关联优化成熟度远高于bytea，可根据场景选择：非安全校验场景仅需要简化关联逻辑的话，用内置hashtext()、hash_combine()函数生成4字节整数哈希代理键，关联性能是长哈希的2_{3倍；需要保留哈希校验能力的话，把SHA512结果转十六进制字符串存为`char(64)`，关联性能比`bytea`高30%}50%；如果可以降低哈希长度，用SHA256存char(32)或者把128位哈希转uuid类型存储，关联性能还能进一步提升。
• 优先使用原生复合键替代哈希代理键
  如果你的复合键字段不超过3个，直接用原生复合主键、复合外键做关联即可，PostgreSQL对复合索引的优化非常成熟，不需要额外加哈希转换层，反而会减少不必要的转换开销。
• 必须保留bytea类型时，换用哈希索引
  关联查询都是等值匹配场景，CREATE INDEX idx_xxx_hash ON 表名 USING hash (哈希字段名);创建哈希索引，比默认B树索引的长字段等值匹配性能高很多。

• 检查关联字段索引有效性
  首先确认两张关联表的哈希字段都已创建适配的索引，其次排查是否存在隐式类型转换：如果关联字段一个是bytea一个是字符串，或者字符串collation不一致，PostgreSQL会跳过索引走全表扫描，直接导致性能暴跌。
• 检查哈希碰撞率
  哈希值重复率超过1%时，PostgreSQL执行计划会优先选择全表扫描而非索引扫描，可执行select 哈希字段, count(*) from 表名 group by 哈希字段 having count(*) >1;确认碰撞率，碰撞率过高时需要换更长的哈希算法或者直接使用原生复合键。
• 检查Aurora PostgreSQL参数配置
  重点核对work_mem参数：关联查询用到哈希连接、排序逻辑时，work_mem过小会导致临时数据落盘，性能下降几个数量级，可先调整会话级参数测试：set work_mem = '64MB';，再执行关联查询验证性能变化。
• 检查执行计划
  执行EXPLAIN ANALYZE 你的关联查询语句;查看执行逻辑，确认关联是否走索引、关联类型是否为最优的Hash Join，若出现Nested Loop全表扫描、Merge Join强制排序的情况，需要针对性调整索引或者参数配置。

内容的提问来源于stack exchange，提问作者BilliD