Hi Tom,
Thanks for reviewing.
On Tue, Nov 13, 2018 at 10:05 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Richard Guo <ri...@pivotal.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
>
>
