测试 Postgres 表分区的 HASH 函数 [英] Test HASH function for Postgres table partitioning

问题描述

我正在使用 Postgres 11，并且想在主键是 UUID 的表上使用哈希分区.我知道我需要预先选择多个分区，并且主键上的哈希函数的模数将用于为每个分区分配行.

像这样:

CREATE TABLE new_table (id uuid) PARTITION BY HASH (id);CREATE TABLE new_table_0 PARTITION OF new_table FOR VALUES WITH (MODULUS 3, REMAINDER 0);CREATE TABLE new_table_1 PARTITION OF new_table FOR VALUES WITH (MODULUS 3, REMAINDER 1);CREATE TABLE new_table_2 PARTITION OF new_table FOR VALUES WITH (MODULUS 3, REMAINDER 2);

文档 提到分区键的哈希值"但没有具体说明散列是如何发生的.我想针对我现有的数据测试这个哈希函数，以查看不同分区数量的分布模式.像这样:

SELECT unknown_partition_hash_function(id) AS hash_value, COUNT(id) AS number_of_recordsFROM existing_table按 1 分组

有没有办法在 SELECT 语句中使用这个哈希函数?

解决方案

应该使用 hash_any.它似乎没有以任何可直接访问的方式公开.

hash_any

I'm using Postgres 11 and would like to use a Hash Partitioning on a table where the primary key is a UUID. I understand I need to select a number of partitions up front, and that the modulus of a hash function on the primary key will be used to assign rows to each partition.

Something like this:

CREATE TABLE new_table ( id uuid ) PARTITION BY HASH (id);
CREATE TABLE new_table_0 PARTITION OF new_table FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE new_table_1 PARTITION OF new_table FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE new_table_2 PARTITION OF new_table FOR VALUES WITH (MODULUS 3, REMAINDER 2);

The documentation mentions "the hash value of the partition key" but doesn't specify how that hashing takes place. I'd like to test this hash function against my existing data to see the distribution patterns for different numbers of partitions. Something like this:

SELECT unknown_partition_hash_function(id) AS hash_value, COUNT(id) AS number_of_records
FROM existing_table
GROUP BY 1

Is there a way to use this hash function in a SELECT statement?

解决方案

It should use hash_any. It doesn't seem to be exposed in any way that's directly accessible.

hash_any

这篇关于测试 Postgres 表分区的 HASH 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！