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

Richard Guo <riguo(at)pivotal(dot)io> writes:
> On Tue, Nov 13, 2018 at 10:05 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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