散列分区使用

• 复制
  复制成功

postgres=# create table t_hash_partition(f1 int,f2 int) partition by hash(f2);
create table t_hash_partition_1 partition of t_hash_partition FOR VALUES WITH(MODULUS 4, REMAINDER 0);
create table t_hash_partition_2 partition of t_hash_partition FOR VALUES WITH(MODULUS 4, REMAINDER 1);
create table t_hash_partition_3 partition of t_hash_partition FOR VALUES WITH(MODULUS 4, REMAINDER 2);
create table t_hash_partition_4 partition of t_hash_partition FOR VALUES WITH(MODULUS 4, REMAINDER 3);

#上面创建4个子分区的hash表，hash分区表需要指定分区的个数，因为使用分区数做为算子来计算每条数据所在分区，所以目前hash分区不支持删除添加和删除。

postgres=# insert into t_hash_partition values(1,1),(2,2),(3,3);
COPY 3
postgres=# select * from t_hash_partition;
 f1 | f2 
----+----
  1 |  1
  3 |  3
  2 |  2
(3 rows)

#TDSQL pg自动根据分区值进行剪枝

postgres=# explain select * from t_hash_partition where f2=2;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Append  (cost=0.00..26.88 rows=7 width=8)
         ->  Seq Scan on t_hash_partition_3  (cost=0.00..26.88 rows=7 width=8)
               Filter: (f2 = 2)
(5 rows)