Wsrtjtyk

create table t_num_type(

   n_bigint bigint,

   n_numeric numeric,

   n_int int

);



insert into t_num_type

select generate_series(1,10000000),

       generate_series(1,10000000),

       generate_series(1,10000000);

1» n_bigint

explain (analyze,buffers,format text)

select sum(n_bigint) from t_num_type;

Finalize Aggregate  (cost=116778.56..116778.57 rows=1 width=32) (actual time=1221.663..1221.664 rows=1 loops=1)

  Buffers: shared hit=23090

  ->  Gather  (cost=116778.34..116778.55 rows=2 width=32) (actual time=1221.592..1221.643 rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        Buffers: shared hit=23090

        ->  Partial Aggregate  (cost=115778.34..115778.35 rows=1 width=32) (actual time=1217.558..1217.559 rows=1 loops=3)

              Buffers: shared hit=63695

              ->  Parallel Seq Scan on t_num_type  (cost=0.00..105361.67 rows=4166667 width=8) (actual time=0.021..747.748 rows=3333333 loops=3)

                    Buffers: shared hit=63695

Planning time: 0.265 ms

Execution time: 1237.360 ms

2» numeric

explain (analyze,buffers,format text)

select sum(n_numeric) from t_num_type;

Finalize Aggregate  (cost=116778.56..116778.57 rows=1 width=32) (actual time=1576.562..1576.562 rows=1 loops=1)

  Buffers: shared hit=22108

  ->  Gather  (cost=116778.34..116778.55 rows=2 width=32) (actual time=1576.502..1576.544 rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        Buffers: shared hit=22108

        ->  Partial Aggregate  (cost=115778.34..115778.35 rows=1 width=32) (actual time=1572.446..1572.446 rows=1 loops=3)

              Buffers: shared hit=63695

              ->  Parallel Seq Scan on t_num_type  (cost=0.00..105361.67 rows=4166667 width=6) (actual time=0.028..781.808 rows=3333333 loops=3)

                    Buffers: shared hit=63695

Planning time: 0.157 ms

Execution time: 1592.559 ms

3» n_int

explain (analyze,buffers,format text)

select sum(n_int) from t_num_type;

Finalize Aggregate  (cost=116778.55..116778.56 rows=1 width=8) (actual time=1247.065..1247.065 rows=1 loops=1)

  Buffers: shared hit=23367

  ->  Gather  (cost=116778.33..116778.54 rows=2 width=8) (actual time=1247.006..1247.055 rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        Buffers: shared hit=23367

        ->  Partial Aggregate  (cost=115778.33..115778.34 rows=1 width=8) (actual time=1242.524..1242.524 rows=1 loops=3)

              Buffers: shared hit=63695

              ->  Parallel Seq Scan on t_num_type  (cost=0.00..105361.67 rows=4166667 width=4) (actual time=0.028..786.940 rows=3333333 loops=3)

                    Buffers: shared hit=63695

Planning time: 0.196 ms

Execution time: 1263.352 ms

pg9.6》

abase=# timing

Timing is on.

abase=# select sum(n_bigint) from t_num_type;

      sum       

----------------

 50000005000000

(1 row)



Time: 2042.587 ms

abase=# select sum(n_numeric) from t_num_type;

      sum       

----------------

 50000005000000

(1 row)



Time: 1874.880 ms

abase=# select sum(n_int) from t_num_type;

      sum       

----------------

 50000005000000

(1 row)



Time: 1073.567 ms

pg10.4》

 postgres=# select sum(n_bigint) from t_num_type;

          sum       

    ----------------

     50000005000000

    (1 row)



    Time: 871.811 ms

    postgres=# select sum(n_numeric) from t_num_type;

          sum       

    ----------------

     50000005000000

    (1 row)



    Time: 1168.779 ms (00:01.169)

    postgres=# select sum(n_int) from t_num_type;

          sum       

    ----------------

     50000005000000

    (1 row)



    Time: 923.551 ms

After many tests Sum efficiency of pg10.4 was significantly improved, 9.6: sum(int)>sum(numeric)>sum(bigint), pg10.4: sum(bigint)>sum(int) : >sum(numeric)

Why after multiple tests pg10:sum(bigint)>sum(int)?
Does it mean that the bigint type is more recommended?

First, you should repeat the experiment several times to see if the difference remains the same. Caching and other effects cause a certain fluctuation in query times.

I'd expect the difference between integer and bigint to be negligible in the long run. Both summation operations should be implemented in hardware.

numeric should be significantly slower, because operations on these binary coded decimals is implemented in C in the database engine.

If bigint summation remains faster even in repeated experiments, my only explanation is tuple deforming: to get to the third column, PostgreSQL has to process the first two columns.