通过使用100万条数据来测试字段的多少是否会影响查询的速度

create database test;

drop  table  if exists small_field_data;
create table small_field_data
(
    id          bigint not null,
    project_id  bigint,
    model_id    bigint,
    floor       smallint,
    top         double precision,
    bottom      double precision,
    create_data timestamp default NULL::timestamp without time zone
);
-- 创建模拟数据
start transaction;
insert into small_field_data select generate_series(1,100*10000),round(random()*1000000),random()*10,
                                    random()*1000,random()*10.11,random()*10.12;
commit;
select floor from small_field_data where project_id = 20;
select distinct(floor) from small_field_data;

drop  table  if exists great_field_data;
create table great_field_data
(
    id          bigint not null,
    project_id  bigint,
    model_id    bigint,
    floor       smallint,
    top         double precision,
    bottom      double precision,
    dbid        bigint,
    name        text,
    specialty   text,
    create_data timestamp default NULL::timestamp without time zone
);
-- 创建模拟数据
start transaction;
insert into great_field_data select generate_series(1,100*10000),round(random()*1000000),random()*10,
                                    random()*1000,random()*10.11,random()*10.12,random()*11,md5(random()::text),md5(random()::text);
commit;
select floor from great_field_data where project_id = 30;
select distinct(floor) from great_field_data;

-- 不太好进行具体的测试，外界影响因素太多（其他程序占用资源，系统占用资源，程序卡顿等等）
-- 多次测试结果如下(ms)：
-- on   small_field_data               great_field_data
-- sql   1           2                  1            2
-- 0    126         230                139          238
-- 1    130         230                134          238
-- 2    129         210                123          268
-- 3    125         230                125          238
-- 4    162         235                129          227
-- 5    116         271                133          230
-- 6    132         237                124          229
-- 7    120         230                124          258
-- 8    140         216                122          236
-- 9    118         229                132          225
-- 通过上方数据可看出,字段少的时候略快于字段多的时候,但现在的电脑配置都很给力,差异并不是很明显.