PostgreSQL中用MD5配合DENSE_RANK()生成主键是否更高效？

核心结论 #

PostgreSQL的查询优化器能识别md5($1)这类基于单行输入的表达式是稳定调用——每一行只会计算一次哈希转换，不会在排序比较阶段重复执行，计算量保持O(n)，而非你担心的O(n log n)。

具体分析 #

• 函数稳定性逻辑：md5()属于PostgreSQL的「稳定函数」（STABLE），只要输入的文本值不变，返回的哈希结果就固定。优化器处理窗口函数的排序子句时，会先批量算出所有行的哈希转换值，再用这些值做排序和排名，不会在排序的每一次比较里重复跑md5计算。
• 两种写法的性能对比：
  • 临时表写法：多了一步写入和读取临时表的磁盘IO，大数据集下会额外增加开销，反而拖慢速度。
  • 直接在OVER子句里写转换：省去了临时表的存储步骤，优化器可以直接在内存里完成哈希计算+排序，性能通常更好。
• 验证方法：执行EXPLAIN ANALYZE查看执行计划，能看到哈希计算是作为单独的步骤完成，不会嵌套在排序循环中。

实用建议 #

• 你用的('x'||substr(md5($1),1,16))::bit(64)::bigint转换逻辑没问题——取md5前16位转64位整数，既控制了长度，也符合你能容忍少量碰撞的需求。
• 如果数据集特别大，怕内存排序压力过高，可以考虑创建带哈希值的物化视图，后续查询直接基于这个视图操作，平衡计算和存储的开销。

内容的提问来源于stack exchange，提问作者Seán Healy