测试多列DK的选择性问题
为了测试多个DK分布键情况下,多表关联是否可以镜像segment过滤,测试如下:
[gpadmin@gtlions50 ~]$ psql gtlions
psql (8.2.15)
Type "help" for help.
gtlions=# create table gtt1(id int,name character varying(5)) distributed by (id,name);
CREATE TABLE
gtlions=# create table gtt2(id int,name character varying(5)) distributed by (id,name);
CREATE TABLE
gtlions=# insert into gtt1 values(1,'a');
INSERT 0 1
gtlions=# insert into gtt1 values(11,'b');
INSERT 0 1
gtlions=# select gp_segment_id,* from gtt1 order by 1;
gp_segment_id | id | name
---------------+----+------
2 | 11 | b
3 | 1 | a
(2 rows)
gtlions=# insert into gtt1 values(111,'c');
INSERT 0 1
gtlions=# insert into gtt1 values(1111,'d');
INSERT 0 1
gtlions=# select gp_segment_id,* from gtt1 order by 1;
gp_segment_id | id | name
---------------+------+------
0 | 1111 | d
2 | 11 | b
3 | 111 | c
3 | 1 | a
(4 rows)
gtlions=# insert into gtt2 select * from gtt1;
INSERT 0 4
gtlions=# explain analyze select * from gtt1 where id=11;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Gather Motion 4:1 (slice1; segments: 4) (cost=0.00..1.01 rows=1 width=6)
Rows out: 1 rows at destination with 3.593 ms to first row, 4.067 ms to end, start offset by 28 ms.
-> Seq Scan on gtt1 (cost=0.00..1.01 rows=1 width=6)
Filter: id = 11
Rows out: 1 rows (seg2) with 0.159 ms to first row, 0.160 ms to end, start offset by 359 ms.
Slice statistics:
(slice0) Executor memory: 139K bytes.
(slice1) Executor memory: 157K bytes avg x 4 workers, 157K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Total runtime: 32.320 ms
(11 rows)
gtlions=# explain analyze select * from gtt1 where name='b';
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Gather Motion 4:1 (slice1; segments: 4) (cost=0.00..1.01 rows=1 width=6)
Rows out: 1 rows at destination with 3.537 ms to first row, 3.538 ms to end, start offset by 31 ms.
-> Seq Scan on gtt1 (cost=0.00..1.01 rows=1 width=6)
Filter: name::text = 'b'::text
Rows out: 1 rows (seg2) with 0.231 ms to first row, 0.233 ms to end, start offset by -250 ms.
Slice statistics:
(slice0) Executor memory: 139K bytes.
(slice1) Executor memory: 157K byt