调整硬件配置

在大容量的数据库中，适当的硬件配置也是提高性能的一个途径。

1.存储器

目前的电脑内存是大增了，8GB也是主流了，内存增大肯定会比内存小的时候性能要高。但比如数据库是几百 GB的 时候，怎么也不可能只通过内存就可以解决了。数据量大的时候，高速的存储介质也是非常重要的，主要用途不同对存储介质的要求也不一样。
    1)OLAP（联机分析处理）
        行扫描的查询为主体的场合，大量的数据通过 IO流来交换，这个时候IO控制器的性能就比较重要了。增加硬盘的场合RAID 5构成有效果。
　 2)OLTP（联机事务处理）
随机访问为主体的场合，要求seek速度要比较快。 SSD (Solid State Drive)或者RAID 1+0构成较好。而RAID 5插入，更新处理比较慢。Aischool单校方案应属于OLTP，但是由于成本的原因方案中存储做的RAID 5。

2.内存容量

数据只保存在内存的时候速度是很快的。如果一个查询的时候大部分数据都能保存在内存上，只有一小部分没能够保存在内存上，这个时候性能差别是相当大的。内存操作和硬盘操作之间的速度差距一般在 100倍以上。因此我们要求查询等处理的数据尽可能能在内存上解决，或者尽量减少硬盘操作。现在的内存也是容量越来越大了，目前8GB也不是稀罕的事情了，并且价格也不贵。因此大型数据库的时候大容量的内存配置是必须的。 Aischool单校方案由于应用和数据库安装在同一机器上，内存配置在16G或以上。

3.CPU速度

电脑的一个关键性的指标就是 CPU的处理速度。因此在 DBMS中CPU速度快的电脑也是必要的。 PostgreSQL的反应速度和CPU的处理速度一般是成正比的。 CPU性能好的话 ，数据库的总体性能也会提高。Aischool单校方案为2颗物理CPU，每颗4核。

数据库参数调整

PostgreSQL有很多可以设置的系统参数。其中对性能影响较大的几个参数如下。

1.连接数

max_connections：最大连接数。默认是 100个。在大系统中100个是比较少的，一般可能都比100多，但是如果过大的话，内存使用过大（(1800 + 270 * max_locks_per_transaction) * max_connections），导致系统性能反而不高。应用程序设置合适的最小缓冲池，减少和数据库的连接开销，但是各应用程序的最小缓冲池之和要小于max_connections- superuser_reserved_connections
superuser_reserved_connections：预留给超级用户的连接数。
Aischool单校方案max_connections=1000、superuser_reserved_connections=10

2.内存相关参数

shared_buffers：设置数据库服务器内存共享内存缓冲区的使用量 。数据库专用服务器一般设置为物理内存的 20%-40%左右。
  wal_buffers：WAL共享数据存储器使用的内存量。 这个参数要求足够大，如果太小的话，log关联的磁盘操作过频繁，一般繁忙的系统设置为xlog文件段的大小16MB。
   work_mem：默认是1MB，如果发现数据经常使用临时文件排序或group by等， 可以考虑设置为一个比较大的值。按需使用，每个排序或merge JOIN用到的哈希表，DISTINCT，都需要消耗work_mem， 如
果一个执行计划中有多个此类操作则最大需要使用多个work_mem。postgres官方不建议（但是支持）在 postgresql.conf文件中更改work_mem。利用 explain analyze可以检查是否有足够的work_mem，例如：在执行计划中出现了Sort Method: external merge Disk:13696kb，这说明需要从硬盘走13MB的数据，这时我们应该在会话级设置参数work_mem（SET work_mem = '14MB';）有足够的值。
effective_cache_size： 设置用于一个查询的有效规模的计划的假设磁盘缓存大小，参数是告诉数据库，OS的缓存大小。越大，数据库使用索引的积极性就越高。因为数据很可能在OS的缓存里，乱序读取的效率也不差。这个值理论上等于OS可以使用的缓存大小。
maintenance_work_mem：它决定数据库的维护操作使用的内存空间的大小。数据库的维护操作包括VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等操作。按需使用maintenance_work_mem设置的内存, 当有并发的创建索引和autovacuum等操作时可能造成内存消耗过度，这时需要设置参数vacuum_cost_delay（VACUUM操作比较消耗IO，设置延时是指VACUUM操作消
耗的成本大于vacuum_cost_limit后延迟10毫秒再继续执行）。

3.其他参数

checkpoint_segments：多少个xlog rotate后触发checkpoint, checkpoint segments一般设置为大于shared_buffer的SIZE。如shared_buffer=1024MB， wal文件单个16MB，则checkpoint_segments>=1024/16。
random_page_cost：默认4.0，调小后更倾向使用索引，而非全表扫描。
synchronous_commit：关闭XLOG的同步写。可以大大提高写事务的处理能力。不会破坏数据库一致性，但是如果数据库异常DOWN机需要recovery时， 恢复后的数据库可能丢失最后10毫秒(wal_writer_delay)的事务。
wal_writer_delay：它决定写事务日志进程的睡眠时间。WAL进程每次在完成写事务日志的任务后，就会睡眠 wal_writer_delay指定的时间，然后醒来，继续将新产生的事务日志从缓冲区写到WAL文件中。单位是毫秒（millisecond），默认 值是200。
bgwriter_delay：声明后端写进程活跃回合之间的延迟。在每个回合里，写进程都会为一些脏的缓冲区发出写操作。然后它就休眠 bgwriter_delay 毫秒，然后重复动作。缺省值是 200。 请注意在许多系统上，休眠延时的有效分辨率是 10 毫秒。因此，设置 bgwriter_delay 为一个不是 10 的倍数的数值与把它设置为下一个 10 的倍数是一样的效果。此值设置过大，在非常繁忙的系统可能会导致系统IO阻塞。
autovacuum：是否开启自动vacuum、analyze，控制是够打开数据库的自动垃圾收集功能。默认值是on。如果autovacuum被设为on，参数track_counts（参考本章10.9）也要被设为on，自动垃圾收集才能正常工作。注意，即使这个参数被设为off，如果事务ID回绕即将发生，数据库会自动启动一个垃圾收集操作。
Aischool非默认参数设置如下：
max_connections = 1000
superuser_reserved_connections = 10
shared_buffers = 1024MB
maintenance_work_mem = 512MB
max_stack_depth = 6MB
vacuum_cost_delay = 10ms
bgwriter_delay = 10ms
wal_buffers = 16384kB
checkpoint_segments = 128
random_page_cost = 2.0
effective_cache_size = 10240MB
synchronous_commit = off
wal_writer_delay = 10ms
log_line_prefix = '%t:%r:%u@%d:[%p]:'
log_statement = 'ddl'

