postgresql对IN子查询的处理

关系准备:

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准备:

//简单的IN子查询
(1).SELECT t_1.* FROM t_1 WHERE t_1_id IN(SELECT t_2_id FROM t_2);
//简单IN子查询但子查询结果不影响父查询
(2).SELECT t_1.* FROM t_1 WHERE 10 IN(SELECT t_2_id FROM t_2);
//父查询中的易失函数影响子查询的优化
(3).SELECT t_1.* FROM t_1 WHERE t_1_id+div((random()*10)::numeric,2)IN(SELECT t_2_id FROM t_2);

测试1：

uu=# EXPLAIN SELECT t_1.* FROM t_1 WHERE t_1_id IN(SELECT t_2_id FROM t_2);
                            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)

查询优化器对SQL语句进行了查询重写，将IN子查询改为了hadh半连接(Hash Semi Join),连接条件:t_1.t_1_id = t_2.t_2_id。如果不进行查询重写，那么t_1有多少条记录，那么就需要全表扫描t_2多少次，这样的执行代价是非常高的，全表扫描对多数数据库来说是非常昂贵的操作。经过查询重写后，只需对t_2做一次全表扫描。

测试2：

uu=# EXPLAIN SELECT t_1.* FROM t_1 WHERE 10 IN(SELECT t_2_id FROM t_2);
                          QUERY PLAN                           
---------------------------------------------------------------
 Result  (cost=23.38..44.08 rows=1070 width=46)
   One-Time Filter: (hashed SubPlan 1)
   ->  Seq Scan on t_1  (cost=23.38..44.08 rows=1070 width=46)
   SubPlan 1
     ->  Seq Scan on t_2  (cost=0.00..20.70 rows=1070 width=4)
(5 rows)

优化器没有对查询进行改写，子查询与父查询没有关系。对子查询进行一次全表扫描即可，对IN子查询的左操作符是常量，所以子查询直接求值。

测试3：

uu=# EXPLAIN SELECT t_1.* FROM t_1 WHERE t_1_id+div((random()*10)::numeric,2)IN(SELECT t_2_id FROM t_2);
                          QUERY PLAN                           
---------------------------------------------------------------
 Seq Scan on t_1  (cost=23.38..65.47 rows=535 width=46)
   Filter: (hashed SubPlan 1)
   SubPlan 1
     ->  Seq Scan on t_2  (cost=0.00..20.70 rows=1070 width=4)
(4 rows)

由于存在random()，要子查询返回的结果集不确定，所以没办法查询优化。