Re: [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates

<div dir="ltr"><div class="gmail_extra"><br /><div class="gmail_quote">On Sat, Oct 25, 2014 at 12:38 PM, Andreas
Karlsson<span dir="ltr"><<a href="mailto:andreas@proxel.se" target="_blank">andreas@proxel.se</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br
/><br/> There was recently talk about if we should start using 128-bit integers (where available) to speed up the
aggregatefunctions over integers which uses numeric for their internal state. So I hacked together a patch for this to
seewhat the performance gain would be.<br /><br /> Previous thread: <a
href="http://www.postgresql.org/message-id/20141017182500.GF2075@alap3.anarazel.de"
target="_blank">http://www.postgresql.org/<u></u>message-id/20141017182500.<u></u>GF2075@alap3.anarazel.de</a><br/><br
/>What the patch does is switching from using numerics in the aggregate state to int128 and then convert the type from
the128-bit integer in the final function.<br /><br /> The functions where we can make use of int128 states are:<br
/><br/> - sum(int8)<br /> - avg(int8)<br /> - var_*(int2)<br /> - var_*(int4)<br /> - stdev_*(int2)<br /> -
stdev_*(int4)<br/><br /> The initial benchmark results look very promising. When summing 10 million int8 I get a
speedupof ~2.5x and similarly for var_samp() on 10 million int4 I see a speed up of ~3.7x. To me this indicates that it
isworth the extra code. What do you say? Is this worth implementing?<br /><br /> The current patch still requires work.
Ihave not written the detection of int128 support yet, and the patch needs code cleanup (for example: I used an int16_
prefixon the added functions, suggestions for better names are welcome). I also need to decide on what estimate to use
forthe size of that state.<br /><br /> The patch should work and pass make check on platforms where __int128_t is
supported.<br/><br /> The simple benchmarks:<br /><br /> CREATE TABLE test_int8 AS SELECT x::int8 FROM
generate_series(1,10000000) x;<br /><br /> Before:<br /><br /> # SELECT sum(x) FROM test_int8;<br />       sum<br />
----------------<br/>  50000005000000<br /> (1 row)<br /><br /> Time: 2521.217 ms<br /><br /> After:<br /><br /> #
SELECTsum(x) FROM test_int8;<br />       sum<br /> ----------------<br />  50000005000000<br /> (1 row)<br /><br />
Time:1022.811 ms<br /><br /> CREATE TABLE test_int4 AS SELECT x::int4 FROM generate_series(1, 10000000) x;<br /><br />
Before:<br/><br /> # SELECT var_samp(x) FROM test_int4;<br />       var_samp<br /> --------------------<br />
 8333334166666.6667<br/> (1 row)<br /><br /> Time: 3808.546 ms<br /><br /> After:<br /><br /> # SELECT var_samp(x) FROM
test_int4;<br/>       var_samp<br /> --------------------<br />  8333334166666.6667<br /> (1 row)<br /><br /> Time:
1033.243ms<span class="HOEnZb"><font color="#888888"><br /><br /> Andreas<br /></font></span><br /><br /> --<br /> Sent
viapgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br /> To
makechanges to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/><br /></blockquote></div><br /></div><div
class="gmail_extra">Theseare some nice improvements.<br /><br /></div><div class="gmail_extra">As far as I'm aware
int128types are supported on every major compiler when compiling for 64bit platforms. Right?<br /></div></div>