如何知道 Postgres 哈希分区将使用哪个分区? [英] How to know which partition will be used in Postgres hash partitioning?

问题描述

有没有办法知道哪个分区将用于由具有文本值的列分区的表中的新行?

Is there a way to know which partition will be used for a new row in a table partitioned by a column with text values?

用例是:根据文本列，我有一个包含许多分区的大表.我需要向该表插入更新数百万行新行.如果我知道每一行将使用哪些分区，我可以重新安排我的批量插入更新以一次只在 1 个分区上工作.

Use case is: I have a big table with many partitions according to a text column. I need to insert-update millions of new rows to this table. If I know which partitions will be used for each row, I can rearrange my bulk inserts-updates to only work on 1 partition at a time.

->hashtext 函数已经过测试，但没有给出正确的分区值.

-> hashtext function is tested and it is not giving the correct partition value.

推荐答案

逆向工程代码，可以通过如下语句得到分区号:

Reverse engineering the code, you can get the partition number with the following statement:

SELECT (hashtextextended('value', 8816678312871386365)::numeric + 5305509591434766563) % 8;

将 8 替换为分区数，将 'value' 替换为相关字符串.

Replace 8 with the number of partitions and 'value' with the string in question.

您可以使用satisfies_hash_partition 测试分区号.要测试 'value' 是否会出现在表 tab 中的第 6 个分区中，您可以运行

You can test the partition number with satisfies_hash_partition. To test if 'value' would end up in partition 6 of 8 in table tab, you can run

SELECT satisfies_hash_partition('tab'::regclass, 8, 6, 'value'::text);

这篇关于如何知道 Postgres 哈希分区将使用哪个分区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！