How to Use the Aggregation-Related Parameters in PostgreSQL

When working with a large database in PostgreSQL, query performance becomes increasingly important. PostgreSQL provides several planner configuration parameters that influence how queries are executed, especially for aggregation operations. Understanding these parameters helps developers and database administrators analyze execution plans and choose the most suitable settings for different workloads.

In this article, we will explore two aggregation-related planner parameters named enable_partitionwise_aggregate and enable_presorted_aggregate.

You will learn how to check their current values, view their metadata, locate their source code definitions, create test tables, and compare execution plans to understand how these parameters affect query execution.

We can get the name and other metadata from the pg_settings catalogue based on the aggregation-related parameters like this.

 select name from pg_settings where name ilike '%aggregate%';

Result :

              name              
--------------------------------
 enable_partitionwise_aggregate
 enable_presorted_aggregate

1. enable_partitionwise_aggregate

Check the current value of this parameter.

show  enable_partitionwise_aggregate;

Result :

 enable_partitionwise_aggregate 
--------------------------------
 off
(1 row)

Check this parameter’s metadata from pg_settings like this.

select * from pg_settings where name = 'enable_partitionwise_aggregate';

Result :

-[ RECORD 1 ]---+------------------------------------------------
name            | enable_partitionwise_aggregate
setting         | off
unit            | 
category        | Query Tuning / Planner Method Configuration
short_desc      | Enables partitionwise aggregation and grouping.
extra_desc      | 
context         | user
vartype         | bool
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | off
reset_val       | off
sourcefile      | 
sourceline      | 
pending_restart | f

Check this parameter’s definition in the guc_parameters.dat file in the postgres source code.

If you are using postgres version greater than 18, then you can see the parameter definition from the guc_parameters.dat file or postgres version is 18 or below 18, you can see this definition from the file named guc_tables.c

{ name => 'enable_partitionwise_aggregate', type => 'bool', context => 'PGC_USERSET', group => 'QUERY_TUNING_METHOD',
  short_desc => 'Enables partitionwise aggregation and grouping.',
  flags => 'GUC_EXPLAIN',
  variable => 'enable_partitionwise_aggregate',
  boot_val => 'false',
},

Create a partitioned table to understand the purpose of the enable_partitionwise_aggregate parameters in postgres.

CREATE TABLE sales
(
    id serial,
    sale_year int,
    region text,
    amount numeric
)
PARTITION BY LIST (sale_year);

Create the child partitions also based on the sale_year column

CREATE TABLE sales_2024
PARTITION OF sales
FOR VALUES IN (2024);
CREATE TABLE sales_2025
PARTITION OF sales
FOR VALUES IN (2025);
CREATE TABLE sales_2026
PARTITION OF sales
FOR VALUES IN (2026);

Insert some sample values for the sales table with different sale_year.

 INSERT INTO sales (sale_year, region, amount)SELECT 2024,       CASE           WHEN i % 3 = 0 THEN 'North'           WHEN i % 3 = 1 THEN 'South'           ELSE 'East'       END,       random() * 1000FROM generate_series(1,100000) i;INSERT INTO sales (sale_year, region, amount)SELECT 2025,       CASE           WHEN i % 3 = 0 THEN 'North'           WHEN i % 3 = 1 THEN 'South'           ELSE 'East'       END,       random() * 1000FROM generate_series(1,100000) i;INSERT INTO sales (sale_year, region, amount)SELECT 2026,       CASE           WHEN i % 3 = 0 THEN 'North'           WHEN i % 3 = 1 THEN 'South'           ELSE 'East'       END,       random() * 1000FROM generate_series(1,100000) i;

For getting the latest statistics to planner, execute the ANALYZE command.

ANALYZE sales;

Now execute the query below to test the working of the partition wise aggregate. The query must include the GROUP BY clause and any of the aggregation functions also.

SELECT
    sale_year,
    SUM(amount)
FROM sales
GROUP BY sale_year;

Result :

 sale_year |            sum             
-----------+----------------------------
      2024 | 49917053.79136649997842378
      2025 |  49987473.2785058630109727
      2026 |  50111984.6956529809636124
(3 rows)

Check whether the value of this parameter is off or on.

show enable_partitionwise_aggregate ;

Result :

 enable_partitionwise_aggregate 
--------------------------------
 off
(1 row)

Now use the EXPLAIN ANALYSE to get the execution details of this query.

