一、常见优化器选项

//表扫描方式       
enable_seqscan       控制优化器对顺序扫描/全表扫描规划类型的使用。
		     实际上并不能完全禁止全表扫描，但是把这个变量关闭会让优化器在存在其他方法时优先选择其他方法。

enable_bitmapscan     控制优化器对位图扫描规划类型的使用。
		      PostgreSQL 不支持创建位图索引。但会为每个查询动态创建页面的位图。它不会被缓存或重复使用，并在位图索引扫描结束时被丢弃。
		      当执行计划的结果行数很多时会进行这种扫描，如非等值查询、IN子句或有多个条件都可以走不同的索引时。

enable_indexscan       控制优化器对索引扫描规划类型的使用。
enable_indexonlyscan   控制优化器对仅索引扫描规划类型的使用。
enable_tidscan         控制优化器对TID扫描规划类型的使用。

//表组合方式
enable_mergejoin     控制优化器对融合连接规划类型的使用。  
enable_hashjoin      控制优化器对Hash连接规划类型的使用。
enable_nestloop      控制优化器对内表全表扫描嵌套循环连接规划类型的使用。

enable_memoize        (PG14新增)      主要用来提升嵌套循环连接的性能，是否允许查询使用 memoize 来缓存嵌套循环连接内参数化扫描的结果。

//排序相关
enable_sort       控制优化器是否使用明确的排序，如果设置为“off”，执行计划只有排序
		  一条路时，优化器也只能选择这条路，但如果有其他方法可以走，优化器会优先选择其了方法。  

enable_incremental_sort  (PG13新增)   控制优化器使用增量排序步骤。
enable_gathermerge    (PG10新增)是否允许查询使用收集合并。  

//聚合选项
enable_hashagg    控制优化器对Hash聚集规划类型的使用。

//分区表相关
enable_partition_pruning      (PG11新增) 控制优化器是否使用分区裁剪。 允许查询规划器和执行器将分区边界与查询中的条件进行比较，以确定必须扫描哪些分区。防止昂贵且不必要的表扫描。       
enable_partitionwise_aggregate  (PG11新增) 控制优化器是否使用使用分区分组或聚合。
enable_partitionwise_join   (PG11新增) 控制优化器是否使用使用分区连接。
enable_presorted_aggregate  控制查询规划器是否生成一个计划，该计划将提供按照查询的 ORDER BY / DISTINCT 聚合函数所需的顺序预先排序的行。禁用后，查询规划器将生成一个计划，该计划始终要求执行器在执行包含 ORDER BY 或 DISTINCT 子句的每个聚合函数的聚合之前执行排序。启用后，规划器将尝试生成一个更高效的计划，该计划为聚合函数提供以它们所需的聚合顺序预先排序的输入。默认值为 on。

--------   
//其余
enable_material 控制优化器对实体化的使用，即是否允许查询使用物化。
enable_group_by_reordering  控制查询规划器是否会生成一个计划，该计划将提供按照计划子节点（例如索引扫描）的键顺序排序的GROUP BY键。禁用时，查询规划器将生成一个计划，其中的GROUP BY键仅排序以匹配ORDER BY子句（如果有）。启用时，规划器将尝试生成一个更高效的计划。默认值为 on。
enable_async_append  是否允许查询使用异步感知追加
force_parallel_mode =on   强制开启并行。通常用来测试，在PostgreSQL 9.6中添加，在PostgreSQL 16版本调整为debug_parallel_query。
enable_parallel_append   (PG11新增) 允许并行执行 Append
enable_parallel_hash     (PG11新增)允许并行hash join
复制

二、表组合方式

常用的表组合方式是NESTED LOOP，HASH JOIN和MERGE JOIN。

（1）HASH JOIN

