PostgreSQL性能相关的必知SQL

创建索引

索引的类型,有B-tree, Hash, GiST, SP-GiST和GIN。对于这些type,使用如下语句创建索引:

执行计划

使用EXPLAIN命令来显示SQL的执行计划,在pg中,EXPLAIN的命令格式如下:

option可以为ANALYZE/VERBOSE/COSTS/BUFFERS/FORMAT

若使用ANALYZE,SQL语句会真的被执行,因此可以看到实际花费的时间,以及获得的行数。所以如果不希望真的修改数据,需要记得加上ROLLBACK。FORMAT则制定输出格式,支持TEXT/XML/JSON/YAML。

理解cost

EXPLAIN输出结果中的cost,其中有两个值,中间用..隔开,第一个数字是启动成本,即返回第一行需要的成本,第二个数字表示返回所有数据的成本。而程本cost描述了执行一个SQL的代价是多少,默认情况下不同的操作所带来不同的cost值:

  • 顺序扫描一个数据块,cost为1
  • 随机扫描一个数据块,cost为4
  • 处理一个数据行的cpu,cost为0.01
  • 处理一个索引列的cpu,cost为0.005
  • 每个操作符的cpu,cost为0.0025

下图是加上ANALYZE后的一个示例:

可以看出多了actual time等信息。

查看数据库占用磁盘空间

最简单的写法

然而看bytes数是要命的事情,所以大象提供了人性化的函数pg_size_pretty( ),将bytes转换为human-readable的单位(KB/MB或GB),而且是自动根据大小来判断使用哪种单位。

同样的方法,适用于获取表的磁盘占用量pg_relation_size( ),pg_total_relation_size( )等。

考虑使用copy来迁移数据表

大表的数据导出和导入单表的时候,COPY语句效率相对较高,例如常把数据表导出到csv文件,然后从csv文件导入为另一张表,写法如下:

尽管缺点是显而易见的(只拷贝了table,且from时必须直接插入到表中),但是在大数据量的情况下,copy的性能是明显优于insert或者类似PgJDBC的\(executeBatch()\)。另,根据2ndQuadrant上这篇文章中所描述的测试结果,copy在各类延迟下的综合性能而言是非常不错的选择:

drivers例如libpq、PgJDBC、psycopg2等都支持copy。pg_dump也用到了copy,另外同时可以尝试的导入导出工具包括pg_bulkload和barman等。