GROUPBY PIPELINED Query Plan
If you have a projection that is already sorted on the customer_gender
column, the optimizer chooses the faster GROUPBY PIPELINED
operation:
=> EXPLAIN SELECTCOUNT(distinct customer_gender
) from customer_dimension; Access Path: +-GROUPBY NOTHING [Cost: 22, Rows: 1] (PATH ID: 1) | Aggregates: count(DISTINCT customer_dimension.customer_gender) | Execute on: Query Initiator | +--->GROUPBY PIPELINED
[Cost: 20, Rows: 10K] (PATH ID: 2) | | Group By: customer_dimension.customer_gender | | Execute on: Query Initiator | | +---> STORAGE ACCESS for customer_dimension [Cost: 17, Rows: 50K (3 RLE)] (PATH ID: 3) | | | Projection: public.customer_dimension_DBD_1_rep_vmartdb_design_vmartdb_design_node0001 | | | Materialize: customer_dimension.customer_gender | | | Execute on: Query Initiator
Similarly, the use of an equality predicate, such as in the following query, preserves GROUPBY PIPELINED
:
=> EXPLAIN SELECT COUNT(DISTINCT annual_income) FROM customer_dimensionWHERE customer_gender = 'Female'
; Access Path: +-GROUPBY NOTHING [Cost: 161, Rows: 1] (PATH ID: 1) | Aggregates: count(DISTINCT customer_dimension.annual_income) | +--->GROUPBY PIPELINED
[Cost: 158, Rows: 10K] (PATH ID: 2) | | Group By: customer_dimension.annual_income | | +---> STORAGE ACCESS for customer_dimension [Cost: 144, Rows: 47K] (PATH ID: 3) | | | Projection: public.customer_dimension_DBD_1_rep_vmartdb_design_vmartdb_design_node0001 | | | Materialize: customer_dimension.annual_income | | | Filter: (customer_dimension.customer_gender = 'Female')
If EXPLAIN
reports GROUPBY HASH
, modify the projection design to force it to use GROUPBY PIPELINED
.