From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Check SubPlan clause for nonnullable rels/Vars |
Date: | 2022-09-11 10:42:03 |
Message-ID: | CAMbWs4-jV=199A2Y_6==99dYnpnmaO_Wz_RGkRTTaCB=Pihw2w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi hackers,
While wandering around the codes of reducing outer joins, I noticed that
when determining which base rels/Vars are forced nonnullable by given
clause, we don't take SubPlan into consideration. Does anyone happen to
know what is the concern behind that?
IMO, for SubPlans of type ALL/ANY/ROWCOMPARE, we should be able to find
additional nonnullable rels/Vars by descending through their testexpr.
As we know, ALL_SUBLINK/ANY_SUBLINK combine results across tuples
produced by the subplan using AND/OR semantics. ROWCOMPARE_SUBLINK
doesn't allow multiple tuples from the subplan. So we can tell whether
the subplan is strict or not by checking its testexpr, leveraging the
existing codes in find_nonnullable_rels/vars_walker. Below is an
example:
# explain (costs off)
select * from a left join b on a.i = b.i where b.i = ANY (select i from c
where c.j = b.j);
QUERY PLAN
-----------------------------------
Hash Join
Hash Cond: (b.i = a.i)
-> Seq Scan on b
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on c
Filter: (j = b.j)
-> Hash
-> Seq Scan on a
(9 rows)
BTW, this change would also have impact on SpecialJoinInfo, especially
for the case of identity 3, because find_nonnullable_rels() is also used
to determine strict_relids from the clause. As an example, consider
select * from a left join b on a.i = b.i
left join c on b.j = ANY (select j from c);
Now we can know the SubPlan is strict for 'b'. Thus the b/c join would
be considered to be legal.
Thanks
Richard
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Check-SubPlan-clause-for-nonnullable-rels-Vars.patch | application/octet-stream | 4.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-09-11 14:53:22 | Re: [PATCH] initdb: do not exit after warn_on_mount_point |
Previous Message | Julien Rouhaud | 2022-09-11 10:17:47 | Re: [PATCH] initdb: do not exit after warn_on_mount_point |