收集系统统计信息

在数据库应用开发中，速度慢的SQL比比皆是。很多速度很慢都是SQL写的不好，效率不高。
比如无用的去重、无效的条件、不必要的子查询、SQL用不上索引等等。特别是数据量很大的时候，体现越明显。对于不符合要求的那些SQL怎么改善呢？要解决这个速度问题，我们首先最主要的是要找到哪些SQL很慢，或者SQL中的那部分很慢。怎样寻找速度很慢的SQL，我们可以借助系统提供的统计信息功能来查找。这里介绍两种方法：1、修改日志参数，记录超过指定时间的SQL以及当时的执行计划；2、通过pg_stat_statements统计。

1.日志收集

log_min_duration_statement：从log找出执行超过一定时间的SQL。这个参数是设置执行最小多长时间的SQL输出到log，例如输出执行超过3秒的SQL，log_min_duration_statement = 3s。这个参数设置为-1是无效，默认为此值。设置为 0是输出所有的SQL，但这样会增加服务器负担，一般不要设置太低的值。
auto_explain功能：Postgres8.4后增加的功能。默认这个功能不能使用的，需要在postgresql.conf 配置文件中设置以下参数。
shared_preload_libraries = 'auto_explain'
custom_variable_classes = 'auto_explain' #PostgreSQL9.2版本后此参数已取消，不需要设置
auto_explain.log_min_duration = 2s
这样系统在执行的时候如果遇到超过2秒的SQL的话，会自动把执行计划输出到log。这样就直接看log就更加容易找到问题点。

2. pg_stat_statements

pg_stat_statements模块提供了一种方法，用于跟踪所有由服务器执行的SQL语句的执行统计，例如：语句总调用次数、总执行时间、从内存读取的块数、从硬盘读取的块数等等信息。在添加或删除模块pg_stat_statements时，因为它需要额外的共享内存，所以必须重启数据库（在postgresql.conf shared_preload_libraries中配置）。pg_stat_statements 模块加载会消耗部分内存，可以通过  pg_stat_statements.max * track_activity_query_size来计算。这个值是比较小的, 假如 pg_stat_statements.max 值为 10000, track_activity_query_size值为4096， 也就消耗了 40 M内存。
参数配置如下：
shared_preload_libraries = 'pg_stat_statements '
track_activity_query_size = 4096 #SQL文本的最大大小，4K
custom_variable_classes = 'pg_stat_statements ' #PostgreSQL9.2版本后此参数已取消，不需要设置
pg_stat_statements.max = 10000 #跟踪模块中的语句的最大数目
pg_stat_statements.track = all
参数配置好后，重启数据库，加载pg_stat_statements模块，运行CREATE EXTENSION pg_stat_statements;语句。
配置好pg_stat_statements模块后，经过一段时间的运行，我们就可以通过pg_stat_statements视图来统计效率低的SQL，语句如下：
--查询语句总调用次数大于10次，平均运行时间倒序的SQL
SELECT t.userid,
t.dbid,
t.query || ';',
t.calls,
t.total_time,
t.rows,
t.total_time / t.calls
FROM pg_stat_statements t
WHERE (t.calls IS NOT NULL OR t.calls <> 0)
AND t.query !~ '^COPY|<insufficient privilege'
AND t.calls > 10
ORDER BY 7 DESC;

分析执行计划及优化语句

1.EXPLAIN介绍

EXPLAIN 语法：
EXPLAIN— show the execution plan of a statement
Synopsis
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
 where option can be one of:
   ANALYZE [ boolean ]
   VERBOSE [ boolean ]
   COSTS [ boolean ]
   BUFFERS [ boolean ]
   FORMAT { TEXT | XML | JSON | YAML }
例子： EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS,FORMAT JSON) select * from t_e_content a where a.contentid < 'CNBJTW2600000000626';
EXPLAIN参数解释：
ANALYZE ：执行命令并显示执行事件，默认false
VERBOSE ：对执行计划提供额外的信息，如查询字段信息等，默认false
COSTS ：显示执行计划的，默认true
BUFFERS ：默认false，前置条件是analyze
FORMAT ：默认格式是TEXT;

PostgreSQL 对每个查询产生一个查询规划。为匹配查询结构和数据属性选择正确的规划对性能绝对有关键性的影响。因此系统包含了一个复杂的规划器用于寻找最优的规划。你可以使用 EXPLAIN 命令察看规划器为每个查询生成的查询规划是什么。阅读查询规划是一门值得专门写一厚本教程的学问，而这份文档可不是这样的教程，但是这里作了一些基本的介绍。

查询规划的结构是一个规划节点的树。最底层的节点是表扫描节点：它们从表中返回原始数据行。不同的表访问模式有不同的扫描节点类型：顺序扫描、索引扫描、位图索引扫描。如果查询需要连接、聚集、排序、或者对原始行的其它操作，那么就会在扫描节点"之上"有其它额外的节点。并且，做这些操作通常都有多种方法，因此在这些位置也有可能出现不同的节点类型。EXPLAIN 给规划树中每个节点都输出一行，显示基本的节点类型和规划器为执行这个规划节点预计的开销值。第一行(最上层的节点)是对该规划的总执行开销的预计；这个数值就是规划器执行最小化的数值。

这里是一个简单的例子，只是用来显示输出会有些什么内容：
kfltdb=> EXPLAIN SELECT * FROM t_e_content;
QUERY PLAN

