日期:2014-05-16  浏览次数:20416 次

测试多列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