EXPLAIN (ANALYZE, VERBOSE)
SELECT sale_year,
       SUM(amount)
FROM sales
GROUP BY sale_year;

Result :

                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=6138.80..6139.71 rows=3 width=36) (actual time=552.829..555.131 rows=3.00 loops=1)
   Output: sales.sale_year, sum(sales.amount)
   Group Key: sales.sale_year
   Buffers: shared hit=2138
   ->  Gather Merge  (cost=6138.80..6139.62 rows=7 width=36) (actual time=552.798..555.092 rows=6.00 loops=1)
         Output: sales.sale_year, (PARTIAL sum(sales.amount))
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=2138
         ->  Sort  (cost=5138.78..5138.78 rows=3 width=36) (actual time=548.879..548.896 rows=2.00 loops=3)
               Output: sales.sale_year, (PARTIAL sum(sales.amount))
               Sort Key: sales.sale_year
               Sort Method: quicksort  Memory: 25kB
               Buffers: shared hit=2138
               Worker 0:  actual time=548.094..548.108 rows=2.00 loops=1
                 Sort Method: quicksort  Memory: 25kB
                 Buffers: shared hit=714
               Worker 1:  actual time=547.501..547.511 rows=1.00 loops=1
                 Sort Method: quicksort  Memory: 25kB
                 Buffers: shared hit=712
               ->  Partial HashAggregate  (cost=5138.72..5138.75 rows=3 width=36) (actual time=548.850..548.863 rows=2.00 loops=3)
                     Output: sales.sale_year, PARTIAL sum(sales.amount)
                     Group Key: sales.sale_year
                     Batches: 1  Memory Usage: 32kB
                     Buffers: shared hit=2124
                     Worker 0:  actual time=548.061..548.074 rows=2.00 loops=1
                       Batches: 1  Memory Usage: 32kB
                       Buffers: shared hit=707
                     Worker 1:  actual time=547.459..547.466 rows=1.00 loops=1
                       Batches: 1  Memory Usage: 32kB
                       Buffers: shared hit=705
                     ->  Parallel Append  (cost=0.00..4513.71 rows=125001 width=15) (actual time=0.011..409.812 rows=100000.00 loops=3)
                           Buffers: shared hit=2124
                           Worker 0:  actual time=0.013..409.513 rows=99859.00 loops=1
                             Buffers: shared hit=707
                           Worker 1:  actual time=0.008..408.654 rows=99711.00 loops=1
                             Buffers: shared hit=705
                           ->  Parallel Seq Scan on public.sales_2024 sales_1  (cost=0.00..1296.24 rows=58824 width=15) (actual time=0.005..46.002 rows=33333.33 loops=3)
                                 Output: sales_1.sale_year, sales_1.amount
                                 Buffers: shared hit=708
                                 Worker 0:  actual time=0.007..0.370 rows=283.00 loops=1
                                   Buffers: shared hit=2
                                 Worker 1:  actual time=0.005..137.625 rows=99711.00 loops=1
                                   Buffers: shared hit=705
                           ->  Parallel Seq Scan on public.sales_2025 sales_2  (cost=0.00..1296.24 rows=58824 width=15) (actual time=0.008..69.122 rows=50000.00 loops=2)
                                 Output: sales_2.sale_year, sales_2.amount
                                 Buffers: shared hit=708
                                 Worker 0:  actual time=0.010..137.671 rows=99576.00 loops=1
                                   Buffers: shared hit=705
                           ->  Parallel Seq Scan on public.sales_2026 sales_3  (cost=0.00..1296.24 rows=58824 width=15) (actual time=0.010..137.848 rows=100000.00 loops=1)
                                 Output: sales_3.sale_year, sales_3.amount
                                 Buffers: shared hit=708
 Planning Time: 0.091 ms
 Execution Time: 555.176 ms
(54 rows)

Now enable this parameter.

SET enable_partitionwise_aggregate = on;

Now execute the same query again and check its execution plan.

EXPLAIN (ANALYZE, VERBOSE)
SELECT sale_year,
       SUM(amount)
FROM sales
GROUP BY sale_year;