---

Seq Scan on t_e_content (cost=0.00..170.09 rows=3209 width=520)
(1 row)
EXPLAIN 引用的数值是：
预计的启动开销。在输出扫描开始之前消耗的时间，也就是在一个排序节点里执行排序的时间。
预计所有行都被检索的总开销。不过事实可能不是这样：比如带有 LIMIT 子句的查询将会在 Limit 规划节点的输入节点里很快停止。
预计这个规划节点输出的行数。同样，只执行到完成为止。
预计这个规划节点的行平均宽度(以字节计算)。
开销是用规划器根据成本参数构造的单位来衡量的，习惯上以磁盘页面抓取为单位。也就是 seq_page_cost 将被按照习惯设为 1.0(一次顺序的磁盘页面抓取)，其它开销参数将参照它来设置。有一点很重要：一个上层节点的开销包括它的所有子节点的开销。还有一点也很重要：这个开销只反映规划器关心的东西，尤其是没有把结果行传递给客户端的时间考虑进去，这个时间可能在实际的总时间里占据相当重要的分量，但是被规划器忽略了，因为它无法通过修改规划来改变：我们相信，每个正确的规划都将输出同样的记录集。输出的行数有一些小技巧，因为它不是规划节点处理/扫描过的行数，通常会少一些，反映对应用于此节点上的任意 WHERE 子句条件的选择性估计。通常而言，顶层的行预计会接近于查询实际返回、更新、删除的行数。
回到我们的例子：
kfltdb=> EXPLAIN SELECT * FROM t_e_content;
QUERY PLAN

---

Seq Scan on t_e_content (cost=0.00..170.09 rows=3209 width=520)

这个例子就像例子本身一样直接了当。如果你做一个

SELECT relname,

relkind,
reltuples,
relpages
FROM pg_class
WHERE relname = 't_e_content';
你会发现 t_e_content 有 138 磁盘页面和3209行。因此开销计算为 138 次页面读取，每次页面读取将消耗 seq_page_cost (默认1.0)，加上3209cpu_tuple_cost (默认0.01)
即：1381.0+3209*0.01=170.09
现在让我们修改查询并增加一个 WHERE 条件：
kfltdb=> EXPLAIN SELECT * FROM t_e_content WHERE contentid < 'CNBJTW2600000005739';
QUERY PLAN

---

Seq Scan on t_e_content (cost=0.00..178.11 rows=2275 width=520)

Filter: ((contentid)::text < 'CNBJTW2600000005739'::text)

(2 rows)

请注意 EXPLAIN 输出显示 WHERE 子句当作一个"Filter"条件。这意味着规划节点为它扫描的每一行检查该条件，并且只输出符合条件的行。预计的输出行数降低了，因为有 WHERE 子句。不过，扫描仍将必须访问所有3209 行，因此开销没有降低；实际上它还增加了一些以反映检查 WHERE 条件的额外 CPU 时间(默认cpu_operator_cost为0.0025)，即：1381.0+32090.01+3209*0.0025=178.1125

这条查询实际选择的行数是3209 ，但是预计的数目只是个大概。如果你试图重复这个试验，那么你很可能得到不同的预计。还有，这个预计会在每次 ANALYZE 命令之后改变，因为 ANALYZE 生成的统计是从该表中随机抽取的样本计算的。

#### 把查询限制条件改得更严格一些：

kfltdb=> EXPLAIN SELECT * FROM t_e_content WHERE contentid < 'CNBJTW2600000000262';

QUERY PLAN

---

Bitmap Heap Scan on t_e_content (cost=16.97..165.81 rows=867 width=520)
Recheck Cond: ((contentid)::text < 'CNBJTW2600000000262'::text)
-> Bitmap Index Scan on idx_contentid_t_e_content (cost=0.00..16.75 rows=867 width=0)
Index Cond: ((contentid)::text < 'CNBJTW2600000000262'::text)
(4 rows)

这里，规划器决定使用两步的规划：最底层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点真实地从表中抓取出那些行。独立地抓取数据行比顺序地读取它们的开销高很多，但是因为并非所有表的页面都被访问了，这么做实际上仍然比一次顺序扫描开销要少。使用两层规划的原因是因为上层规划节点把索引标识出来的行位置在读取它们之前按照物理位置排序，这样可以最小化独立抓取的开销。节点名称里面提到的"Bitmap"是进行排序的机制。

如果 WHERE 条件有足够的选择性，规划器可能会切换到一个"简单的"索引扫描规划：

kfltdb=> EXPLAIN SELECT * FROM t_e_content WHERE contentid = 'CNBJTW2600000000262';

QUERY PLAN

---

Index Scan using idx_contentid_t_e_content on t_e_content (cost=0.00..4.27 rows=1 width=520)
Index Cond: ((contentid)::text = 'CNBJTW2600000000262'::text)

(2 rows)

在这个例子中，表的数据行是以索引顺序抓取的，这样就令读取它们的开销更大，但是这里的行少得可怜，因此对行位置的额外排序并不值得。最常见的就是看到这种规划类型只抓取一行，以及那些要求 ORDER BY 条件匹配索引顺序的查询。

向 WHERE 子句里面增加另外一个条件：

kfltdb=> EXPLAIN SELECT * FROM t_e_content WHERE contentid = 'CNBJTW2600000000262' AND status = '1';

QUERY PLAN

---

Index Scan using idx_contentid_t_e_content on t_e_content (cost=0.00..4.27 rows=1 width=520)
Index Cond: ((contentid)::text = 'CNBJTW2600000000262'::text)
Filter: ((status)::text = '1'::text)
(3 rows)

新增的条件 status = '1' 减少了预计的输出行，但是没有减少开销，因为我们仍然需要访问相同的行。请注意，status 子句不能当做一个索引条件使用(因为这个索引只是在 contentid 列上有)。它被当做一个从索引中检索出的行的过滤器来使用。因此开销实际上略微增加了一些以反映这个额外的检查。

如果在 WHERE 里面使用的好几个字段上都有索引，那么规划器可能会使用索引的 AND 或 OR 的组合：

