分析postgresql中性能优化问题

         最近利用postgresql 进行空间数据库管理，对postgresql的使用较多，由于数据量很大在建表时候一直想优化生成的速度，下面是我做关于数据库不同操作的性能上的差异。

         首先谈下插入操作，最简单的是直接insert，接着是多行插入，然后还有建立索引和不建立索引的几种方式。

插入操作性能统计表

	单行插入	多行插入（1000行）	建立索引（id）	建立索引（id,vxyz）
消耗时间（s）	4.1	1.2	1.4	2.5

 

        由此可以看出

        （1）  多行插入尤其是对于大数据量插入时候在效率上有更大的提升。

        （2）  建立索引尽管会降低插入速度，但是影响不大

 

         再谈下更新操作，这里的更新操作不是单纯的更新，而是包含了拓扑关系的构建， 也就是涉及到表的查询。最直接的是根据查询结果进行单步更新，然后更复杂的是利用with语句进行聚合，在表查询可以利用到插入阶段建立的索引来提升性能。根据这几种情况做了以下的表。

        

 

	单步更新	使用with语句聚合（1000行）	使用索引（id btree）	使用索引（id vxyz btree）
消耗时间（s）	30s	15	5.9	2

 

         更新操作其实基本上和插入操作类似，当然由于建立了索引使得邻域查询的速度大大提高。

         最后考虑多线程，在多核心系统中利用多核可以极大的提升处理速度，充分利用闲置的CPU资源。

消耗时间(s)\操作项	只插入	只建立拓扑	插入和拓扑	核心数
单线程	48	95	142.6	1
多线程	39	61.803	90.55	2
多线程	27	47.7436	74	4
多线程	24	37.4702	59.4195	7

 

         多线程可以提升整体的性能，当然它达不到1+1 = 2 的效果，而且随着核心数提升，性能提升在下降，这可能是数据库的并发控制造成的。但是std::thread::hardware_concurrency - 1 个线程是发挥性能的最佳选择、

总结：多线程，sql 语句聚合，建立索引可以提高性能。

反思： 在多线程多连接创建的pgsql函数不能重名的，即使两个函数的内容是一致的。sql不支持动态表。

提升：性能上应该还有两点优化，利用不同的索引，利用plv8代替plpgsql函数（据说性能可以提高好几倍）