| From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
|---|---|
| To: | Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Prove a NOT IN's left-hand expressions non-nullable from quals |
| Date: | 2026-06-19 01:13:25 |
| Message-ID: | CAMbWs4_TNUs1jn7q0J-=Esz7ziiFdjDAtW4x2u6tv6H5hhmhDA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
383eb21eb teaches the planner to convert "x NOT IN (SELECT ...)" to an
anti-join when both sides of the comparison are known to be non-null.
On the outer side we currently get that only from NOT NULL constraints
and the outer-join-aware-Var infrastructure.
That misses a case: the left-hand column has no NOT NULL constraint,
but a qual forces it non-null anyway, as in "x IS NOT NULL AND x NOT
IN (...)" or "x > 0 AND x NOT IN (...)". We leave those as SubPlan
filters today, even though x clearly can't be NULL where the NOT IN is
evaluated.
The attached patch proves the left-hand Var non-null from such a qual.
pull_up_sublinks_jointree_recurse collects the quals at or below the
NOT IN's jointree node (only those on rels not below the nullable side
of an outer join, so they really do filter the rows) and hands them to
convert_ANY_sublink_to_join, which checks them with
find_nonnullable_vars.
Quals above the NOT IN's node could help in some cases too, but that's
a separate extension and I've left it as a follow-up. See the details
in the draft commit message.
Thoughts?
- Richard
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Prove-a-NOT-IN-s-left-hand-expressions-non-nullab.patch | application/octet-stream | 34.5 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Chao Li | 2026-06-19 01:35:34 | Re: Fix \crosstabview to honor \pset display_true/display_false |
| Previous Message | Henson Choi | 2026-06-19 00:43:10 | Re: Row pattern recognition |