Aggregate and hash aggregate

In this recipe, we will be discussing aggregate and hash aggregate mechanisms in PostgreSQL.

Getting ready

Aggregate is a node type that only evaluates the aggregate operators. Some of the aggregate operators are SUM, MIN, MAX, and so on.

Hash aggregate is a node type that requires an aggregate operator, and a group key column. In general, we see this node type being utilized during the GROUP BY, DISTINCT, or set operations.

How to do it…

Aggregate

1. To demonstrate the aggregates behavior, let's query the benchmarsql as follows:

   benchmarksql=# EXPLAIN SELECT max(i_price) FROM bmsql_item;
                                  QUERY PLAN                               
   ------------------------------------------------------------------------
   
   Aggregate
     (cost=2549.00..2549.01 rows=1 width=6)
      ->  Seq Scan on bmsql_item  (cost=0.00..2299.00 rows=100000
             width=6)
   (2 rows)
   

2. From the preceding plan, as expected we got the aggregate node type, which is followed...