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

On Wed, Sep 23, 2020 at 12:48 PM Tom Lane <tgl@sss.pgh.pa.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