分区表 - 墨天轮问答

分区表

我来答

2023-01-10

分区表

大哥们请教个问题
create table hash_partition_table (
id serial primary key,
testnum serial)partition by hash(testnum)(
partition p1,
partition p2
);
我用分区hash的方式建个分区表，用hash键查询的时候，不指定分区，这时候db会把所有分区扫一遍，还是能直接查到hash后的分区啊。

我来答

添加附件

问题补充

3条回答

默认

最新

卿似人间烟火

肯定是先扫一遍，查询到所有的已经创建过的

有用 0

lll

EXPLAIN PERFORMANCE select *  from hash_partition_table PARTITION (p2) where testnum = 1989;
                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..8.27 rows=1 width=8) (actual time=0.029..0.029 rows=0 loops=1)
   Output: id, testnum
   (CPU: ex c/r=0, ex row=0, ex cyc=1464, inc cyc=1423844319799774)
   Iterations: 1
   ->  Partitioned Index Scan using idx_hash_partition_table_p1 on public.hash_partition_table  (cost=0.00..8.27 rows=1 width=8) (actual time=0.018..0.018 rows=0 loops=1)
         Output: id, testnum
         Index Cond: (hash_partition_table.testnum = 1989)
         (Buffers: shared hit=4)
         (CPU: ex c/r=0, ex row=0, ex cyc=1423844319798310, inc cyc=1423844319798310)
         Selected Partitions:  1
 Total runtime: 0.143 ms
(11 rows)

EXPLAIN PERFORMANCE select *  from hash_partition_table PARTITION (p1) where testnum = 1989;
                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..8.27 rows=1 width=8) (actual time=0.041..0.043 rows=1 loops=1)
   Output: id, testnum
   (CPU: ex c/r=2376, ex row=1, ex cyc=2376, inc cyc=2847937465612016)
   Iterations: 1
   ->  Partitioned Index Scan using idx_hash_partition_table_p1 on public.hash_partition_table  (cost=0.00..8.27 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=1)
         Output: id, testnum
         Index Cond: (hash_partition_table.testnum = 1989)
         (Buffers: shared hit=5)
         (CPU: ex c/r=2847937465609640, ex row=1, ex cyc=2847937465609640, inc cyc=2847937465609640)
         Selected Partitions:  2
 Total runtime: 0.160 ms
(11 rows)

还真是，不指定就平扫，hash也是如此。

有用 1

卿似人间烟火

要不要试试这个show tables；【狗头】

有用 0

回答交流

提交

问题信息

请登录之后查看

邀请回答

暂无人订阅该标签，敬请期待~~

墨值悬赏