一般行数相差较多的，大表做驱动表，使用HASH JOIN。HASH JOIN总是选择较小的数据集来构建HASH表，然后用较大的数据集去做探测。这是HASH JOIN的算法决定的，这样的开销最小。
原理是：根据小表建立一个可以存在于hash area内存中的hash table，然后用大表来探测前面的hash table。
Hash Join根据hash table是否能在hash内存（work_mem*hash_mem_multiplier）中放的下而分为两种情况，One-Pass Hash Join（一次性完成的哈希连接）和Two-Pass Hash Join（两阶段完成的哈希连接）。

• One-Pass Hash Join：就是将小表的数据使用哈希函数一次性映射到hash table中（hash area足够存放hash table）。Hash table中存储的是哈希键和值对，可以通过键快速访问值。
  [注：hash_mem_multiplier是PG-13版本增加的，允许单独设置hash操作的mem, 设置hash_mem_multiplier参数, 意思是work_mem的倍数, 调大hash_mem_multiplier可以提高hash操作性能]

• Two-Pass Hash Join ：如果优化器预估显示hash table将超过分配的内存。
  在第一阶段中，执行器扫描内部行集并构建hash table。如果扫描的行属于第一批，则将其添加到hash table并保存在内存中，否则它将被写入临时文件（每个批处理都有一个单独的文件）。临时文件的总量由temp_file_limit参数限制。
  在第二阶段中，扫描外部集合。如果该行属于第一批，它将与内部行集第一批行的hash table进行匹配。如果该行属于不同的批处理，则将其存储在临时文件中。因此，N批可以使用2(N-1)个文件。一旦第二阶段完成，为哈希表分配的内存将被释放。此时已经完成了其中一个批次的连接结果。Two-Pass Hash Join的执行计划里，"Batches："关键字代表在第一阶段里拆分为多少个批次进行处理。
  磁盘上存储在临时文件里的数据，会一直重复这些步骤：内部数据集的行从临时文件转移到哈希表；然后从另一个临时文件中读取与同一批处理相关的外部集的行，并与此哈希表进行匹配。一旦处理，临时文件将被删除。

（2）NESTED LOOP

如果在上边基础上给关联列增加索引，并针对关联列增加筛选条件，只取少量记录，则可能不走HASH JOIN而选择NESTED LOOP，因为这种情况下，只需要较少的循环，并且因为存在索引，可以很快完成join。NESTED LOOP在OLTP交易场景占比是最多的，常用于关联字段为主键或索引字段的情况，通过主键或索引以及loop的方式，A表可以快速查找到匹配的B表中数据。

在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（>10000不适合），要把返回子集较小的表作为外表，而且在内表的连接字段上要有索引，否则会很慢。

执行的过程为：确定一个驱动表（outer table），另一个表为inner table，驱动表中的每一行与inner表中的相应记录JOIN类似一个嵌套的循环。适用于驱动表的记录集比较小（<10000）而且inner表有有效的访问方法（Index）。需要注意的是，JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

（3）MERGE JOIN

MERGE JOIN一般出现在两个表的数据集规模相对接近的时候，它把两个行源的数据分别先排序，然后将两个排过序的行源做JOIN，如果被关联的两个数据集正好是有序的，这种方式则是最高效的。并且，PG的MERGE JOIN不支持<>等操作。

三、几种AGG算子

常见的聚集算子实现有两种，一种是基于哈希表的实现（HASH AGG），另一种则是基于排序的实现（GROUP AGG）。两种方法通常来说都是针对有group by的查询而言的。GroupAggregate 需要对记录进行排序，而 HashAggregate 则无需进行排序，通常 HashAggregate 要快很多。

除此之外，对于没有group by的查询来说，只需要扫描一遍表，并对每一个元组进行 count/sum/min等累积计算操作即可，这种情况在PG中被称为PLAIN AGG。

但是对于GroupAggregate来说，消耗的内存基本上是恒定的，无论group by哪个字段。当聚合函数较少的时候，速度也相对较慢，但是相对稳定。

HashAggregate在少数聚合函数是表现优异，但是很多聚合函数，性能跟消耗的内存差异很明显。尤其是受group by字段的唯一性很明显，字段count（district）值越大，hash聚合消耗的内存越多，性能下降剧烈。

