On Mon, Feb 10, 2014 at 3:03 PM, Heikki Linnakangas <hlinnakan...@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
