gp_percentile_agg

The gp_percentile_agg module introduces improved Pivotal Query Optimizer (GPORCA) performance for ordered-set aggregate functions including percentile_cont(), percentile_disc(), and median(). These improvements particularly benefit MADlib, which internally invokes these functions.

GPORCA generates a more performant query plan when:

• The sort expression does not include any computed columns.
• The <fraction> provided to the function is a const and not an ARRAY.
• The query does not contain a GROUP BY clause.

The gp_percentile_agg module is a Greenplum Database extension.

Installing and Registering the Module

The gp_percentile_agg module is installed when you install Greenplum Database. You must register the gp_percentile_agg extension in each database where you want to use the module:

CREATE EXTENSION gp_percentile_agg;

Refer to Installing Additional Supplied Modules for more information.

About Using the Module

To realize the GPORCA performance benefits when using ordered-set aggregate functions, in addition to registering the extension you must also enable the optimizer_enable_orderedagg server configuration parameter before you run the query. For example, to enable this parameter in a psql session:

SET optimizer_enable_orderedagg = on;

When the extension is registered, optimizer_enable_orderedagg is enabled, and you invoke the percentile_cont(), percentile_disc(), or median() functions, GPORCA generates the more performant query plan.

Additional Module Documentation

Refer to Ordered-Set Aggregate Functions in the PostgreSQL documentation for more information about using ordered-set aggregates.