pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedStrange performance boost with random()
4+ messages / 4 participants
[nested] [flat]
* Strange performance boost with random()
@ 2014-02-10 19:52 M Putz <puma@c007.de>
2014-02-10 20:03 ` Re: Strange performance boost with random() Heikki Linnakangas <hlinnakangas@vmware.com>
0 siblings, 1 reply; 4+ messages in thread
From: M Putz @ 2014-02-10 19:52 UTC (permalink / raw)
To: pgsql-performance
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?
Thanks, M. Putz
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strange performance boost with random()
2014-02-10 19:52 Strange performance boost with random() M Putz <puma@c007.de>
@ 2014-02-10 20:03 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2014-02-11 15:21 ` Re: Strange performance boost with random() Sébastien Lorion <sl@thestrangefactory.com>
0 siblings, 1 reply; 4+ messages in thread
From: Heikki Linnakangas @ 2014-02-10 20:03 UTC (permalink / raw)
To: M Putz <puma@c007.de>; +Cc: pgsql-performance
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strange performance boost with random()
2014-02-10 19:52 Strange performance boost with random() M Putz <puma@c007.de>
2014-02-10 20:03 ` Re: Strange performance boost with random() Heikki Linnakangas <hlinnakangas@vmware.com>
@ 2014-02-11 15:21 ` Sébastien Lorion <sl@thestrangefactory.com>
2014-02-11 15:37 ` Re: Strange performance boost with random() Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Sébastien Lorion @ 2014-02-11 15:21 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnakangas@vmware.com>; +Cc: M Putz <puma@c007.de>; pgsql-performance
On Mon, Feb 10, 2014 at 3:03 PM, Heikki Linnakangas <hlinnakangas@vmware.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@postgresql.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strange performance boost with random()
2014-02-10 19:52 Strange performance boost with random() M Putz <puma@c007.de>
2014-02-10 20:03 ` Re: Strange performance boost with random() Heikki Linnakangas <hlinnakangas@vmware.com>
2014-02-11 15:21 ` Re: Strange performance boost with random() Sébastien Lorion <sl@thestrangefactory.com>
@ 2014-02-11 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2014-02-11 15:37 UTC (permalink / raw)
To: Sébastien Lorion <sl@thestrangefactory.com>; +Cc: Heikki Linnakangas <hlinnakangas@vmware.com>; M Putz <puma@c007.de>; pgsql-performance
=?UTF-8?Q?S=C3=A9bastien_Lorion?= <sl@thestrangefactory.com> writes:
> That's interesting .. Does PostgreSQL always use the NUMERIC data type for
> constants in absence of cast ?
If they're not integers, yes, that's the initial assumption.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-02-11 15:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-02-10 19:52 Strange performance boost with random() M Putz <puma@c007.de>
2014-02-10 20:03 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2014-02-11 15:21 ` Sébastien Lorion <sl@thestrangefactory.com>
2014-02-11 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox