When I was working on another task, the following case caught my mind.
create table t1(a int, b int, c int);
create table t2(a int, b int, c int);
create table t3(a int, b int, c int);
explain (costs off) select * from t1
where exists (select 1 from t2
where exists (select 1 from t3
where t3.c = t1.c
and t2.b = t3.b)
and t2.a = t1.a);
I got the plan like this:
QUERY PLAN
-----------------------------------
Hash Semi Join
Hash Cond: (t1.a = t2.a)
Join Filter: (hashed SubPlan 2)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
SubPlan 2
-> Seq Scan on t3
(8 rows)
Note we CAN'T pull up the inner sublink which produced the SubPlan 2.
I traced the reason is after we pull up the outer sublink, we got:
select * from t1 semi join t2 on t2.a = t1.a AND
exists (select 1 from t3
where t3.c = t1.c
and t2.b = t3.b);
Later we tried to pull up the EXISTS sublink to t1 OR t2
separately, since
this subselect referenced to t1
AND t2, so we CAN'T pull up the sublink. I
am thinking why we have to pull up it t1 OR t2 rather than JoinExpr(t1, t2),
I think the latter one is better.
So I changed the code like this, I got the plan I wanted and 'make
installcheck' didn't find any exception.
QUERY PLAN
------------------------------------------------
Hash Semi Join
Hash Cond: ((t2.b = t3.b) AND (t1.c = t3.c))
-> Hash Semi Join
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
(9 rows)
@@ -553,10 +553,10 @@ pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node,
*/
j->quals = pull_up_sublinks_qual_recurse(root,
j->quals,
- &j->larg,
- available_rels1,
- &j->rarg,
- child_rels);
+ jtlink1,
+ bms_union(available_rels1, child_rels),
+ NULL,
+ NULL);
/* Return NULL representing constant TRUE */
return NULL;
}
Any feedback is welcome.
--
