PostgreSQL并行创建索引

PostgreSQL参数探究

最近在测试postgreSQL和Mysql之间的性能，发现无论是批量插入还是联合查询，PostgreSQL的性能都差着Mysql一大截。
怀疑是PostgreSQL的参数设定导致的性能限制，逐步的进行一些测试和总结
有时间的话接着测

PostgreSQL并行创建索引

(一)    通过调整postgreSQL的参数来启用多核并行创建索引

在postgreSQL11之后的版本中，新增了并行创建索引的功能，能够提供多核索引创建功能。同时postgreSQL11也支持了经典的存储过程，我们可以通过存储过程来对并行创建索引来进行测试。

• 在postgreSQL中创建数据表，并在其中使用存储过程插入大量的数据
  下面的操作使用存储结构向表test_demo中插入了5亿条数据

create table test_demo (test_data numeric);

create or replace procedure insert_data(backets integer)
language plpgsql
as $$
	declare
		i int;
	begin
		i := 0;
		while i < buckets
		loop
			insert into test_demo select random() from generate_series(1,1000000);
			i := i+1;
			raise notice 'inserted % buckets' , i;
			commit;
		end loop;
		return;
	end;
$$;
call insert_data(500);

• 使用参数设置并行创建索引的核数
  在postgreSQL中，默认情况下启用并行索引创建。负责此行为的参数为max_parallel_maintenance_workers，可以使用下面的sql指令设置或者查看
  • SHOW max_parallel_maintenance_workers，用于查看当前的参数，默认值一般为2，表明可以启动两个工作程序来帮助创建索引。
  • SET max_parallel_maintenance_workers TO 0，用于将该参数设置为0，这表明在创建索引时将没有多核索引可用。
• 在仅使用一个cpu内核的情况下创建索引
  将该参数设置为0后，启动创建索引，并且记录用时

set max_parallel_maintenance_workers to 0;
create index idx1 on test_demo(test_data);

• 在使用两个cpu内核的情况下创建索引
  将参数设置为2后进行索引创建（这里可以将参数设置为更高的核数，但是设置超过2不会有更大的意义。postgreSQL仍然仅会使用两个内核）

set max_parallel_maintenance_workers to 2;
create index idx2 on test_demo(test_data);

• 在已有索引的情况下通过调整该参数能否优化数据插入
  在已经创建索引的情况下该参数是否能够在优化索引创建得到同时优化数据插入？使用指令将max_parallel_maintenance_workers分别设置为0和2，前后插入一千万条数据来查看插入的用时。发现该参数并没有对数据插入产生明显的影响。
  
  

（二）    通过调整参数来使用更多的内存创建索引

• 在索引创建的过程中，cpu内核并不是唯一的限制因素，内存也同样非常重要。maintenance_work_mem默认设置为非常低的值(64MB)，这会极大地限制可以在内存中排序的数据量。因此可以将其设置为更高的值来创建索引。可以看到，使用更多的内存空间时创建索引的速度提升非常明显。

SHOW maintenance_work_mem;--查看当前数据库的内存参数

SET maintenance_work_mem TO '4GB';

CREATE INDEX 
idx4 ON test_demo (test_data);

• 在已有索引的情况下调整内存能否优化数据插入
  在已有索引的情况下通过调整内存来优化数据插入。通过插入一千万条数据来查看该参数的效果。发现该参数并没有对数据插入产生明显的影响。
  
  

（三）    通过调整checkpoint和io来优化索引创建

由于目前测试的postgreSQL部署在腾讯云服务器上，所以不能对checkpoint_timeout等参数进行配置，因此将测试数据库转移到我自己的本机上。主要是为了测试配置这些参数对创建索引的优化影响。
经过一系列“严密的测试”，我还是没得出这个checkpoint索引创建的系列最优参数。因此只能搁置一段事件。

参考博客见PostgreSQL，并行创建索引以提高性能。