postgresql中IN子查询改写为hash join连接

DB-version:

[postgres@CentOS ~]$ postgres --versionpostgres (PostgreSQL) 9.2.3

Relations are as follows:

uu=# \d x              Table "public.x" Column |         Type          | Modifiers --------+-----------------------+----------- x_num  | integer               |  x_name | character varying(32) | uu=# \d y              Table "public.y" Column |         Type          | Modifiers --------+-----------------------+----------- y_num  | integer               |  y_name | character varying(32) | 

test SQL:

SELECT * FROM X WHERE x_num IN(SELECT y_num FROM y)

QUERY PLAN:

 uu=# EXPLAIN SELECT * FROM X WHERE x_num IN(SELECT y_num FROM y);                              QUERY PLAN                              ---------------------------------------------------------------------- Hash Join  (cost=23.25..49.88 rows=350 width=86)   Hash Cond: (x.x_num = y.y_num)   ->  Seq Scan on x  (cost=0.00..17.00 rows=700 width=86)   ->  Hash  (cost=20.75..20.75 rows=200 width=4)         ->  HashAggregate  (cost=18.75..20.75 rows=200 width=4)               ->  Seq Scan on y  (cost=0.00..17.00 rows=700 width=4)(6 rows)

我们可以看出，执行计划里面出现了(x.x_num = y.y_num)，这是在原SQL语句里面所没有的，Hash Join 说明，优化器在逻辑优化部分将
查询语句重写为两表连接。