Result :

                                                                       QUERY PLAN                                                                       -------------------------------------------------------------------------------------------------------------------------------------------------------- Gather  (cost=3208.00..5416.23 rows=2 width=36) (actual time=284.963..292.992 rows=3.00 loops=1)   Output: sales.sale_year, (sum(sales.amount))   Workers Planned: 2   Workers Launched: 2   Buffers: shared hit=2164   ->  Parallel Append  (cost=2208.00..4416.03 rows=1 width=36) (actual time=285.144..285.157 rows=1.00 loops=3)         Buffers: shared hit=2164         Worker 0:  actual time=285.666..285.678 rows=1.00 loops=1           Buffers: shared hit=728         Worker 1:  actual time=285.139..285.151 rows=1.00 loops=1           Buffers: shared hit=728         ->  HashAggregate  (cost=2208.00..2208.01 rows=1 width=36) (actual time=285.663..285.669 rows=1.00 loops=1)               Output: sales.sale_year, sum(sales.amount)               Group Key: sales.sale_year               Buffers: shared hit=728               Worker 0:  actual time=285.663..285.669 rows=1.00 loops=1                 Batches: 1  Memory Usage: 32kB                 Buffers: shared hit=728               ->  Seq Scan on public.sales_2024 sales  (cost=0.00..1708.00 rows=100000 width=15) (actual time=0.070..141.892 rows=100000.00 loops=1)                     Output: sales.sale_year, sales.amount                     Buffers: shared hit=728                     Worker 0:  actual time=0.070..141.892 rows=100000.00 loops=1                       Buffers: shared hit=728         ->  HashAggregate  (cost=2208.00..2208.01 rows=1 width=36) (actual time=285.136..285.142 rows=1.00 loops=1)               Output: sales_1.sale_year, sum(sales_1.amount)               Group Key: sales_1.sale_year               Buffers: shared hit=728               Worker 1:  actual time=285.136..285.142 rows=1.00 loops=1                 Batches: 1  Memory Usage: 32kB                 Buffers: shared hit=728               ->  Seq Scan on public.sales_2025 sales_1  (cost=0.00..1708.00 rows=100000 width=15) (actual time=0.075..141.746 rows=100000.00 loops=1)                     Output: sales_1.sale_year, sales_1.amount                     Buffers: shared hit=728                     Worker 1:  actual time=0.075..141.746 rows=100000.00 loops=1                       Buffers: shared hit=728         ->  HashAggregate  (cost=2208.00..2208.01 rows=1 width=36) (actual time=284.625..284.632 rows=1.00 loops=1)               Output: sales_2.sale_year, sum(sales_2.amount)               Group Key: sales_2.sale_year               Batches: 1  Memory Usage: 32kB               Buffers: shared hit=708               ->  Seq Scan on public.sales_2026 sales_2  (cost=0.00..1708.00 rows=100000 width=15) (actual time=0.012..141.465 rows=100000.00 loops=1)                     Output: sales_2.sale_year, sales_2.amount                     Buffers: shared hit=708 Planning Time: 0.132 ms Execution Time: 293.061 ms(45 rows)

In this example, enabling enable_partitionwise_aggregate reduced the execution time from 555.176 ms to 293.061 ms, improving query performance by approximately 47%. This improvement occurs because postgres performs aggregation locally within each partition instead of combining all rows and aggregating them globally.

As a result, fewer executor nodes are required, less intermediate data is processed, and the overall execution plan becomes more efficient.

When enable_partitionwise_aggregate is OFF

With the parameter disabled, the postgres treats the partitioned table as a single relation during aggregation. Although it scans each partition using a parallel append, and the aggregation is performed only after rows from all partitions are combined.

When enable_partitionwise_aggregate is ON

After enabling the parameter, postgres recognizes that the GROUP BY column (sale_year) is also the partition key. This allows the planner to perform aggregation independently within each partition.

Comparison of Both Execution Plans

2. enable_presorted_aggregate

Check the current value of this parameter.

show enable_presorted_aggregate;

Result :

 enable_presorted_aggregate 
----------------------------
 on
(1 row)

Check this parameter’s metadata from pg_settings like this.

select * from pg_settings where name = 'enable_presorted_aggregate';

Result :

-[ RECORD 1 ]---+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
name            | enable_presorted_aggregate
setting         | on
unit            | 
category        | Query Tuning / Planner Method Configuration
short_desc      | Enables the planner's ability to produce plans that provide presorted input for ORDER BY / DISTINCT aggregate functions.
extra_desc      | Allows the query planner to build plans that provide presorted input for aggregate functions with an ORDER BY / DISTINCT clause.  When disabled, implicit sorts are always performed during execution.
context         | user
vartype         | bool
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | on
reset_val       | on
sourcefile      | 
sourceline      | 
pending_restart | f

