Re: How to do faster DML

On 2024-02-16 01:34:01 +0100, Peter J. Holzer wrote:
> On 2024-02-15 16:51:56 -0700, David G. Johnston wrote:
> > What you see with an exact type is what you get, which allows for implementing
> > equality, unlike inexact which requires epsilon checking.
>
> You can check binary fp values for equality. If they are equal, they
> will compare equal. If they aren't, they won't.
>
> What you can't expect is that the laws of commutativity, associativity,
> etc. hold. If you compute a value in two different ways which should be
> equivalent mathematically (e.g. a*b/c vs. a/c*b), the result may be
> different and an equality test may fail. But that is also the case for
> numeric (and of course integer).

To illustrate that point:

hjp=> create table t_n (a numeric, b numeric, c numeric);
CREATE TABLE

hjp=> insert into t_n values(47, 52, 10);
INSERT 0 1

-- the numbers are not specially chosen. I just invoked
-- select (random()*100)::int;
-- three times, and they were the ones that came up.

hjp=> select *, a / b * c, a * c / b, a / b * c = a * c / b from t_n;
╔════╤════╤════╤════════════════════════╤════════════════════╤══════════╗
║ a  │ b  │ c  │        ?column?        │      ?column?      │ ?column? ║
╟────┼────┼────┼────────────────────────┼────────────────────┼──────────╢
║ 47 │ 52 │ 10 │ 9.03846153846153846150 │ 9.0384615384615385 │ f        ║
╚════╧════╧════╧════════════════════════╧════════════════════╧══════════╝
(1 row)

So with type numeric two expressions which should be equal
mathematically, aren't in fact equal.

Now let's try the same thing with binary floating point:

hjp=> create table t_f (a float8, b float8, c float8);
CREATE TABLE

hjp=> insert into t_f values(47, 52, 10);
INSERT 0 1

hjp=> select *, a / b * c, a * c / b, a / b * c = a * c / b from t_f;
╔════╤════╤════╤═══════════════════╤═══════════════════╤══════════╗
║ a  │ b  │ c  │     ?column?      │     ?column?      │ ?column? ║
╟────┼────┼────┼───────────────────┼───────────────────┼──────────╢
║ 47 │ 52 │ 10 │ 9.038461538461538 │ 9.038461538461538 │ t        ║
╚════╧════╧════╧═══════════════════╧═══════════════════╧══════════╝
(1 row)

Now they are indeed equal. This is *not* guaranteed and I got a bit
lucky here, but the fact that I got lucky on the first try shows that
"float bad, numeric good" is not backed up by reality.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"