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) ...

Get PostgreSQL High Performance Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.