Check this parameter’s definition from the guc_parameters.dat file on the postgres source code.

{ name => 'enable_presorted_aggregate', type => 'bool', context => 'PGC_USERSET', group => 'QUERY_TUNING_METHOD',
  short_desc => 'Enables the planner\'s ability to produce plans that provide presorted input for ORDER BY / DISTINCT aggregate functions.',
  long_desc => 'Allows the query planner to build plans that provide presorted input for aggregate functions with an ORDER BY / DISTINCT clause.  When disabled, implicit sorts are always performed during execution.',
  flags => 'GUC_EXPLAIN',
  variable => 'enable_presorted_aggregate',
  boot_val => 'true',
},

Create a sample table to understand the purpose of this parameter named enable_presorted_aggregate.

 CREATE TABLE sol (
    id          bigserial PRIMARY KEY,
    order_id    bigint  NOT NULL,
    product_id  bigint  NOT NULL,
    qty         numeric(12,2) NOT NULL,
    name        text    NOT NULL
);

Now insert some sample values also.

 INSERT INTO sol (order_id, product_id, qty, name)
SELECT (i % 20000) + 1,
       (i % 500) + 1,
       (random() * 100)::numeric(12,2),
       'line ' || i
FROM generate_series(1, 2000000) AS i;

Create a multi column index on the sol table based on the columns named order_id and product_id.

CREATE INDEX sol_order_product_idx ON sol (order_id, product_id);

Execute the ANALYZE command for catching the latest statistics to the postgres planner.

ANALYZE sol;

Now enable this parameter.

SET enable_presorted_aggregate = on;

Now execute the below query with explain analyze and check it’s execution plan.

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT array_agg(product_id ORDER BY product_id) FROM sol;

Result :

                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=1 read=1821, temp read=2941 written=2952
   ->  Sort (actual rows=2000000.00 loops=1)
         Sort Key: product_id
         Sort Method: external merge  Disk: 23528kB
         Buffers: shared hit=1 read=1821, temp read=2941 written=2952
         ->  Index Only Scan using sol_order_product_idx on sol (actual rows=2000000.00 loops=1)
               Heap Fetches: 0
               Index Searches: 1
               Buffers: shared hit=1 read=1821
 Planning:
   Buffers: shared hit=19 read=5
 Planning Time: 0.106 ms
 Execution Time: 399.698 ms
(14 rows)

Now disable this parameter using the SET command.

SET enable_presorted_aggregate = off;

Execute the same query again and check it’s execution plan.

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT array_agg(product_id ORDER BY product_id) FROM sol;
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=1822, temp read=2941 written=2952
   ->  Index Only Scan using sol_order_product_idx on sol (actual rows=2000000.00 loops=1)
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=1822
 Planning Time: 0.071 ms
 Execution Time: 324.679 ms
(8 rows)

The execution plans show how the enable_presorted_aggregate parameter influences the planner's strategy for aggregate functions that use an ORDER BY or DISTINCT clause.

When enable_presorted_aggregate is ON

With enable_presorted_aggregate enabled, the postgres attempts to produce input that is already sorted before executing the aggregate function.

In this execution plan, postgres adds an explicit Sort node before the Aggregate node.

The execution flow is:

• Index only scan reads all rows from the table.
• A separate sort node sorts the rows by product_id.
• The sorted rows are then passed to the Aggregate node to compute array_agg(product_id ORDER BY product_id).

Because two million rows must be sorted, so postgres performs an External Merge Sort, which spills data to temporary disk files. This increases disk I/O and introduces additional processing overhead.

When enable_presorted_aggregate is OFF

After disabling the parameter, Postgresql no longer tries to generate a plan with presorted input.

The execution plan becomes much simpler.

The execution flow is:

• Index only scan reads all rows.
• The rows are directly passed to the Aggregate node.

Notice that the Sort node completely disappears from the execution plan.

Since PostgreSQL does not perform a separate sorting step, there is less processing overhead, resulting in a shorter execution time.

The enable_partitionwise_aggregate and enable_presorted_aggregate parameters allow the postgres to use different planning strategies for aggregation queries.

By testing these parameters with sample tables and reviewing the execution plans, you can better understand when each approach is beneficial.

Evaluating query plans before and after changing these settings is a practical way to see how the planner behaves and to determine which configuration is more suitable for your workload.