1、标量子查询与filter
当一个查询在select和from之间，那么这种子查询就是标量子查询。实际应用中，很多人在写SQL时为了方便会写一堆标量子查询的SQL，在表数据不大时，一般并不会有什么影响，但当数据量较大时，往往会对性能造成巨大影响。
因为标量子查询类似于一个天然的嵌套循环，而且驱动表固定为主表。如下所示：
1 2 3 4 5 6 7 8 9 10 | bill=# explain select empno,ename,sal,deptno,
bill-# (select d.dname from dept d where d.deptno = e.deptno) as dname
bill-# from emp e;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on emp e (cost=0.00..15.84 rows=14 width=64)
SubPlan 1
-> Seq Scan on dept d (cost=0.00..1.05 rows=1 width=9)
Filter: (deptno = e.deptno)
(4 rows)
|
对于上面的SQL，emp表每输出一行数据，都要去dept表中全表扫描一遍。
而我们都知道，嵌套循环的被驱动表的连接列必须包含在索引中，同理，标量子查询的表的连接列也必须包含在索引中。但是我们在实际写SQL时还是要避免使用标量子查询，否则主表返回大量数据时，子表得被多次遍历，从而对SQL性能产生巨大影响。
那么对于标量子查询的SQL我们该怎么优化呢？最常用的就是改写成外连接，这样对于PostgreSQL的优化器而言可以根据实际情况去选择表的连接方式。这里需要注意的是，不能将标量子查询改成内连接，我们前面的例子中也可以看到，标量子查询实际是一个传值的过程，当主表传值给子表时，如果没有相应的值则会显示NULL，而如果使用内连接的话这部分数据就丢失了。
因此，上面的标量子查询可以改写成：
可以看到，优化器根据实际情况选择了更合适的hash join。
1 2 3 4 5 6 7 8 9 10 11 | bill=# explain select e.empno,e.ename,e.sal,e.deptno,d.dname
bill-# from emp e
bill-# left join dept d on (d.deptno = e.deptno);
QUERY PLAN
-------------------------------------------------------------------
Hash Left Join (cost=1.09..2.31 rows=14 width=27)
Hash Cond: (e.deptno = d.deptno)
-> Seq Scan on emp e (cost=0.00..1.14 rows=14 width=18)
-> Hash (cost=1.04..1.04 rows=4 width=13)
-> Seq Scan on dept d (cost=0.00..1.04 rows=4 width=13)
(5 rows)
|
当主表连接列是外键，而子表的连接列是主键时，使用内连接也可以，因为外键自然不会存在NULL值。
1 2 3 4 5 6 7 8 9 10 11 | bill=# explain select e.empno,e.ename,e.sal,e.deptno,d.dname
bill-# from emp e
bill-# inner join dept d on (d.deptno = e.deptno);
QUERY PLAN
-------------------------------------------------------------------
Hash Join (cost=1.09..2.31 rows=14 width=27)
Hash Cond: (e.deptno = d.deptno)
-> Seq Scan on emp e (cost=0.00..1.14 rows=14 width=18)
-> Hash (cost=1.04..1.04 rows=4 width=13)
-> Seq Scan on dept d (cost=0.00..1.04 rows=4 width=13)
(5 rows)
|
除了标量子查询外，往往filter也会产生类似的情况，因为在filter中驱动表也会被固定住，那么优化器可能会选择低效的执行计划。而对于PostgreSQL而言本身也不支持hint功能，如果错误的执行计划被固定，那么往往只能去改写SQL。
这里说明下下filter，在PostgreSQL中filter主要有2种情况，一种是我们常见的where后面过滤数据的，这种一般不会产生什么性能问题，例如：
1 2 3 4 5 6 | bill=# explain select * from t where id < 10;
QUERY PLAN
-------------------------------------------------------
Seq Scan on t (cost=0.00..16925.00 rows=100 width=4)
Filter: (id < 10)
(2 rows)
|
而另一种就是filter中是一些表的连接条件，这种呢便是我们前面说的情况，往往需要去关注的，例如：
1 2 3 4 5 6 7 8 | bill=# explain select exists (select 1 from t where t.id=n.id) from n;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on n (cost=0.00..169250145.00 rows=10000 width=1)
SubPlan 1
-> Seq Scan on t (cost=0.00..16925.00 rows=1 width=0)
Filter: (id = n.id)
(4 rows)
|
那么哪些写法会容易产生filter呢？在PostgreSQL中当使用exists或者not exists时，或者子查询中有固话子查询的关键词，如union、union all、cube、rollup、limit等，那么执行计划往往容易产生filter。
因此上面的SQL我们用in去替换exists进行改写：
1 2 3 4 5 6 7 8 | bill=# explain select id in (select id from t) from n;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on n (cost=0.00..129160170.00 rows=10000 width=1)
SubPlan 1
-> Materialize (cost=0.00..23332.00 rows=1000000 width=4)
-> Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4)
(4 rows)
|
除此之外，在PostgreSQL中我们更推荐使用= any的方式去改写该类SQL：
1 2 3 4 5 6 7 | bill=# explain select id = any(array(select id from t)) from n;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on n (cost=14425.00..14695.00 rows=10000 width=1)
InitPlan 1 (returns $0)
-> Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4)
(3 rows)
|
复制
当然这并不是说in的写法就一定比exists要好，只是相较于exists更不容易产生filter。这是为什么呢？因为如果子查询中包含我们上面提到的固化关键字时，子查询会被固化为一个整体，当采用exists写法时，如果子查询中有主表的连接列，那么便只能是主表通过连接列给子查询中的表传值，因此会选择filter。而使用in的写法，即使子查询被固化，但如果没有主表连接列的字段，那么便不会选择filter。
