Обсуждение: Pull up sublink of type 'NOT NOT (expr)'

Hello Richard,

Currently for quals in the form of "NOT NOT (SubLink)", this SubLink would not

be considered when pulling up sublinks. For instance:

gpadmin=# explain select * from a where NOT NOT (a.i in (select b.i from b));

                         QUERY PLAN

-------------------------------------------------------------

 Seq Scan on a  (cost=51.50..85.62 rows=1005 width=8)

   Filter: (hashed SubPlan 1)

   SubPlan 1

     ->  Seq Scan on b  (cost=0.00..44.00 rows=3000 width=4)

(4 rows)

Should we give it a chance, like the attached does?

Sometimes hashed subplan is faster than hash join and than all the other options, as it preserves the order.
Using NOT NOT, one can control whether to use it or not:
https://pgblog.bashtanov.com/2017/12/08/double-negative-and-query-performance/ (test case and results in the bottom of the page).

Surely dirty tricks should not be the way to control the planner, but when breaking them we should probably provide a way to achieve the same result,
ideally making the planner choose the best plan without hints.

Best,
  Alex

Richard Guo <riguo@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?

            regards, tom lane

Richard Guo <riguo@pivotal.io> writes:
> On Tue, Nov 13, 2018 at 10:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> What is the argument that this occurs often enough to be worth expending
>> extra cycles and code space on?

> 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?

Well, really, if we're just doing this on speculation and don't even have
any concrete indication that anybody writes code like that, I can't get
excited about expending even a few lines of code on it.

The reason why we perform optimizations similar to this in places like
eval_const_expressions is (IMO anyway) that transformations such as
function inlining and subquery pullup can create parse trees that look
like this, even when the original query was perfectly sane and without
obvious redundancy.  However, because pull_up_sublinks runs before any
of that, it would only ever see NOT NOT if someone had actually written
such a thing.  So to justify expending any code space or cycles on
optimizing this, you have to make the case that that actually happens
in the wild, and does so often enough to justify wasting some (admittedly
small) number of cycles for everybody else.  I'd kind of like to see some
actual field occurrence of NOT NOT over an optimizable IN/EXISTS before
deciding that it's worth doing.

It's sort of annoying that we have to run pull_up_sublinks before we do
scalar expression simplification.  If we could do that in the other order,
NOT NOT elimination would fall out automatically, and we'd also be able
to recognize some other cases where it initially seems that an IN or
EXISTS is not at top level, but it becomes so after we const-fold, apply
DeMorgan's laws, etc.  However, according to the point I made above,
it makes more sense to apply expression simplification after we've
completed pullup-like operations.  So we can't really get there from
here unless we wanted to do (parts of?) expression simplification twice,
which is unlikely to win often enough to justify the cost.

So I'm inclined to reject this patch as probably being a net loss in
almost all cases.  If you can show any real-world cases where it wins,
we could reconsider.

            regards, tom lane