Richard Guo <guofengli...@gmail.com> 于2025年2月26日周三 17:46写道：
> On Tue, Feb 25, 2025 at 1:30 AM Robert Haas <robertmh...@gmail.com> wrote: > > On Mon, Feb 24, 2025 at 8:08 AM wenhui qiu <qiuwenhu...@gmail.com> > wrote: > > > Actually ,Many fork postgresql databases have already implemented > ,For example, if the relevant field has a non-null constraint ，Many > databases can do the same thing as not exist （ MySQL ，SQL Server，Oracle） > > > I'm not surprised to hear it. Long-time PostgreSQL users just don't > > use NOT IN, so it's fine, but anyone coming from another database gets > > hosed. I think it would be good to put some effort into improving this > > area, but I do not have time to work on it myself. > > I agree that it'd be beneficial to make some improvements to NOT IN > subqueries. From what I can see, we may have two potential options: > > * As Tom mentioned, we can prove that the subquery's output never > contains NULL values and then convert the NOT IN into an anti-join. > (It seems to me that we would also need to prove that the outer side > never contains NULL values either, because whether the NULL values > from the outer side should be included in the output depends on > whether the inner side is empty.) > > * We can add support in the executor to handle the NULL semantics of > NOT IN. This may require inventing a new join type. > > I'm not quite sure which option is more promising at the moment, or if > there are other options to consider. > > Recently, I found Greenplum implement pull-up NOT IN subquery. They have the below comments in their codes: We normalize NOT subqueries using the following axioms: * * val NOT IN (subq) => val <> ALL (subq) Richard, do you have an impression about this? -- Thanks, Tender Wang
