PostgreSQL in 语法 的优化器处理以及如何优化

postgresql in 的优化器处理以及如何优化

在使用数据库的过程中，经常会遇到需要匹配多个值的情况。

通常的写法包括：

每种写法会产生多种执行计划的可能，如下：

sql优化策略是尽量减少cpu的运算以及page的扫描数量。

下面针对每种sql，看看对应的可能的执行计划有什么差别，（使用开关来控制执行计划的选择, 如set enable_indexscan=off）

支持的开关如下：

开始测试，使用auto_explain输出执行计划：

测试sql写法1：

优化器选择1 (index scan)：

离散扫描，适合小的扫描集。

优化器选择2 (bitmap scan)：

比index scan多了recheck的开销，以及按照ctid排序的开销。

适合大的扫描集，排序的目的是减少离散扫描，还可以用到块设备的prefetch。

优化器选择3 (seq scan)：

适合非常庞大的扫描集。

测试sql写法2：

测试sql写法3：

优化器选择1 (bitmapindex scan + bitmapor)：

使用or的写法，只能选择bitmap index scan，所以不如使用in的写法。

优化器选择2 (seq scan)：

测试sql写法4：

优化器选择1 (nestloop join)：

适合小的扫描集，并且其中有一个表的join列是带有主键或唯一约束的。

优化器选择2 (hash join)：

适合大的扫描集，同时两个表的join列上面都没有索引的情况。

优化器选择3 (merge join)：

适合大的扫描集，并且两个表的join列都有索引。

测试sql写法5：

测试sql写法6：

优化选择建议如下，可以根据需求来写sql，最不建议写的是or：

index scan：

bitmap scan：

seq scan：

bitmapindex scan + bitmapor|and：

nestloop join：

hash join：

merge join：

如果你发现写好的sql没有选择最优的执行计划，可以通过设置优化器开关，或者使用hint plan这个插件来指定优化器使用对应的scan或join method.

http://pghintplan.osdn.jp/pg_hint_plan.html