Grouping

In this recipe, we will be discussing the optimizer node type, which will be chosen during the group by operation.

Getting ready

As we discussed group or aggregate operations in the previous recipe, grouping operations will have performed based on the group key list. The PostgreSQL optimizer chooses hash aggregate, when it finds enough memory and if not, group aggregate will be the option. Unlike hash aggregate, the group aggregate operation needs data to be sorted. If the group columns have a sorted index already, then group aggregate will choose over the hash aggregate as to reduce the memory usage.

How to do it…

1. To demonstrate group aggregate, let's run the query in the benchmarksql database to get the count of customers, grouped by their city:

    benchmarksql=# EXPLAIN SELECT COUNT(*), c_city FROM
              bmsql_customer GROUP BY c_city;
                                               QUERY PLAN                                             
   -----------------------------...