PostgreSQL多列哈希分区原理及自定义函数下分区修剪探究

1. 自定义哈希函数与操作符类 #

PostgreSQL哈希分区依赖哈希函数生成的哈希值分配数据到对应分区，这里我们通过自定义函数和操作符类，替换默认的哈希计算逻辑：

整数类型自定义哈希函数 #

create or replace function part_hashint4_noop(value int4, seed int8)
returns int8 as $$
select value + seed;
$$ language sql immutable;

这个函数简化了哈希计算，直接将输入整数与种子值相加作为哈希结果（默认哈希函数会做复杂散列，此处用简化逻辑方便验证）。

整数类型操作符类 #

create operator class part_test_int4_ops
for type int4
using hash as
operator 1 =,
function 2 part_hashint4_noop(int4, int8);

操作符类绑定了整数的等于操作符和自定义哈希函数，指定哈希分区时使用该函数计算整数列的哈希值。

文本类型自定义哈希函数 #

create or replace function part_hashtext_length(value text, seed int8)
RETURNS int8 AS $$
select length(coalesce(value, ''))::int8
$$ language sql immutable;

该函数忽略文本内容，仅以文本长度作为哈希结果（空值处理为0长度），同样用于简化验证逻辑。

文本类型操作符类 #

create operator class part_test_text_ops
for type text
using hash as
operator 1 =,
function 2 part_hashtext_length(text, int8);

绑定文本的等于操作符和自定义哈希函数，指定分区时用文本长度作为哈希计算依据。

2. 多列哈希分区表创建 #

基于上述操作符类，创建包含两列哈希键的分区表：

begin;
create table hp(a int,b text, c int)
      partition by hash(a part_test_int4_ops, b part_test_text_ops);
create table hp0 partition of hp for values with (modulus 4, remainder 0);      
create table hp1 partition of hp for values with (modulus 4, remainder 1);      
create table hp2 partition of hp for values with (modulus 4, remainder 2);      
create table hp3 partition of hp for values with (modulus 4, remainder 3);      
commit;

这里指定a列（使用自定义整数操作符类）和b列（使用自定义文本操作符类）作为哈希分区键，分4个分区，数据分配规则为最终哈希值 % 4 = 对应余数。

3. 多列哈希分区计算逻辑 #

PostgreSQL多列哈希分区的核心计算流程：

• 对每个分区键，调用绑定的哈希函数计算哈希值（不同列会传入不同种子值，避免哈希冲突）
• 将所有列的哈希值通过内部逻辑（如异或、累加）组合成一个最终哈希值
• 用最终哈希值对分区模数取余，得到的余数对应目标分区

以示例查询为例：

• 查询a = 1 and b = 'xxx'：
  • a列哈希值：1 + 种子值
  • b列哈希值：length('xxx') = 3
  • 组合哈希后取余4得到0，对应hp0分区
• 查询a = 2 and b = 'xxx'：
  • a列哈希值：2 + 种子值
  • b列哈希值：3
  • 组合哈希后取余4得到3，对应hp3分区

4. 分区修剪机制 #

当查询包含所有哈希分区键的等值条件时，PostgreSQL会触发分区修剪：

1. 解析查询中的等值条件，提取每个分区键的具体值
2. 对每个键值调用对应的自定义哈希函数计算哈希值
3. 按多列哈希组合规则计算最终余数
4. 直接定位到对应余数的分区，仅扫描该分区，跳过其他分区

从执行计划可以看到，两次查询分别只扫描了hp0和hp3分区，这就是分区修剪的效果：数据库无需扫描所有分区，仅访问符合条件的目标分区，大幅提升查询效率。

注意：如果查询缺少任意一个哈希分区键的等值条件，数据库无法计算出唯一余数，就只能扫描所有分区，无法触发修剪。

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