postgresql大数据查询加索引和不加索引耗时总结

1、创建测试表

CREATE TABLE big_data
(
  id character varying(50) NOT NULL,
  name character varying(50),
  datetime timestamp with time zone,
  CONSTRAINT big_data_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE big_data
  OWNER TO postgres;

2、创建插入数据函数

CREATE OR REPLACE FUNCTION insert_bigdata()
  RETURNS text AS
$BODY$
declare ii integer;
declare jj integer;
begin

    ii = 1;
    jj = 1;
    FOR ii IN 1..10 LOOP
        FOR jj IN 1..10000 LOOP
            INSERT INTO big_data values(uuid_generate_v4(), 'lisi'||jj, now());
        END LOOP;
    END LOOP;
    RETURN 'success';
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION insert_bigdata()
  OWNER TO postgres; 

3、插入一千万条数据（修改函数中的循环次数，多执行几次，插入需要的数据）

select insert_bigdata();

4、给name字段不加索引和加索引分别统计执行时间

查询结果10条记录

select * from big_data where name='lisi10';

查询结果100条记录
select * from big_data where name='lisi100';

查询结果1000条记录
select * from big_data where name='lisi1000';

查询结果10000条记录
select * from big_data where name='lisi10000';

查询结果100000条记录
select * from big_data where name='lisi100000';

耗时统计表（单位/毫秒）

耗时统计图

5、结果总结

在查询结果小于1000条记录时，加索引会大幅度提高查询效率。

在查询结果大于1000条记录时，加索引对查询效率的提升逐渐减小，尤其是超过10000条时，使用索引后的查询时间也比较长。

当前结果仅适用于创建的big_data这张数据表（如果数据表中字段比较多，数据量比较大，会在更小的查询结果记录数出现加索引查询效率提升不明显的问题）。