转载

PostgreSQL性能相关的必知SQL

创建索引

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

CREATEINDEXnameONtableUSINGtype (column)

执行计划

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

EXPLAIN [(option [, ...])] statement
EXPLAIN [ANALYZE] [VERBOSE] statement

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等信息。

查看数据库占用磁盘空间

最简单的写法

SELECTpg_database_size(current_database());

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

SELECTpg_size_pretty(pg_database_size(current_database()));

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

考虑使用copy来迁移数据表

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

COPYtable_name | queryTO filename.csvWITHCSVHEADER ;

COPYtable_nameFROMfilename.csvCSVHEADER

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

inserting 1000000 rowsbatched, unbatchedand withCOPY
batchinsertelapsed:      23.715315s
sequentialinsertelapsed: 36.150162s
COPYelapsed:              1.743593s
Done.

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

原文 http://qiancy.com/2017/02/04/postgresql-performance-sql/

正文到此结束