穆罕默德·阿里：理解PostgreSQL中的哈希聚合和哈希连接
内容提要
PostgreSQL使用哈希连接和哈希聚合提高查询效率。哈希聚合通过创建哈希表进行分组和计算，哈希连接通过创建哈希表在连接操作中查找匹配的行。哈希连接比嵌套循环连接和合并连接更高效。内存使用可以通过调整参数控制，如果哈希表无法放入内存，系统会使用临时文件存储数据。哈希连接适用于整数和字符串数据类型，但不均匀的数据分布可能影响效率。查询中包含不必要的列会增加哈希表的大小和内存需求。
延伸解读
哈希连接与哈希聚合的适用场景
哈希连接和哈希聚合并非万能。哈希连接在等值连接且数据集较大时通常优于嵌套循环连接，尤其当哈希表能放入内存时。哈希聚合则适用于包含SUM、COUNT、AVG等聚合函数且带GROUP BY的查询。但两者都受数据分布影响：若连接键或分组键存在严重倾斜，大量相同值会落入同一桶，导致该桶处理耗时增加，整体效率下降。
内存参数调优的权衡
work_mem和hash_mem_multiplier共同决定哈希操作可用内存，公式为work_mem * hash_mem_multiplier。PostgreSQL 15将后者默认值从1.0提高到2.0，使哈希操作可用内存翻倍。但调高参数需谨慎：每个哈希节点都会占用相应内存，若查询含多个哈希操作，总内存可能成倍增长，挤压其他查询资源。此外，temp_file_limit限制临时文件总空间，默认-1表示不限制，但过度使用磁盘会拖慢性能。
从执行计划判断哈希表是否溢出
通过EXPLAIN ANALYZE可观察哈希连接的实际内存使用。若输出中Batches为1，说明哈希表完全在内存中构建；若出现多个批次，则表明work_mem或hash_mem_multiplier不足，数据被分批写入临时文件处理。此时可考虑适当调高参数，但需结合系统整体内存评估。使用EXPLAIN (ANALYZE, BUFFERS)还能查看临时文件的读写统计，帮助定位磁盘I/O瓶颈。
统计信息与查询设计的影响
查询规划器依赖统计信息选择哈希连接或哈希聚合。确保autoanalyze参数合理或手动运行ANALYZE，可帮助规划器准确估计行数和数据分布，避免误选算法。此外，查询中应避免包含不必要的列，因为哈希表会存储连接键和所需列，多余列会增大哈希表体积和内存需求，甚至导致本可内存容纳的哈希表溢出到磁盘。
Q&A
PostgreSQL中的哈希聚合是如何工作的？
哈希聚合通过创建哈希表来分组和计算聚合值。当查询包含GROUP BY和SUM、COUNT或AVG等聚合函数时，查询规划器可能使用哈希聚合。PostgreSQL遍历每一行，对GROUP BY键进行哈希，并将行放入对应的桶中。处理完所有行后，使用哈希表中的数据计算每个组的最终聚合结果。
哈希连接在什么情况下比嵌套循环连接更高效？
哈希连接在处理大数据集时通常比嵌套循环连接更高效，因为它消除了重复扫描整个数据集的需要。此外，当数据未排序且哈希表能放入内存时，哈希连接也比合并连接更高效。
如何控制PostgreSQL哈希操作的内存使用？
哈希操作的内存使用由work_mem和hash_mem_multiplier参数控制。work_mem指定用于排序和哈希表的内存，增加work_mem可以创建更大的哈希表。hash_mem_multiplier计算哈希操作允许使用的最大内存，公式为：总内存 = work_mem * hash_mem_multiplier。此外，temp_file_limit限制临时文件使用的磁盘空间。
当哈希表无法放入内存时，PostgreSQL如何处理？
PostgreSQL将数据分成多个批次（批次数量总是2的幂），每个批次使用临时文件存储无法放入内存的数据。处理时，先为第一批次构建哈希表，属于该批次的行保留在内存中，其他行写入临时文件。然后扫描外部表，匹配第一批次的行，其他行也写入临时文件。处理完第一批次后，清除哈希表，从临时文件加载下一批次，重复此过程直到所有批次处理完毕。
哈希连接适用于哪些数据类型？有哪些限制？
哈希连接特别适用于整数和字符串数据类型。然而，不均匀的数据分布可能影响哈希连接的效率，例如大量行具有相同的哈希值会导致某个桶处理时间过长。此外，查询中包含不必要的列会增加哈希表的大小和内存需求。
如何查看哈希连接执行计划中的内存使用情况？
在EXPLAIN输出中，哈希连接节点会显示Buckets、Batches和Memory Usage等信息。例如“Buckets: 1024 Batches: 1 Memory Usage: 9kB”表示使用了1024个桶、1个批次，内存使用9kB。如果Batches大于1，说明哈希表未能完全放入内存，可能需要增加work_mem或hash_mem_multiplier。使用EXPLAIN (BUFFERS)可以查看溢出文件的详细信息。
设置过高的work_mem和hash_mem_multiplier有什么风险？
设置过高的work_mem和hash_mem_multiplier可能导致内存使用过高，因为该设置应用于每个节点。例如，一个查询中有3个哈希操作，将占用3 * work_mem * hash_mem_multiplier的总内存。这可能导致其他查询内存不足。