kfltdb=> EXPLAIN SELECT * FROM t_e_content WHERE contentid < 'CNBJTW2600000000262' AND contentno > 'CNBJTW222195109600000000810';

QUERY PLAN

---

Bitmap Heap Scan on t_e_content (cost=23.45..106.07 rows=78 width=520)
Recheck Cond: (((contentno)::text > 'CNBJTW222195109600000000810'::text) AND ((contentid)::text < 'CNBJTW2600000000262'::text))
-> BitmapAnd (cost=23.45..23.45 rows=78 width=0)
-> Bitmap Index Scan on idx_contentno_t_e_content (cost=0.00..6.41 rows=288 width=0)
Index Cond: ((contentno)::text > 'CNBJTW222195109600000000810'::text)
-> Bitmap Index Scan on idx_contentid_t_e_content (cost=0.00..16.75 rows=867 width=0)
Index Cond: ((contentid)::text < 'CNBJTW2600000000262'::text)
(7 rows)

但是这么做要求访问两个索引，因此与只使用一个索引，而把另外一个条件只当作过滤器相比，这个方法未必是更优。如果你改变涉及的范围，你会看到规划器相应地发生变化。

让我们试着使用我们上面讨论的字段连接两个表：

kfltdb=> EXPLAIN SELECT * FROM t_e_content a,t_e_content_version b WHERE a.contentno = b.contentno AND a.contentid < 'CNBJTW2600000000626';

QUERY PLAN

---

Nested Loop (cost=19.70..839.31 rows=1150 width=1060)
-> Bitmap Heap Scan on t_e_content a (cost=19.70..169.71 rows=961 width=520)
Recheck Cond: ((contentid)::text < 'CNBJTW2600000000626'::text)
-> Bitmap Index Scan on idx_contentid_t_e_content (cost=0.00..19.46 rows=961 width=0)
Index Cond: ((contentid)::text < 'CNBJTW2600000000626'::text)
-> Index Scan using idx_no_t_e_content_version on t_e_content_version b (cost=0.00..0.69 rows=1 width=540)
Index Cond: ((contentno)::text = (a.contentno)::text)
(7 rows)

在这个嵌套循环里，外层的扫描是我们前面看到的同样的位图索引，因此其开销和行计数是一样的(实例由于开发环境数据在变化，故开销不一样)，因为我们在该节点上附加了 WHERE 子句 contentid < 'CNBJTW2600000000626。此时a.contentno = b.contentno 子句还没有什么关系，因此它不影响外层扫描的行计数。对于内层扫描，当前外层扫描的数据行的 contentno 被插入内层索引扫描生成类似 b.contentno = contentno这样的索引条件。以外层扫描的开销为基础设置循环节点的开销，加上每个外层行的一个重复(这里是916*0.69)，然后再加上连接处理需要的一点点 CPU 时间。

在这个例子里，连接的输出行数与两个扫描的行数的乘积相同，但通常并不是这样的，因为通常你会有提及两个表的 WHERE 子句，因此它只能应用于连接(join)点，而不能影响两个关系的输入扫描。比如，如果我们加一条 WHERE ... AND a.lastupdatetime < b.lastupdatetime ，将减少输出行数，但是不改变任何一个输入扫描。

寻找另外一个规划的方法是通过设置每种规划类型的允许/禁止开关，强制规划器抛弃它认为优秀的(扫描)策略。这个工具目前比较原始，但很有用。

kfltdb=> SET enable_hashjoin = on;

SET

kfltdb=> EXPLAIN SELECT * FROM t_e_content a,t_e_content_version b WHERE a.contentno = b.contentno AND a.contentid < 'CNBJTW2600000000626';

QUERY PLAN

---

Hash Join (cost=181.72..421.80 rows=1150 width=1060)
Hash Cond: ((b.contentno)::text = (a.contentno)::text)
-> Seq Scan on t_e_content_version b (cost=0.00..213.42 rows=4042 width=540)
-> Hash (cost=169.71..169.71 rows=961 width=520)
-> Bitmap Heap Scan on t_e_content a (cost=19.70..169.71 rows=961 width=520)
Recheck Cond: ((contentid)::text < 'CNBJTW2600000000626'::text)
-> Bitmap Index Scan on idx_contentid_t_e_content (cost=0.00..19.46 rows=961 width=0)
Index Cond: ((contentid)::text < 'CNBJTW2600000000626'::text)
(8 rows)

这个规划仍然试图用同样的索引扫描从 t_e_content里面取出感兴趣的行，把它们藏在一个内存 Hash 表里，然后对 t_e_content_version 做一次顺序扫描，对每一条 t_e_content_version 记录检测上面的 Hash 表，寻找可能匹配 a.contentno = b.contentno 的行。读取 t_e_content和建立 Hash 表是此散列连接的全部启动开销，因为我们在开始读取 t_e_content_version之前不可能获得任何输出行。这个连接的总预计时间同样还包括相当重的检测 Hash 表 4042 次的 CPU 时间。不过，请注意，我们不需要对 169.71 乘 4042 ，因为 Hash 表的在这个规划类型中只需要设置一次。

我们可以用 EXPLAIN ANALYZE 检查规划器的估计值的准确性。这个命令实际上执行该查询然后显示每个规划节点内实际运行时间的和以及单纯 EXPLAIN 显示的估计开销。比如，我们可以像下面这样获取一个结果：

kfltdb=> SET enable_hashjoin = OFF;

SET

kfltdb=> EXPLAIN ANALYZE SELECT * FROM t_e_content a,t_e_content_version b WHERE a.contentno = b.contentno AND a.contentid < 'CNBJTW2600000000626';

QUERY PLAN

---

