greenplum 列模式与行模式对比

--建表
CREATE TABLE ds_chn_prd_serv_col_201108
(
...
)
WITH (APPENDONLY=true, COMPRESSTYPE=quicklz, ORIENTATION=COLUMN,
  OIDS=FALSE
)
DISTRIBUTED BY (serv_id);

CREATE TABLE ds_chn_prd_serv_row_201108
(
...
)
WITH (APPENDONLY=true, COMPRESSTYPE=quicklz, ORIENTATION=ROW,
  OIDS=FALSE
)
DISTRIBUTED BY (serv_id);

--数据准备

insert into ds_chn_prd_serv_col_201108 select * from ds_chn_prd_serv_201108;
insert into ds_chn_prd_serv_row_201108 select * from ds_chn_prd_serv_201108;

--记录条数 20363699

--列模式测试
--3549   3892 3785
select * from ds_chn_prd_serv_col_201108  where acc_nbr in ('13388948142','13399826222');
--4248 4793 3979
select * from ds_chn_prd_serv_col_201108  where serv_id in (20001106,20001162);

--574  582 573
select user_name from ds_chn_prd_serv_col_201108  where acc_nbr in ('13388948142','13399826222');
--471 735 745
select user_name from ds_chn_prd_serv_col_201108  where serv_id in (20001106,20001162);

--702 594
select count(*),max(serv_id) from ds_chn_prd_serv_col_201108 ;

----行模式测试
--3869 3306 3120
select * from ds_chn_prd_serv_row_201108  where acc_nbr in ('13388948142','13399826222');
--3639 3440 4066
select * from ds_chn_prd_serv_row_201108  where serv_id in (20001106,20001162);

--3210 3205 3298
select user_name from ds_chn_prd_serv_row_201108  where acc_nbr in ('13388948142','13399826222');
--4046 3582 2895
select user_name from ds_chn_prd_serv_row_201108  where serv_id in (20001106,20001162);

--4120 3335
select count(*),max(serv_id) from ds_chn_prd_serv_row_201108 ;

--总结
主机配置：....
总记录数 20363699
       row   col
全字段查询     3.7   3.7
部分字段查询(选取一个字段) 3.2   0.55
汇总查询     3.6   0.55

--结论
 列模式适合宽表设计(多字段表),在查询时只查询部分字段的情况，效率高于行模式
 
 
-表清理
drop table ds_chn_prd_serv_row_201108;
drop table ds_chn_prd_serv_col_201108;