PostgreSQL 利用编译器extension 支持int128，提升聚合性能

2022-06-06 10:29:40

背景

PostgreSQL 9.4以及以前的版本，在INT，INT2，INT8的聚合计算中，为了保证数据不会溢出，中间结果使用numeric来存储。

numeric是PostgreSQL自己实现的一种数值类型，可以存储非常大的数值(估计是做科学计算的需求)，但是牺牲了一定的性能。

为了提高聚合，特别是大数据量的聚合时的性能，社区借用了编译器支持的int128类型，作为数据库int, int2, int8的中间计算结果，从而提升计算性能。

编译器相关的解释

gcc,clang,icc都支持int128

1. gcc

6.8 128-bit Integers

As an extension the integer scalar type __int128 is supported for targets which have an integer mode wide enough to hold 128 bits. 

Simply write __int128 for a signed 128-bit integer, or unsigned __int128 for an unsigned 128-bit integer. 

There is no support in GCC for expressing an integer constant of type __int128 for targets with long long integer less than 128 bits wide.

2. icc

From what I can tell, at least icc 13.0.1+ support __int128_t and __uint128_t. Courtesy of Matt Godbolt's Compiler Explorer:

__int128_t ai (__int128_t x, __int128_t y) {
  return x + y;
}

__int128_t mi (__int128_t x, __int128_t y) {
  return x * y;
}

__int128_t di (__int128_t x, __int128_t y) {
  return x / y;
}

__int128_t ri (__int128_t x, __int128_t y) {
  return x % y;
}
compiles to:

L__routine_start_ai_0:
ai:
        add       rdi, rdx                                      #2.14
        mov       rax, rdi                                      #2.14
        adc       rsi, rcx                                      #2.14
        mov       rdx, rsi                                      #2.14
        ret                                                     #2.14
L__routine_start_mi_1:
mi:
        mov       rax, rdi                                      #6.14
        imul      rsi, rdx                                      #6.14
        imul      rcx, rdi                                      #6.14
        mul       rdx                                           #6.14
        add       rsi, rcx                                      #6.14
        add       rdx, rsi                                      #6.14
        ret                                                     #6.14
L__routine_start_di_2:
di:
        push      rsi                                           #9.44
        call      __divti3                                      #10.14
        pop       rcx                                           #10.14
        ret                                                     #10.14
L__routine_start_ri_3:
ri:
        push      rsi                                           #13.44
        call      __modti3                                      #14.14
        pop       rcx                                           #14.14
        ret                                                     #14.14
with icc 13.0.1 (http://goo.gl/UnxEFt).

PostgreSQL int128支持

编译时根据编译器的特性自动判断是否使用int128特性.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8122e1437e332e156d971a0274879b0ee76e488a

Add, optional, support for 128bit integers.

We will, for the foreseeable future, not expose 128 bit datatypes to
SQL. But being able to use 128bit math will allow us, in a later patch,
to use 128bit accumulators for some aggregates; leading to noticeable
speedups over using numeric.

So far we only detect a gcc/clang extension that supports 128bit math,
but no 128bit literals, and no *printf support. We might want to expand
this in the future to further compilers; if there are any that that
provide similar support.


config/c-compiler.m4        diff | blob | blame | history
configure       diff | blob | blame | history
configure.in        diff | blob | blame | history
src/include/c.h     diff | blob | blame | history
src/include/pg_config.h.in      diff | blob | blame | history
src/include/pg_config.h.win32       diff | blob | blame | history

性能提升测试

There was recently talk about if we should start using 128-bit integers
(where available) to speed up the aggregate functions over integers
which uses numeric for their internal state. So I hacked together a
patch for this to see what the performance gain would be.

Previous thread:
http://www.postgresql.org/message-id/20141017182500.GF2075@alap3.anarazel.de

What the patch does is switching from using numerics in the aggregate
state to int128 and then convert the type from the 128-bit integer in
the final function.

The functions where we can make use of int128 states are:

- sum(int8)
- avg(int8)
- var_*(int2)
- var_*(int4)
- stdev_*(int2)
- stdev_*(int4)

The initial benchmark results look very promising. When summing 10
million int8 I get a speedup of ~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 is
worth the extra code. What do you say? Is this worth implementing?

The current patch still requires work. I have not written the detection
of int128 support yet, and the patch needs code cleanup (for example: I
used an int16_ prefix on the added functions, suggestions for better
names are welcome). I also need to decide on what estimate to use for
the size of that state.

The patch should work and pass make check on platforms where __int128_t
is supported.

The simple benchmarks:

CREATE TABLE test_int8 AS SELECT x::int8 FROM generate_series(1, 
10000000) x;

Before:

# SELECT sum(x) FROM test_int8;
       sum
----------------
  50000005000000
(1 row)

Time: 2521.217 ms

After:

# SELECT sum(x) FROM test_int8;
       sum
----------------
  50000005000000
(1 row)

Time: 1022.811 ms

CREATE TABLE test_int4 AS SELECT x::int4 FROM generate_series(1, 
10000000) x;

Before:

# SELECT var_samp(x) FROM test_int4;
       var_samp
--------------------
  8333334166666.6667
(1 row)

Time: 3808.546 ms

After:

# SELECT var_samp(x) FROM test_int4;
       var_samp
--------------------
  8333334166666.6667
(1 row)

Time: 1033.243 ms