PostgreSQL性能相关的基础要点

事务化处理

对数据库的修改,尤其是直接改变数据或数据结构,尽量使用事务块来完成。关于事务,PostgreSQL默认把每个SQL语句放在一个事务中执行,相当于被自动加上了begin和commit包围。事务当然可以全部回滚,但是在包围内可以定义savepoint,并且可以用rollback to回滚到指定的savepoint。

正确使用索引

优化的首要方式,还是要先处理好索引。虽然索引都是有成本的,但该尝试的时候还是该果断尝试。PG支持的几个主要索引方式:

  1. B-Tree,create index默认使用的是B-Tree,显然就是个大众脸,对于大多数情况,已经能有比较好的表现。
  2. Hash索引在判断相等时生效。然而由于对并不是事务安全的,所以需要在崩溃后手动重建,通常认为使用场景并不多。
  3. GIN(Generalized Inverted Indexes)在需要将许多值映射到同一行的的时候比较有用,例如为数组值建索引,或者实现全文索引等。相比之下,B-Tree对一行只有一个值的情况下比较有用。
  4. GiST(Generalized Search Tree)用于构建广义均衡树结构,可以用于等值和范围比较以外的更多类型操作,例如几何数据类型,以及全文检索等。最大的价值应该在于它的扩展性,让大家可以据此为新的数据类型实现新的索引方案。
  5. SP-GiST,分区广义搜索树,也是用来实现新的索引方案,但提供的是分区搜索树。适用于不同类型的不平衡数据集的快速查找,前提是这些数据集使用与查询相匹配的规则进行了分区。
  6. BRIN,为处理大表而生,尤其是这些表里的行的自然排序与某列的值相关。例如日志表,行顺序写入与时间戳相关,而时间戳又是记录在某列字段里,这种情况下,在时间戳字段上使用BRIN索引后,根据时间戳查询行数据时,可以避免大量表查找,而对应的开销很小。

另外有一些特别之处,也许有用:

  1. 局部索引,在create index的时候可以写where语句,这样的索引可以按需进行,也可以比较小。
  2. 表达式索引,可以直接对需索引字段执行表达式操作。
  3. 多列索引,借助bitmap index scan,pg可以在多列查询的时候,将多个单列索引结合起来使用。所以对每个字段建单列索引也是可以的。多列索引在某种特定查询方式下可以提供更强大的性能,但只能在严格按索引顺序来引用索引中的列的前提下,才能生效。多列索引(a,b)可以对where a=x and b=y的查询,以及where a=x的查询生效,但对仅有where b=y的查询无效,而且字段a的单列索引会冗余失效。

参数开关配置

索引的使用是有代价的,也并非总能生效,一来pg在取行数据的时候,依然是要去表里取数据。因此默认情况下,pg自己在每个查询之前纠结地做着选择,因此在某些情况下,例如表并不很大时,或者需要获取表中重要部分的数据时,pg有可能依旧会选择顺序搜索,而忽略已经建好的索引。

如果想简单粗暴地让pg执行你的索引,可以按需修改配置文件,例如如下修改强制打开和关闭了我希望和不希望执行的查找方式: