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

Hi Tom,

Thanks for your input.

On Fri, Nov 16, 2018 at 6:06 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

Agree! If we do expression simplification in the other order, we have to do
that twice,
which is not a good idea.

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

I am convinced. I do not see this happen often enough in real world neither.
Feel free to reject his patch.

Thanks
Richard