Re: More optimization effort?

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Another very similar (but possibly easier) case is:

> select * from pgbench_accounts where aid = 1.0;

> This will use a sequential scan rather than an index scan, because the
> query optimizer doesn't know that the only integer for which =(int4,
> numeric) will return true is 1. Therefore it has to scan the whole
> table one row at a time and check, for each one, whether the =
> operator returns true. It can't cast the constant to an integer
> because the user might have written 1.1 rather than 1.0, in which case
> the cast would fail; but the query should return 0 rows, not ERROR.

> You can imagine fixing this by having some kind of datatype-specific
> knowledge that would replace "aid = 1.0" with "aid = 1" and "aid =
> 1.1" with "false"; it would also have to know that "aid = 9999999999"
> should be changed to "false" because 9999999999 isn't representable as
> int4.

Couple thoughts about that:

* Another conceivable route to a solution is to add "int = numeric"
and friends to the btree opclasses for integers. I'm not sure if
there is any fundamental reason we've not done that (it's possible
it would fall foul of the requirements about transitivity, not sure).
However, this would only fix things to the extent of allowing an
index scan to occur; it wouldn't help in non-indexed cases, nor would
it know anything about simplifying say "aid = 1.1" to "false".

* The already-existing protransform infrastructure could be used to
address this type of problem; that is, you could imagine attaching
a transform function to numeric_eq that would look for cases like
"integer::numeric = nonintegralconstant" and simplify them accordingly.
When that feature went in, there was talk of using transforms to
simplify e.g. "variable + 0" or "variable * 1", but nobody's got round
to anything of the sort yet.

* On the other hand, protransform doesn't offer any easy way to apply
similar optimizations to a bunch of different functions/operators.
For instance, if your goal is to simplify "variable + 0", there are
a depressingly large number of "+" operators to write transform
functions for. Maybe there's no way around duplicate coding for that,
but it looks tedious and bulky.

> I have, however, decided not to volunteer to be the one who works on
> that project.

Me either. Any one of these things would require a *lot* of work in
order to have a coherent feature that provided useful behavior across
a bunch of different datatypes.

regards, tom lane