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

最新推荐文章于 2024-02-28 06:00:20 发布

landcruiser5700

最新推荐文章于 2024-02-28 06:00:20 发布

阅读量2.1k

点赞数

本文链接：https://blog.csdn.net/landcruiser5700/article/details/42040955

版权

DB-version:

[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 说明，优化器在逻辑优化部分将
查询语句重写为两表连接。