Re: BUG #16624: Query Optimizer - Performance bug related to predicate simplification

Thank you so much for the helpful discussion!

On Wed, Sep 23, 2020 at 4:49 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Wed, Sep 23, 2020 at 12:48 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > If you were really intent on making this happen, you could imagine
> > for instance attaching a SupportRequestSimplify hook to "int4eq",
> > which could transform "x - c1 = c2" into "x = c1 + c2" after verifying
> > that the constant values wouldn't produce overflow. But this idea
> > doesn't scale very well, since you'd really be talking about looking
> > for a whole bunch of different possible rearrangements not just one.
>
> Yeah. Apparently even sophisticated C compilers can only perform
> simpler algebraic reductions on integers, even though it probably
> matters a lot more than it would during database query optimization
> [1]. It's not as universal as you might think at first. Algebraic
> reductions are unsafe with floats because you risk losing precision. I
> bet that there are various subtle behaviors that get broken once you
> think it all through for each datatype.
>
> It's not that hard to create algebraic expressions involving integers
> that production quality C compilers cannot perform relatively obvious
> algebraic reductions on. I'm not an expert by any means, but I suspect
> that it's a surprisingly hard problem.
>
> As you said, the fix for this problem is don't do that. People that
> write SQL for a living all seem to figure that out on their own. It's
> not something we hear any complaints about.
>
> [1] https://www.agner.org/optimize/optimizing_cpp.pdf
> --
> Peter Geoghegan
>

--
-Xinyu