Re: [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates
От | Arthur Silva |
---|---|
Тема | Re: [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates |
Дата | |
Msg-id | CAO_YK0X2FYVwfJ7Cv+xwk2CTH36dnyxu2VHbEEEuBg0UnxCwGA@mail.gmail.com обсуждение исходный текст |
Ответ на | [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates (Andreas Karlsson <andreas@proxel.se>) |
Ответы |
Re: [WIP Patch] Using 128-bit integers for sum, avg and
statistics aggregates
(Andres Freund <andres@2ndquadrant.com>)
Re: [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates (Andreas Karlsson <andreas@proxel.se>) |
Список | pgsql-hackers |
<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>
В списке pgsql-hackers по дате отправления:
Следующее
От: Robert HaasДата:
Сообщение: Re: Directory/File Access Permissions for COPY and Generic File Access Functions