• 所以在SQL中有大量聚合函数，group by 的字段由相对比较唯一的时候，应该用GroupAggregate，而不能用HashAggregate。所以尽管大多数情况下Agg的时候，选择HashAgg性能较好，但是如果大结果集，大量聚合则可能选择Sort+GroupAgg。

• Group By 后面的列越多，优化器会认为返回的不同值越多，优化器更偏向于采用GroupAggregate算法。PostgreSQL 对于多列值的合并估算并不准确，这就需要手动创建多列统计信息。实际优化过程中，可能经常需要创建多列统计信息，以促使优化器采用HashAggregate。举例为：create statistics sss1(ndistinct) on id1,id2,id3,id4,id5,id6 from tab_a;

（1）PLAIN AGG

PLAIN AGG直接计算不含分组操作(group by)的计算策略，只需要扫描一遍表，并对每一个元组进行 count/sum/min等累积计算操作即可。

postgres=# create table tab_a1(a1 int, a2 int, a3 int);
CREATE TABLE
postgres=# explain  select avg(a1) from tab_a1;
+----------------------------------------------------------------+
|                           QUERY PLAN                           |
+----------------------------------------------------------------+
| Aggregate  (cost=35.50..35.51 rows=1 width=32)                 |
|   ->  Seq Scan on tab_a1  (cost=0.00..30.40 rows=2040 width=4) |
+----------------------------------------------------------------+
(2 rows)
复制

（2）GROUP AGG

GROUP AGG，可以用来计算含有分组操作的聚集计算，但是它要求其每个元组的输入必须是有序的，因此也称为sort-agg。对于普通聚合函数，使用group聚合，其原理是先将表中的数据按照group by的字段排序，这样子同一个group by的值就在一起，这样就只需要对排好序的数据进行一次全扫描，就可以得到聚合的结果了。

postgres=# set enable_hashagg = off;
SET
postgres=# explain select count(1) from pg_class group by oid;
+-------------------------------------------------------------------------------------------------+
|                                           QUERY PLAN                                            |
+-------------------------------------------------------------------------------------------------+
| GroupAggregate  (cost=0.27..29.89 rows=454 width=12)                                            |
|   Group Key: oid                                                                                |
|   ->  Index Only Scan using pg_class_oid_index on pg_class  (cost=0.27..23.08 rows=454 width=4) |
+-------------------------------------------------------------------------------------------------+
(3 rows)
复制

（3）HASH AGG

Hash 聚合是种常用的数据处理算法，他会对如sum,avg,max,min 等group by 操作进行数据的分组和聚合计算，在处理的过程中，会将数据分成多个组，每个组具有相同的分组键，聚合计算会对该组中的数据进行合并计算。它不依赖于下层元组的有序输入。

会根据group by字段后面的值算出hash值，并根据前面使用的聚合函数在内存中维护对应的列表。如果select后面有两个聚合函数，那么在内存中就会维护两个对应的数据。有n个聚合函数就会维护n个同样的数组。对于hash算法来说，数组的长度肯定是大于group by的字段的distinct值的个数的，且跟这个值应该呈线性关系，group by后面的值越唯一，使用的内存也就越大。

Hash 聚合的优点减少了磁盘的IO 消耗，将大部分聚合计算都在内存中进行，同时基于hash聚合可以使用并行的能力，充分利用多核心的CPU 来进行计算加速数据的处理。

postgres=#  explain select count(*) from tab_aa group by relname;
+----------------------------------------------------------------+
|                           QUERY PLAN                           |
+----------------------------------------------------------------+
| HashAggregate  (cost=23.20..25.20 rows=200 width=72)           |
|   Group Key: relname                                           |
|   ->  Seq Scan on tab_aa  (cost=0.00..20.80 rows=480 width=64) |
+----------------------------------------------------------------+
(3 rows)
复制