Re: Improving avg performance for numeric - Mailing list pgsql-hackers

p.s. Performance tests

postgres=# create table foo(a int, b float, c double precision, d numeric, gr int);
CREATE TABLE
postgres=#
postgres=# insert into foo select 1, 2.0, 3.0, 3.14, random()*10000 from generate_series(1,10000000);

postgres=# \d foo
          Table "public.foo"
 Column |       Type       | Modifiers
--------+------------------+-----------
 a      | integer          |
 b      | double precision |
 c      | double precision |
 d      | numeric          |
 gr     | integer          |


set work_mem to '2MB';

postgres=# show debug_assertions;
 debug_assertions
------------------
 off
(1 row)



postgres=# explain (analyze, timing off) select sum(a) from foo;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=208332.23..208332.24 rows=1 width=4) (actual rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=4) (actual rows=10000000 loops=1)
 Total runtime: 1210.321 ms (1195.117 ms) -- patched (original)
(3 rows)

Time: 1210.709 ms
postgres=# explain (analyze, timing off) select sum(a) from foo group by gr;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 HashAggregate  (cost=233331.87..233431.71 rows=9984 width=8) (actual rows=10001 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=8) (actual rows=10000000 loops=1)
 Total runtime: 2923.987 ms (2952.292 ms)
(3 rows)

Time: 2924.384 ms

postgres=# explain (analyze, timing off) select avg(a) from foo;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=208332.23..208332.24 rows=1 width=4) (actual rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=4) (actual rows=10000000 loops=1)
 Total runtime: 1331.627 ms (1312.140 ms)
(3 rows)

postgres=# explain (analyze, timing off) select avg(a) from foo group by gr;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 HashAggregate  (cost=233331.87..233456.67 rows=9984 width=8) (actual rows=10001 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=8) (actual rows=10000000 loops=1)
 Total runtime: 3139.296 ms (3079.479 ms)
(3 rows)

postgres=# explain (analyze, timing off) select sum(b) from foo;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=208332.23..208332.24 rows=1 width=8) (actual rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=8) (actual rows=10000000 loops=1)
 Total runtime: 1327.841 ms (1339.214 ms)
(3 rows)

postgres=# explain (analyze, timing off) select sum(b) from foo group by gr;
                                             QUERY PLAN                                            
----------------------------------------------------------------------------------------------------
 HashAggregate  (cost=233331.87..233431.71 rows=9984 width=12) (actual rows=10001 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=12) (actual rows=10000000 loops=1)
 Total runtime: 3047.893 ms (3095.591 ms)
(3 rows)

postgres=# explain (analyze, timing off) select avg(b) from foo;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=208332.23..208332.24 rows=1 width=8) (actual rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=8) (actual rows=10000000 loops=1)
 Total runtime: 1454.665 ms (1471.413 ms)
(3 rows)

postgres=# explain (analyze, timing off) select avg(b) from foo group by gr;
                                             QUERY PLAN                                            
----------------------------------------------------------------------------------------------------
 HashAggregate  (cost=233331.87..233456.67 rows=9984 width=12) (actual rows=10001 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=12) (actual rows=10000000 loops=1)
 Total runtime: 3282.838 ms (3187.157 ms)
(3 rows)

postgres=# explain (analyze, timing off) select sum(c) from foo;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=208332.23..208332.24 rows=1 width=8) (actual rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=8) (actual rows=10000000 loops=1)
 Total runtime: 1348.555 ms (1364.585 ms)
(3 rows)

postgres=# explain (analyze, timing off) select sum(c) from foo group by gr;
                                             QUERY PLAN                                            
----------------------------------------------------------------------------------------------------
 HashAggregate  (cost=233331.87..233431.71 rows=9984 width=12) (actual rows=10001 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=12) (actual rows=10000000 loops=1)
 Total runtime: 3028.663 ms (3069.710 ms)
(3 rows)

postgres=# explain (analyze, timing off) select avg(c) from foo;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=208332.23..208332.24 rows=1 width=8) (actual rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=8) (actual rows=10000000 loops=1)
 Total runtime: 1488.980 ms (1463.813 ms)
(3 rows)

postgres=# explain (analyze, timing off) select avg(c) from foo group by gr;
                                             QUERY PLAN                                            
----------------------------------------------------------------------------------------------------
 HashAggregate  (cost=233331.87..233456.67 rows=9984 width=12) (actual rows=10001 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=12) (actual rows=10000000 loops=1)
 Total runtime: 3252.972 ms (3149.986 ms)
(3 rows)

postgres=# explain (analyze, timing off) select sum(d) from foo;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=208332.23..208332.24 rows=1 width=7) (actual rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=7) (actual rows=10000000 loops=1)
 Total runtime: 2301.769 ms (2784.430 ms)
(3 rows)

postgres=# explain (analyze, timing off) select sum(d) from foo group by gr;
                                             QUERY PLAN                                            
----------------------------------------------------------------------------------------------------
 HashAggregate  (cost=233331.87..233456.67 rows=9984 width=11) (actual rows=10001 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=11) (actual rows=10000000 loops=1)
 Total runtime: 4189.272 ms (4440.335 ms)
(3 rows)

postgres=# explain (analyze, timing off) select avg(d) from foo;
                                            QUERY PLAN                                            
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=208332.23..208332.24 rows=1 width=7) (actual rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=7) (actual rows=10000000 loops=1)
 Total runtime: 2308.493 ms (5195.970 ms)
(3 rows)

postgres=# explain (analyze, timing off) select avg(d) from foo group by gr;
                                             QUERY PLAN                                            
----------------------------------------------------------------------------------------------------
 HashAggregate  (cost=233331.87..233456.67 rows=9984 width=11) (actual rows=10001 loops=1)
   ->  Seq Scan on foo  (cost=0.00..183332.58 rows=9999858 width=11) (actual rows=10000000 loops=1)
 Total runtime: 4179.978 ms (6828.398 ms)
(3 rows)


int, float, double 26829 ms (26675 ms) -- 0.5% slower .. statistic error .. cleaner code
numeric sum 6490 ms (7224 ms) --  10% faster
numeric avg 6487 ms (12023 ms) -- 46% faster