Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

James Cloos <cloos(at)jhcloos(dot)com> writes:
> "ST" == Shaun Thomas <sthomas(at)optionshouse(dot)com> writes:
> ST> That said, the documentation here says FLOAT4 is an alias for REAL,
> ST> so it's somewhat nonintuitive for FLOAT4 to be so much slower than
> ST> FLOAT8, which is an alias for DOUBLE PRECISION.

> There are some versions of glibc where doing certain math on double is
> faster than doing it on float, depending on how things are compiled.
> Maybe this is one of them?

No, it isn't. The problem here is that the result of SQRT() is
float8 (a/k/a double precision) while the variable that it is to
be assigned to is float4 (a/k/a real). As was noted upthread,
changing the variable's declared type to eliminate the run-time
type coercion removes just about all the discrepancy between PG
and Oracle runtimes. The original comparison is not apples-to-apples
because the Oracle coding required no type coercions. (Or at least,
so I assume; I'm not too familiar with Oracle's math functions.)

plpgsql is not efficient at all about coercions performed as a side
effect of assignments; if memory serves, it always handles them by
converting to text and back. So basically the added cost here came
from float8out() and float4in(). There has been some talk of trying
to do such coercions via SQL casts, but nothing's been done for fear
of compatibility problems.

regards, tom lane