PostgreSQL在何处处理 sql查询之六十

开始

用 explain来看hash join:

postgres=# explain select dept.no_emps,emp.age from dept,emp where emp.name = dept.mgr and dept.dept_name = 'shoe';
                            QUERY PLAN                            
------------------------------------------------------------------
 Hash Join  (cost=19.30..45.07 rows=23 width=8)
   Hash Cond: ((emp.name)::text = (dept.mgr)::text)
   ->  Seq Scan on emp  (cost=0.00..21.30 rows=1130 width=42)
   ->  Hash  (cost=19.25..19.25 rows=4 width=42)
         ->  Seq Scan on dept  (cost=0.00..19.25 rows=4 width=42)
               Filter: ((dept_name)::text = 'shoe'::text)
(6 rows)

postgres=# 

我在代码里加点调试信息,出现的是这样的结果

---Before T_OpExpr, context is 
---Before T_RelabelType, context is (
---For T_RelabelType ,Before get_rule_expr_paren, context is (
---Before T_Var, context is ((
.......In get_Variable, levelsup is: 0
.......var->varno is: 65001
---Before T_Var, context is ((
.......In get_Variable, levelsup is: 0
.......var->varno is: 2
attnum is: 1
-----------------2 
-----------------3 
______________IN get_variable, attname is :name 
---After T_Var, context is ((emp.name
__________________________________________
---After T_Var, context is ((emp.name
__________________________________________
---For T_RelabelType ,After get_rule_expr_paren, context is ((emp.name)::text
---After T_RelabelType, context is ((emp.name)::text
__________________________________________
---Before T_RelabelType, context is ((emp.name)::text = 
---For T_RelabelType ,Before get_rule_expr_paren, context is ((emp.name)::text = 
---Before T_Var, context is ((emp.name)::text = (
.......In get_Variable, levelsup is: 0
.......var->varno is: 65000
---Before T_Var, context is ((emp.name)::text = (
.......In get_Variable, levelsup is: 0
.......var->varno is: 65001
---Before T_Var, context is ((emp.name)::text = (
.......In get_Variable, levelsup is: 0
.......var->varno is: 1
attnum is: 2
-----------------2 
-----------------3 
______________IN get_variable, attname is :mgr 
---After T_Var, context is ((emp.name)::text = (dept.mgr
__________________________________________
---After T_Var, context is ((emp.name)::text = (dept.mgr
__________________________________________
---After T_Var, context is ((emp.name)::text = (dept.mgr
__________________________________________
---For T_RelabelType ,After get_rule_expr_paren, context is ((emp.name)::text = (dept.mgr)::text
---After T_RelabelType, context is ((emp.name)::text = (dept.mgr)::text
__________________________________________
---After T_OpExpr, context is ((emp.name)::text = (dept.mgr)::text)
__________________________________________
---Before T_OpExpr, context is 
---Before T_RelabelType, context is (
---For T_RelabelType ,Before get_rule_expr_paren, context is (
---Before T_Var, context is ((
.......In get_Variable, levelsup is: 0
.......var->varno is: 1
attnum is: 1
-----------------2 
-----------------3 
______________IN get_variable, attname is :dept_name 
---After T_Var, context is ((dept_name
__________________________________________
---For T_RelabelType ,After get_rule_expr_paren, context is ((dept_name)::text
---After T_RelabelType, context is ((dept_name)::text
__________________________________________
---Before T_Const, context is ((dept_name)::text = 
---After T_Const, context is ((dept_name)::text = 'shoe'::text
__________________________________________
---After T_OpExpr, context is ((dept_name)::text = 'shoe'::text)
__________________________________________

实际上,explain面对的也是一个二叉树。

对于我的查询,

第一层:

根节点上的是 =,其NodeTag是 T_OpExpr ,其核心就是 = 符号。

左节点是: (emp.name)::text,右节点是:(dept.mgr)::text。

第二层:

对于 (emp.name)::text而言,其NodeTag 是 T_RelableType,其内容核心是  text。

对于(dept.mgr)::text 而言,其NodeTag 是 T_RelableType,其内容核心是  text。

 

第三层:

对于 (emp.name)而言,其NodeTag是 T_Var,然后会再进入一层,NodeTag仍然是 T_Var。

其核心是 变量 name。它是Outer表的变量。

 

对于 (dept.mgr)而言,其NodeTag是 T_Var,然后会再进入一层,NodeTag仍然是 T_Var。

其核心是 变量 mgr。它是Inner表的变量。

posted @ 2013-06-12 13:02  健哥的数据花园  阅读(337)  评论(0编辑  收藏  举报