Nested Loop (cost=19.70..839.31 rows=1150 width=1060) (actual time=0.656..19.444 rows=1136 loops=1)
-> Bitmap Heap Scan on t_e_content a (cost=19.70..169.71 rows=961 width=520) (actual time=0.618..0.977 rows=944 loops=1)
Recheck Cond: ((contentid)::text < 'CNBJTW2600000000626'::text)
-> Bitmap Index Scan on idx_contentid_t_e_content (cost=0.00..19.46 rows=961 width=0) (actual time=0.599..0.599 rows=944 loops=1)
Index Cond: ((contentid)::text < 'CNBJTW2600000000626'::text)
-> Index Scan using idx_no_t_e_content_version on t_e_content_version b (cost=0.00..0.69 rows=1 width=540) (actual time=0.016..0.016 rows=1 loops=944)
Index Cond: ((contentno)::text = (a.contentno)::text)
Total runtime: 19.664 ms
(8 rows)

请注意"actual time"数值是以真实时间的毫秒计的，而"cost"估计值是以任意磁盘抓取的单元计的；因此它们很可能不一致。我们要关心的事是两组比值是否一致。

在一些查询规划里，一个子规划节点很可能运行多次。比如，在上面的嵌套循环的规划里，内层的索引扫描对每个外层行执行一次。在这种情况下，"loops" 报告该节点执行的总数目，而显示的实际时间和行数目是每次执行的平均值。这么做的原因是令这些数字与开销预计显示的数字具有可比性。要乘以"loops" 值才能获得在该节点花费的总时间。

EXPLAIN ANALYZE 显示的 Total runtime 包括执行器启动和关闭的时间，以及花在处理结果行上的时间。它不包括分析、重写、规划的时间。对于 SELECT 查询，总运行时间通常只是比从顶层规划节点汇报出来的总时间略微大些。对于 INSERT, UPDATE, DELETE 命令，总运行时间可能会显著增大，因为它包括花费在处理结果行上的时间。在这些查询里，顶层规划节点的时间实际上是花在计算新行和/或定位旧行上的时间，但是不包括花在标记变化上的时间。

如果 EXPLAIN 的结果除了在你实际测试的情况之外不能推导出其它的情况，那它就什么用都没有；比如，在一个很小的表上的结果不能适用于大表。规划器的开销计算不是线性的，因此它很可能对大些或者小些的表选择不同的规划。一个极端的例子是一个只占据一个磁盘页面的表，在这样的表上，不管它有没有索引可以使用，你几乎都总是得到顺序扫描规划。规划器知道不管在任何情况下它都要进行一个磁盘页面的读取，所以再扩大几个磁盘页面读取以查找索引是没有意义的。

下面是对表关联的3种结合运算的概念图。

哈希运算要做一张哈希表，如果外部的tb1的数据不是特别多的时候是比较快的。如果tb1相当大，这时候做一张的话可能的时间反而更多。因为做的哈希表内存装不下，需要输出到硬盘，这样IO读取多了，速度就低下了。合并查询对外部和内部表都要用两个表的关联字段各自做一张，并且还要排序。如果是已经拍好序的，速度是很快。如果运算有数据特大的时候还有可能不如嵌套循环快。因此我们有个别地方可能直接用系统默认的执行计划的话反而很慢。如果是那样的话，可以尝试强制改变执行计划。禁止使用hash连接或合并连接，这个要具体问题具体分析。

有时候我们不想用系统默认的执行计划，这时候就需要自己强制控制执行计划。
禁止某种运算的SQL语法：SET enable_运算类型 = off;   //或者=false
开启某种运算的SQL语法：SET enable_运算 = on;   //或者=true
执行计划可以改变的运算方法如下：
  enable_bitmapscan
enable_hashagg
enable_hashjoin
enable_indexscan
enable_indexonlyscan
enable_material
enable_mergejoin
enable_nestloop
enable_seqscan
enable_sort
enable_tidscan

如果我们只想改变当前要执行的SQL的执行计划，而不想影响其他的SQ的话。在设置 SQL里面加一个关键字 session即可

例子： set session enable_hashjoin=false   //禁止使用哈希结合算法

2.用明确的 JOIN 控制规划器

