Re: hash agg is slower on wide tables? - Mailing list pgsql-hackers

Hi

I did some benchmarks and I found some strange numbers.

do $$
begin
  drop table if exists t1;
  execute 'create table t1(' ||
     array_to_string(array(select 'a' || i || ' smallint' from generate_series(1,30) g(i)), ',') || ')';
  -- special column a2, a11
  insert into t1
    select 2008, i % 12 + 1, random()*20, random()*20, random()*20, random()*20, random()*20, random()*20, random()*20, random()*20,
           case when random() < 0.9 then 1 else 0 end, random()*20, random()*20, random()*20, random()*20, random()*20, random()*20, random()*20, random()*20, random()*20,
           random()*20, random()*20, random()*20, random()*20, random()*20, random()*20, random()*20, random()*20, random()*20, random()*20
      from generate_series(1,7009728) g(i);
  drop table if exists t2;
  create table t2 as select a2, a11 from t1;
  analyze t1; analyze t2;
end;
$$;

postgres=# \dt+ t*
                  List of relations
 Schema | Name | Type  | Owner |  Size  | Description
--------+------+-------+-------+--------+-------------
 public | t1   | table | pavel | 622 MB |
 public | t2   | table | pavel | 242 MB |
(2 rows)

postgres=# explain analyze select count(*), a2, a11 from t1 group by 3,2 order by 3,2;
                                                        QUERY PLAN                                                        
---------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=202327.03..202327.09 rows=24 width=4) (actual time=2609.159..2609.161 rows=24 loops=1)
   Sort Key: a11, a2
   Sort Method: quicksort  Memory: 26kB
   ->  HashAggregate  (cost=202326.24..202326.48 rows=24 width=4) (actual time=2609.137..2609.139 rows=24 loops=1) --- grouping 1997 ms
         Group Key: a11, a2
         ->  Seq Scan on t1  (cost=0.00..149753.28 rows=7009728 width=4) (actual time=0.071..616.222 rows=7009728 loops=1)
 Planning time: 0.138 ms
 Execution time: 2609.247 ms
(8 rows)

postgres=# explain analyze select count(*), a2, a11 from t2 group by 3,2 order by 3,2;
                                                        QUERY PLAN                                                        
---------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=153688.03..153688.09 rows=24 width=4) (actual time=2100.058..2100.059 rows=24 loops=1)
   Sort Key: a11, a2
   Sort Method: quicksort  Memory: 26kB
   ->  HashAggregate  (cost=153687.24..153687.48 rows=24 width=4) (actual time=2100.037..2100.040 rows=24 loops=1) --- grouping 1567 ms -- 25% faster
         Group Key: a11, a2
         ->  Seq Scan on t2  (cost=0.00..101114.28 rows=7009728 width=4) (actual time=0.043..532.680 rows=7009728 loops=1)
 Planning time: 0.178 ms
 Execution time: 2100.158 ms
(8 rows)

postgres=# \dt+ t*
                   List of relations
 Schema | Name | Type  | Owner |  Size   | Description
--------+------+-------+-------+---------+-------------
 public | t1   | table | pavel | 6225 MB |
 public | t2   | table | pavel | 2423 MB |
(2 rows)

postgres=# explain analyze select count(*), a2, a11 from t1 group by 3,2 order by 3,2;
                                                           QUERY PLAN                                                           
---------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2023263.19..2023263.25 rows=24 width=4) (actual time=99453.272..99453.274 rows=24 loops=1)
   Sort Key: a11, a2
   Sort Method: quicksort  Memory: 26kB
   ->  HashAggregate  (cost=2023262.40..2023262.64 rows=24 width=4) (actual time=99453.244..99453.249 rows=24 loops=1) --- 31891 ms
         Group Key: a11, a2
         ->  Seq Scan on t1  (cost=0.00..1497532.80 rows=70097280 width=4) (actual time=16.935..67562.615 rows=70097280 loops=1)
 Planning time: 14.526 ms
 Execution time: 99453.413 ms
(8 rows)

postgres=# explain analyze select count(*), a2, a11 from t2 group by 3,2 order by 3,2;
                                                          QUERY PLAN                                                          
-------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1536868.33..1536868.39 rows=24 width=4) (actual time=20656.397..20656.399 rows=24 loops=1)
   Sort Key: a11, a2
   Sort Method: quicksort  Memory: 26kB
   ->  HashAggregate  (cost=1536867.54..1536867.78 rows=24 width=4) (actual time=20656.375..20656.378 rows=24 loops=1) --- 15248 ms --100% faster
         Group Key: a11, a2
         ->  Seq Scan on t2  (cost=0.00..1011137.88 rows=70097288 width=4) (actual time=0.060..5408.205 rows=70097280 loops=1)
 Planning time: 0.161 ms
 Execution time: 20656.475 ms
(8 rows)

It looks like hah agg is slower when it is based on wide table about 25-100%. Is it - or I don't see something?

Regards

Pavel