Re: Pull up sublink of type 'NOT NOT (expr)'

Hi Tom,

Thanks for reviewing.

On Tue, Nov 13, 2018 at 10:05 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Richard Guo <riguo(at)pivotal(dot)io> writes:
> > Currently for quals in the form of "NOT NOT (SubLink)", this SubLink
> would
> > not be considered when pulling up sublinks.
>
> Yup.
>
> > Should we give it a chance, like the attached does?
>
> What is the argument that this occurs often enough to be worth expending
> extra cycles and code space on?
>
> If we do do something like this, I'd be inclined to make it handle
> any-number-of-consecutive-NOTs, and maybe remove NOT NOT over an ANY,
> not just EXISTS. But I don't honestly think that it's worth troubling
> over. Do even the dumbest ORMs generate such code?
>

What this patch does is to recursively remove NOT NOT over a SubLink, so it
actually can handle any-number-of-consecutive-NOTs, both over ANY and over
EXISTS.

Over ANY:

gpadmin=# explain select * from a where not not not not a.i in (select i
from b);
QUERY PLAN
-----------------------------------------------------------------------
Hash Join (cost=42.75..93.85 rows=1130 width=8)
Hash Cond: (a.i = b.i)
-> Seq Scan on a (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=40.25..40.25 rows=200 width=4)
-> HashAggregate (cost=38.25..40.25 rows=200 width=4)
Group Key: b.i
-> Seq Scan on b (cost=0.00..32.60 rows=2260 width=4)
(7 rows)

Over EXISTS:

gpadmin=# explain select * from a where not not not not exists (select 1
from b where a.i = b.i);
QUERY PLAN
-----------------------------------------------------------------------
Hash Join (cost=42.75..93.85 rows=1130 width=8)
Hash Cond: (a.i = b.i)
-> Seq Scan on a (cost=0.00..32.60 rows=2260 width=8)
-> Hash (cost=40.25..40.25 rows=200 width=4)
-> HashAggregate (cost=38.25..40.25 rows=200 width=4)
Group Key: b.i
-> Seq Scan on b (cost=0.00..32.60 rows=2260 width=4)
(7 rows)

I am not using an ORM, but just considering maybe it would be better if
PostgreSQL can do such pull-up.
Tom, what's your suggestion? Is it worthwhile expending several lines of
codes to do this pull-up?

Thanks
Richard

>
>