我们可以在一定程度上用明确的 JOIN 语法控制查询规划器。要明白为什么有这茬事，我们首先需要一些背景知识。
在简单的连接查询里，比如
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
规划器可以按照任何顺序自由地连接给出的表。比如，它可以生成一个查询规划先用 WHERE 条件 a.id = b.id 把 A 连接到 B ，然后用另外一个 WHERE 条件把 C 连接到这个表上来，或者也可以先连接 B 和 C 然后再连接 A ，同样得到这个结果。或者也可以连接 A 到 C 然后把结果与 B 连接，不过这么做效率比较差，因为必须生成完整的 A 和 C 的迪卡尔积，而在查询里没有可用的 WHERE 子句可以优化该连接(PostgreSQL 执行器里的所有连接都发生在两个输入表之间，所以在这种情况下它必须先得出一个结果)。重要的一点是这些连接方式给出语义上相同的结果，但在执行开销上却可能有巨大的差别。因此，规划器会对它们进行检查并找出最高效的查询规划。
如果查询只涉及两或三个表，那么在查询里不会有太多需要考虑的连接。但是潜在的连接顺序的数目随着表数目的增加成指数增加的趋势。当超过十个左右的表以后，实际上根本不可能对所有可能做一次穷举搜索，甚至对六七个表都需要相当长的时间进行规划。如果有太多输入的表，PostgreSQL 规划器将从穷举搜索切换为基因概率搜索，以减少可能性数目(样本空间)。切换的阈值是用运行时参数 geqo_threshold 设置的。基因搜索花的时间少，但是并不一定能找到最好的规划。
当查询涉及外部连接时，规划器就不像对付普通(内部)连接那么自由了。比如，看看下面这个查询
SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
尽管这个查询的约束和前面一个非常相似，但它们的语义却不同，因为如果 A 里有任何一行不能匹配B和C的连接里的行，那么该行都必须输出。因此这里规划器对连接顺序没有什么选择：它必须先连接 B 到 C ，然后把 A 连接到该结果上。因此，这个查询比前面一个花在规划上的时间少。在其它情况下，规划器就有可能确定多种连接顺序都是安全的。比如，对于
SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);
将 A 首先连接到 B 或 C 都是有效的。当前，只有 FULL JOIN 完全强制连接顺序。大多数 LEFT JOIN 或 RIGHT JOIN 都可以在某种程度上重新排列。
明确的连接语法(INNER JOIN, CROSS JOIN 或无修饰的 JOIN)语义上和 FROM 中列出输入关系是一样的，因此我们没有必要约束连接顺序。
即使大多数 JOIN 并不完全强迫连接顺序，但仍然可以明确的告诉 PostgreSQL 查询规划器 JOIN 子句的连接顺序。比如，下面三个查询逻辑上是等效的：
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
但如果我们告诉规划器遵循 JOIN 的顺序，那么第二个和第三个还是要比第一个花在规划上的时间少。这个作用对于只有三个表的连接而言是微不足道的，但对于数目众多的表，可能就是救命稻草了。
要强制规划器遵循准确的 JOIN 连接顺序，我们可以把运行时参数 join_collapse_limit 设置为 1(其它可能的数值在下面讨论)。
你完全不必为了缩短搜索时间来约束连接顺序，因为在一个简单的 FROM 列表里使用 JOIN 操作符就很好了。比如考虑：
SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;
如果设置 join_collapse_limit = 1 ，那么这句话就相当于强迫规划器先把A连接到B ，然后再连接到其它的表上，但并不约束其它的选择。在本例中，可能的连接顺序的数目减少了 5 倍。
按照上面的想法考虑规划器的搜索问题是一个很有用的技巧，不管是对减少规划时间还是对引导规划器生成好的规划都很有帮助。如果缺省时规划器选择了一个糟糕的连接顺序，你可以用 JOIN 语法强迫它选择一个更好的(假设知道一个更好的顺序)。
一个非常相近的影响规划时间的问题是把子查询压缩到它们的父查询里面。比如，考虑下面的查询
SELECT *
FROM x, y,
(SELECT * FROM a, b, c WHERE something) AS ss
WHERE somethingelse;
这个情况可能在那种包含连接的视图中出现；该视图的 SELECT规则将被插入到引用视图的场合，生成非常类似上面的查询。通常，规划器会试图把子查询压缩到父查询里，生成
SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
这样通常会生成一个比独立的子查询更好些的规划。比如，外层的 WHERE 条件可能先把X连接到 A 上，这样就消除了 A 中的许多行，因此避免了形成全部子查询逻辑输出的需要。但是同时，我们增加了规划的时间；在这里，我们有一个用五路连接代替两个独立的三路连接的问题，这样的差距是巨大的，因为可能的规划数的是按照指数增长的。规划器将在父查询可能超过 from_collapse_limit 个 FROM 项的时候，不再压缩子查询，以此来避免巨大的连接搜索数。from_collapse_limit 和 join_collapse_limit的默认值都为8，你可以通过调整这个运行时参数来在规划时间和规划质量之间作出平衡。
例如：set session join_collapse_limit = 1; set session from_collapse_limit = 1;
from_collapse_limit 和 join_collapse_limit 名字类似是因为他们做的事情几乎相同：一个控制规划器何时把子查询"平面化"，另外一个控制何时把明确的连接平面化。通常，你要么把 join_collapse_limit 设置成和 from_collapse_limit 一样(明确连接和子查询的行为类似)，要么把 join_collapse_limit 设置为 1(如果你想用明确连接控制连接顺序)。但是你可以把它们设置成不同的值，这样你就可以在规划时间和运行时之间进行仔细的调节。

3.案例解析
案例1：
SQL来源于长沙生产环境，SQL如下（平均运行时间在1000ms左右）
SELECT t1.questionid,
t1.questioncode,
t1.typelevel,
t1.creator,
t1.createtime,
t1.updatetime,
t1.status,
t1.title,
t1.subjectid,
t1.grade,
t1.term,
t1.item,
t1.degree,
t1.sharerange,
t1.defaultscore,
t1.paragraphid,
t1.orgid,
t1.sourceid,
t1.studylevelid,
t1.editiontypeid,
t1.refertimes,
t1.clicktimes,
t1.labelid AS typelevellabelid,
tu.creatorname
FROM t_e_question t1
LEFT JOIN (SELECT userid,
realname AS creatorname
FROM t_e_user_logininfo) tu
ON t1.creator = tu.userid, (SELECT q.questioncode,
MAX(version) ver
FROM t_e_question q
WHERE q.status != '-1'
AND q.creator = 'CNBJTW0200000000589'
GROUP BY q.questioncode) t2
WHERE t1.status != '-1'
AND t1.status != '4'
AND t1.questioncode = t2.questioncode
AND t1.version = t2.ver
AND term = '1'
AND paragraphid = 'PRIMARY_SCHOOL'
AND createtime >= to_timestamp('2013-01-22', 'YYYY-MM-DD HH24:MI:SS.US')
AND createtime <= to_timestamp('2013-04-22 23:59:59', 'YYYY-MM-DD HH24:MI:SS.US')
AND to_char(updatetime, 'YYYY-MM-DD') >= to_char(to_date('2013-01-22', 'YYYY-MM-DD'), 'YYYY-MM-DD')
AND to_char(updatetime, 'YYYY-MM-DD') <= to_char(to_date('2013-04-22', 'YYYY-MM-DD'), 'YYYY-MM-DD')
AND creator = 'CNBJTW0200000000589'
ORDER BY updatetime DESC NULLS LAST,
questionid DESC NULLS LAST LIMIT 200 offset 0;

