postgresql index使用后速率提升变化

此文章示例仅为最简单示例，测试结果仅供参考。

测试环境：

数据库：pg12

操作系统及配置：centos7(虚拟机)，虚拟内存1GB

1.插入模拟数据

以下为一些基础函数的说明：

--生成1-10000的数字，step为1
select generate_series(1,10000);

--随机字母 
select  chr(int4(random()*26)+65);

--生成三位随机字母
select rpad(chr(int4(random()*26)+65),3,chr(int4(random()*26)+65));

--随机数字 十位不超过6的两位数
select  (random()*(6^2))::integer;

--三位数
select  (random()*(10^3))::integer;

创建表，插入模拟数据：

--创建测试表
create table t_test(
	id integer, 
	name character varying, 
	age integer, 
	address character varying,
	birthday  date,
	salary integer,
	logintime timestamp);

--随机插入一万行数据
insert into t_test 
	SELECT generate_series(1,1000000),
	rpad(chr(int4(random()*26)+65),3,chr(int4(random()*26)+65)), 
	(random()*(6^2))::integer,
	null,
	now() - ((random()*1000)::integer||' day')::interval as birthday,
	(random()*(10^4))::integer,
	clock_timestamp() as logintime;

2.对比普通查询与加索引查询所用的时间

--10000条数据不加索引直接查询
qianbase=# explain analyze select  * from t_test where id=27;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Seq Scan on t_test  (cost=0.00..199.00 rows=1 width=60) (actual time=0.013..0.835 rows=1 loops=1)
   Filter: (id = 27)
   Rows Removed by Filter: 9999
 Planning Time: 0.043 ms
 Execution Time: 0.849 ms
(5 rows)


--增加索引（b-tree），查询
create index index_test on t_test(id);
qianbase=# explain analyze select  * from t_test where id=27;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Index Scan using index_test on t_test  (cost=0.29..8.30 rows=1 width=60) (actual time=0.014..0.015 rows=1 loops=1)
   Index Cond: (id = 27)
 Planning Time: 0.059 ms
 Execution Time: 0.030 ms
(4 rows)

3.对比数据量不同是，查询时间的变化

数据量（行）	普通查询（ms）	加索引查询(ms)
1	0.023	0.023
10	0.022	0.022
100	0.035	0.025
1000	0.094	0.028
10000	0.849	0.030
100000	7.096	0.030
1000000	86.271	0.030

折线统计图分析：