postgresql对EXISTS类型查询优化

关系准备：

CREATE TABLE t_1(
	t_1_id INT UNIQUE,
	t_1_col_1 INT,
	t_1_col_2 VARCHAR(10));

CREATE TABLE t_2(
	t_2_id INT UNIQUE,
	t_2_col_1 INT,
	t_2_col_2 VARCHAR(10));

数据准备:

INSERT INTO t_1 VALUES(1,11,'t_1_1');
INSERT INTO t_1 VALUES(2,12,NULL);
INSERT INTO t_1 VALUES(3,NULL,'t_1_3');
INSERT INTO t_1 VALUES(4,14,'t_1_4');
INSERT INTO t_1 VALUES(5,15,NULL);
INSERT INTO t_1 VALUES(7,NULL,NULL);
INSERT INTO t_2 VALUES(1,11,'t_2_1');
INSERT INTO t_2 VALUES(2,NULL,'t_2_2');
INSERT INTO t_2 VALUES(3,13,NULL);
INSERT INTO t_2 VALUES(4,14,'t_2_4');
INSERT INTO t_2 VALUES(6,16,'t_2_6');
INSERT INTO t_2 VALUES(7,NULL,NULL);

SQL准备:

//EXISTS类型的普通相关子查询，子查询条件和父查询相关联：
(4).SELECT t_1.* FROM t_1 WHERE EXISTS(SELECT t_2_id FROM t_2 WHERE t_1_id=t_2_id);
//EXISTS类型的普通子相关子查询，子查询条件和子查询没有关系：
(5).SELECT t_1.* FROM t_1 WHERE EXISTS(SELECT t_2_id FROM t_2 WHERE t_1_id=10);
//EXISTS类型的普通非相关子查询
(6).SELECT t_1.* FROM t_1 WHERE EXISTS(SELECT t_2_id FROM t_2 WHERE t_2_id=10);
//EXISTS类型的普通非相关子查询，子查询简单没有表存在；
(7).SELECT t_1.* FROM t_1 WHERE EXISTS(SELECT 10);

测试1:

uu=# EXPLAIN SELECT t_1.* FROM t_1 WHERE EXISTS(SELECT t_2_id FROM t_2 WHERE t_1_id=t_2_id);
                            QUERY PLAN                             
-------------------------------------------------------------------
 Hash Semi Join  (cost=34.08..69.49 rows=1070 width=46)
   Hash Cond: (t_1.t_1_id = t_2.t_2_id)
   ->  Seq Scan on t_1  (cost=0.00..20.70 rows=1070 width=46)
   ->  Hash  (cost=20.70..20.70 rows=1070 width=4)
         ->  Seq Scan on t_2  (cost=0.00..20.70 rows=1070 width=4)
(5 rows)

        查询优化器对查询进行了查询重写，通过子查询上拉技术，把子查询转换为使用t_1.t_1_id = t_2.t_2_id作为连接条件实现hash半连接(hash semi join)操作。如果不做优化，则t_1表有多少条记录，都需要扫描t_2表多少次，每次都对t_2做全表扫描，这样的操作是非常昂贵的操作；优化后，只需对t_2进行一次全表扫描，这样大大节省了成本值，然后采用 Hash Semi Join 连接算法。

测试2：

uu=# EXPLAIN SELECT t_1.* FROM t_1 WHERE EXISTS(SELECT t_2_id FROM t_2 WHERE t_1_id=10);
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=0.00..28.99 rows=1 width=46)
   ->  Index Scan using t_1_t_1_id_key on t_1  (cost=0.00..8.27 rows=1 width=46)
         Index Cond: (t_1_id = 10)
   ->  Seq Scan on t_2  (cost=0.00..20.70 rows=1070 width=0)
(4 rows)

        优化器对查询SQL语句进行了优化。通过子查询上拉技术，对子查询t_2做一个顺序扫描，然后通过索引方式访问t_1的索引，最后将t_1结果集与t_2结果集做嵌套循环半连接。

测试3:

uu=# EXPLAIN SELECT t_1.* FROM t_1 WHERE EXISTS(SELECT t_2_id FROM t_2 WHERE t_2_id=10);
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Result  (cost=8.27..28.97 rows=1070 width=46)
   One-Time Filter: $0
   InitPlan 1 (returns $0)
     ->  Index Only Scan using t_2_t_2_id_key on t_2  (cost=0.00..8.27 rows=1 width=0)
           Index Cond: (t_2_id = 10)
   ->  Seq Scan on t_1  (cost=0.00..20.70 rows=1070 width=46)
(6 rows)

        由于该查询是非相关子查询，我们只需对表t_2做一次扫描，验证子查询结果是TRUE还是FALSE，所以不会执行多次，没有必要优化，按照SQL语句意思走即可。

测试4:

uu=# EXPLAIN SELECT t_1.* FROM t_1 WHERE EXISTS(SELECT 10);
                          QUERY PLAN                          
--------------------------------------------------------------
 Result  (cost=0.01..20.71 rows=1070 width=46)
   One-Time Filter: $0
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=0)
   ->  Seq Scan on t_1  (cost=0.00..20.70 rows=1070 width=46)
(5 rows)

      只需对子查询求解 TRUE还是FALSE即可。