执行计划如下：
Limit (cost=12364.10..12364.10 rows=1 width=371)
-> Sort (cost=12364.09..12364.10 rows=1 width=371)
Sort Key: t1.updatetime, t1.questionid
-> Nested Loop Left Join (cost=6083.65..12364.08 rows=1 width=371)
Join Filter: ((t1.creator)::text = (t_e_user_logininfo.userid)::text)
-> Nested Loop (cost=6083.65..12359.80 rows=1 width=363)
Join Filter: (((t1.questioncode)::text = (q.questioncode)::text) AND (t1.version = (max(q.version))))
-> HashAggregate (cost=5993.96..6027.19 rows=3323 width=25)
-> Bitmap Heap Scan on t_e_question q (cost=90.79..5972.09 rows=4373 width=25)
Recheck Cond: ((creator)::text = 'CNBJTW0200000000589'::text)
Filter: ((status)::text <> '-1'::text)
-> Bitmap Index Scan on idx_creator_t_e_question (cost=0.00..89.69 rows=4990 width=0)
Index Cond: ((creator)::text = 'CNBJTW0200000000589'::text)
-> Materialize (cost=89.69..6183.08 rows=2 width=368)
-> Bitmap Heap Scan on t_e_question t1 (cost=89.69..6183.07 rows=2 width=368)
Recheck Cond: ((creator)::text = 'CNBJTW0200000000589'::text)
Filter: (((status)::text <> '-1'::text) AND ((status)::text <> '4'::text) AND ((term)::text = '1'::text) AND ((paragraphid)::text = 'PRIMARY_SCHOOL'::text) AND (createtime >= to_timestamp('2013-01-22'::text, 'YYYY-MM-DD HH24:MI:SS.US'::text)) AND (createtime <= to_timestamp('2013-04-22 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS.US'::text)) AND (to_char(updatetime, 'YYYY-MM-DD'::text) >= to_char((to_date('2013-01-22'::text, 'YYYY-MM-DD'::text))::timestamp with time zone, 'YYYY-MM-DD'::text)) AND (to_char(updatetime, 'YYYY-MM-DD'::text) <= to_char((to_date('2013-04-22'::text, 'YYYY-MM-DD'::text))::timestamp with time zone, 'YYYY-MM-DD'::text)))
-> Bitmap Index Scan on idx_creator_t_e_question (cost=0.00..89.69 rows=4990 width=0)
Index Cond: ((creator)::text = 'CNBJTW0200000000589'::text)
-> Index Scan using pk_t_e_user_logininfo on t_e_user_logininfo (cost=0.00..4.27 rows=1 width=27)
Index Cond: ((userid)::text = 'CNBJTW0200000000589'::text)

