Re: Strange performance boost with random()

On Mon, Feb 10, 2014 at 3:03 PM, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com
> wrote:

> On 02/10/2014 09:52 PM, M Putz wrote:
>
>>
>> Hello,
>>
>> While analyzing performance, we encountered the following phenomenon,
>>
>> SELECT sum(pow(.5*generate_series,.5))
>> FROM generate_series(1,1000000);
>>
>> is much much (a hundred times) slower than
>>
>> SELECT sum(pow(random()*generate_series,.5))
>> FROM generate_series(1,1000000);
>>
>> and asymptotic difference is even more astounding.
>> This seems counter-intuitive, considering the cost of
>> an additional random() call instead of a constant factor.
>> What are the reasons for this strange performance boost?
>>
>
> Different data type. The first uses numeric, which is pretty slow for
> doing calculations. random() returns a double, which makes the pow and sum
> to also use double, which is a lot faster.
>
> To see the effect, try these variants:
>
> SELECT sum(pow(.5::float8 * generate_series,.5))
> FROM generate_series(1,1000000);
>
> SELECT sum(pow(random()::numeric * generate_series,.5))
> FROM generate_series(1,1000000);
>
> - Heikki
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

That's interesting .. Does PostgreSQL always use the NUMERIC data type for
constants in absence of cast ?

Sébastien