DB-version:
test SQL:
QUERY PLAN:
我们可以看出,执行计划里面出现了(x.x_num = y.y_num),这是在原SQL语句里面所没有的,Hash Join 说明,优化器在逻辑优化部分将
查询语句重写为两表连接。
[postgres@CentOS ~]$ postgres --version
postgres (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 说明,优化器在逻辑优化部分将
查询语句重写为两表连接。