On Wed, Sep 23, 2020 at 4:49 PM Peter Geoghegan <pg@bowt.ie> wrote:
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.