一般我在分析执行计划时主要观察2点，大表是否走索引（小表全表扫描效率可能更高）和cost递增大的节点，此计划中两次取了t_e_question表的数据，且都为成本耗费比较大的节点。优化点：1、考虑只取一次t_e_question表数据，可用窗口函数解决此问题；2、简化日期条件；3、对questioncode,version字段建联合索引，窗口函数可能会用到。
改造后语句如下：
-- CREATE INDEX idx_qv_t_e_question ON t_e_question (questioncode,version desc)TABLESPACE ts_index_common;
SELECT t.questionid,
t.questioncode,
t.typelevel,
t.creator,
t.createtime,
t.updatetime,
t.status,
t.title,
t.subjectid,
t.grade,
t.term,
t.item,
t.degree,
t.sharerange,
t.defaultscore,
t.paragraphid,
t.orgid,
t.sourceid,
t.studylevelid,
t.editiontypeid,
t.refertimes,
t.clicktimes,
t.labelid AS typelevellabelid,
(SELECT realname
FROM t_e_user_logininfo
WHERE userid = 'CNBJTW0200000000589') creatorname--$5 请使用绑定变量
FROM (SELECT questionid,
questioncode,
typelevel,
creator,
createtime,
updatetime,
status,
title,
subjectid,
grade,
term,
item,
degree,
sharerange,
defaultscore,
paragraphid,
orgid,
sourceid,
studylevelid,
editiontypeid,
refertimes,
clicktimes,
labelid,
row_number() over(PARTITION BY questioncode ORDER BY version DESC) top
FROM t_e_question
WHERE status NOT IN ('-1', '4')
AND term = '1'
AND paragraphid = 'PRIMARY_SCHOOL'
AND createtime >= '2013-01-22'--$1 请使用绑定变量
AND createtime < '2013-04-23'--$2 请使用绑定变量
AND updatetime >= '2013-01-22'--$1 请使用绑定变量
AND updatetime < '2013-04-23'--$2 请使用绑定变量
AND creator = 'CNBJTW0200000000589') t--$5 请使用绑定变量
WHERE t.top = 1
ORDER BY t.updatetime DESC NULLS LAST,
t.questionid DESC NULLS LAST LIMIT 200 offset 0; --$3 $4 请使用绑定变量
执行计划如下：
Limit (cost=6007.71..6007.71 rows=1 width=363)
InitPlan 1 (returns $0)
-> Index Scan using idx_userid_t_e_user_logininfo on t_e_user_logininfo (cost=0.00..4.27 rows=1 width=8)
Index Cond: ((userid)::text = 'CNBJTW0200000000589'::text)
-> Sort (cost=6003.44..6003.45 rows=1 width=363)
Sort Key: t.updatetime, t.questionid
-> Subquery Scan on t (cost=6000.77..6003.43 rows=1 width=363)
Filter: (t.top = 1)
-> WindowAgg (cost=6000.77..6002.41 rows=82 width=368)
-> Sort (cost=6000.77..6000.97 rows=82 width=368)
Sort Key: t_e_question.questioncode, t_e_question.version
-> Bitmap Heap Scan on t_e_question (cost=89.26..5998.16 rows=82 width=368)
Recheck Cond: ((creator)::text = 'CNBJTW0200000000589'::text)
Filter: (((status)::text <> ALL ('{-1,4}'::text[])) AND (createtime >= '2013-01-22 00:00:00'::timestamp without time zone) AND (createtime < '2013-04-23 00:00:00'::timestamp without time zone) AND (updatetime >= '2013-01-22 00:00:00'::timestamp without time zone) AND (updatetime < '2013-04-23 00:00:00'::timestamp without time zone) AND ((term)::text = '1'::text) AND ((paragraphid)::text = 'PRIMARY_SCHOOL'::text))
-> Bitmap Index Scan on idx_creator_t_e_question (cost=0.00..89.24 rows=4930 width=0)
Index Cond: ((creator)::text = 'CNBJTW0200000000589'::text)
优化后的语句平均运行时间降到了40多ms。
案例2：
SQL来源于长沙生产环境，SQL如下（平均运行时间在700ms左右）
SELECT COUNT()
FROM (SELECT t1.questioncode
FROM t_e_question t1,
(SELECT q.questioncode,
MAX(version) ver
FROM t_e_question q
WHERE q.status != '-1'
AND q.creator = 'CNBJTW0200000000589'
GROUP BY q.questioncode) t2
WHERE t1.status NOT IN ('-1', '4')
AND t1.creator = 'CNBJTW0200000000589'
AND t1.questioncode = t2.questioncode
AND t1.version = t2.ver
AND term = '1'
AND paragraphid = 'PRIMARY_SCHOOL'
AND createtime >= to_timestamp('2013-01-22', 'YYYY-MM-DD HH24:MI:SS.US')
AND createtime <= to_timestamp('2013-04-22 23:59:59', 'YYYY-MM-DD HH24:MI:SS.US')
AND to_char(updatetime, 'YYYY-MM-DD') >= to_char(to_date('2013-01-22', 'YYYY-MM-DD'), 'YYYY-MM-DD')
AND to_char(updatetime, 'YYYY-MM-DD') <= to_char(to_date('2013-04-22', 'YYYY-MM-DD'), 'YYYY-MM-DD')
AND creator = 'CNBJTW0200000000589'
GROUP BY t1.questioncode) AS tt;
执行计划如下：
Aggregate (cost=12249.06..12249.07 rows=1 width=0)
-> HashAggregate (cost=12249.03..12249.04 rows=1 width=20)
-> Nested Loop (cost=6036.21..12249.03 rows=1 width=20)
Join Filter: (((t1.questioncode)::text = (q.questioncode)::text) AND (t1.version = (max(q.version))))
-> HashAggregate (cost=5946.97..5979.80 rows=3283 width=25)
-> Bitmap Heap Scan on t_e_question q (cost=90.32..5925.37 rows=4320 width=25)
Recheck Cond: ((creator)::text = 'CNBJTW0200000000589'::text)
Filter: ((status)::text <> '-1'::text)
-> Bitmap Index Scan on idx_creator_t_e_question (cost=0.00..89.24 rows=4930 width=0)
Index Cond: ((creator)::text = 'CNBJTW0200000000589'::text)
-> Materialize (cost=89.24..6121.50 rows=2 width=25)
-> Bitmap Heap Scan on t_e_question t1 (cost=89.24..6121.49 rows=2 width=25)
Recheck Cond: ((creator)::text = 'CNBJTW0200000000589'::text)
Filter: (((status)::text <> ALL ('{-1,4}'::text[])) AND ((term)::text = '1'::text) AND ((paragraphid)::text = 'PRIMARY_SCHOOL'::text) AND (createtime >= to_timestamp('2013-01-22'::text, 'YYYY-MM-DD HH24:MI:SS.US'::text)) AND (createtime <= to_timestamp('2013-04-22 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS.US'::text)) AND (to_char(updatetime, 'YYYY-MM-DD'::text) >= to_char((to_date('2013-01-22'::text, 'YYYY-MM-DD'::text))::timestamp with time zone, 'YYYY-MM-DD'::text)) AND (to_char(updatetime, 'YYYY-MM-DD'::text) <= to_char((to_date('2013-04-22'::text, 'YYYY-MM-DD'::text))::timestamp with time zone, 'YYYY-MM-DD'::text)))
-> Bitmap Index Scan on idx_creator_t_e_question (cost=0.00..89.24 rows=4930 width=0)
Index Cond: ((creator)::text = 'CNBJTW0200000000589'::text)
这条语句和案例1类似，但是优化空间更大，因为它只是求总数，根据业务可以去掉语句的绿底部分，优化后语句如下：
SELECT COUNT()
FROM (SELECT questioncode
FROM t_e_question
WHERE status NOT IN ('-1', '4')
AND creator = 'CNBJTW0200000000589' --$3 请使用绑定变量
AND term = '1'
AND paragraphid = 'PRIMARY_SCHOOL'
AND createtime >= '2013-01-22'--$1 请使用绑定变量
AND createtime < '2013-04-23'--$2 请使用绑定变量
AND updatetime >= '2013-01-22'--$1 请使用绑定变量
AND updatetime < '2013-04-23'--$2 请使用绑定变量
GROUP BY questioncode) AS tt;
执行计划如下：
Aggregate (cost=5999.88..5999.89 rows=1 width=0)
-> HashAggregate (cost=5998.47..5999.10 rows=63 width=20)
-> Bitmap Heap Scan on t_e_question (cost=89.26..5998.26 rows=82 width=20)
Recheck Cond: ((creator)::text = 'CNBJTW0200000000589'::text)
Filter: (((status)::text <> ALL ('{-1,4}'::text[])) AND (createtime >= '2013-01-22 00:00:00'::timestamp without time zone) AND (createtime < '2013-04-23 00:00:00'::timestamp without time zone) AND (updatetime >= '2013-01-22 00:00:00'::timestamp without time zone) AND (updatetime < '2013-04-23 00:00:00'::timestamp without time zone) AND ((term)::text = '1'::text) AND ((paragraphid)::text = 'PRIMARY_SCHOOL'::text))
-> Bitmap Index Scan on idx_creator_t_e_question (cost=0.00..89.24 rows=4930 width=0)
Index Cond: ((creator)::text = 'CNBJTW0200000000589'::text)
优化后的语句平均运行时间降到了20ms左右。

优化经验：1、大表尽量走索引；2、最理想的SQL执行路径是我们希望它执行的路径，因为PG是根据收集统计信息作出的最优判断，故我们需定期ANALYZE表，即使这样PG也有可能选择的执行路径不是最优的，这时就需要我们根据以上的一些技术强制它按照我们的思路运行；3、在优化效率低的SQL时，有时SQL很复杂，执行计划就更复杂了，一时半会难以看懂，这时我们可以配合删减法（一条条删除语句条件和子查询进行测试）进行瓶颈查找；4、技术上的优化是有限的，业务上的提升空间更大，故大家在写SQL时尽量从业务角度简写SQL。