Postgres14哈希分区表使用LIMIT优化查询相关问题咨询

以下针对你提出的三个问题逐一解答：

问题1：性能提升的原因是否是查询找到匹配记录后就立即终止，无需扫描全部100个分区？ #

这个是性能提升的直接表现，但核心诱因是LIMIT 1修正了优化器的执行计划选择。
PostgreSQL 14的哈希分区逻辑本身支持分区裁剪：只要WHERE条件里携带了分区键user_id的等值查询，理论上不管加不加LIMIT都只需要扫描对应哈希值的1个分区，不需要碰剩下99个分区。
你不带LIMIT时查询慢，是因为你给user_id和file_id分别建了独立的单值索引，优化器做行数估算时出现偏差，误以为走file_id的本地索引效率更高，最终触发了全100个分区的file_id索引扫描，所以耗时达到200ms。
加了LIMIT 1之后，优化器的代价计算逻辑会调整，明确知道只要拿到1条匹配记录就可以终止执行，因此会优先选择分区裁剪逻辑，直接定位到user_id对应的单个分区，在该分区内查file_id的索引找到匹配行就直接返回，自然速度提升明显。

问题2：这种场景下使用LIMIT是否属于不好的开发实践？ #

不属于，反而属于贴合业务逻辑的合理优化。
你已经明确知道user_id+file_id的组合最多只有1条记录，加LIMIT 1相当于把业务侧的唯一性约束主动告知数据库，一方面可以帮助优化器做出更正确的执行计划选择，另一方面也可以避免极端情况下（比如数据写入异常出现重复记录）返回多条数据导致上层业务报错，反而提升了代码的健壮性。
唯一需要注意的是，可以在SQL旁加注释说明加LIMIT 1的业务背景，避免后续维护人员不了解逻辑随意删除。

问题3：是否需要将file_id和user_id设置为联合主键？ #

非常推荐，这个操作可以从根源上解决你现在遇到的执行计划不稳定的问题，还能提升数据可靠性。
首先PostgreSQL的分区表强制要求主键必须包含分区键，你要创建主键本来就必须把user_id加入主键字段，刚好user_id+file_id的组合符合你的业务唯一性约束，完全满足要求。
其次创建联合主键后，数据库会自动生成对应联合唯一索引，后续你再执行user_id+file_id的等值查询时，优化器可以100%选择分区裁剪+唯一索引扫描的执行路径，哪怕你不加LIMIT 1也能稳定跑出20ms以内的速度，比现在靠LIMIT 1临时修正执行计划的方案要稳定得多。
另外联合主键自带的唯一约束还能从数据库层面拦截重复的user_id+file_id记录写入，从根源上避免脏数据产生。

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