PostgreSQL aggregation pushdown, explained: a 200M-row group-by test
How aggregation pushdown changes the plan behind a slow group-by join in PostgreSQL, what an internal 200M-row test measured, and when the gain does not apply.
29 Sept 2026 · 4 min read
A reporting query that answered in well under a second at 5 million rows now takes 5 seconds at 200 million. Nobody touched the application.
So you do what everyone does. Add an index. Double the memory. Build a nightly summary table and inherit the job of keeping it fresh. Sometimes that works. Often the plan keeps the same shape, and the shape is the problem.
The plan shape behind a slow group-by join
Here is the kind of query that turns up in almost every analytics workload:
```sql
SELECT c.name, sum(s.amount)
FROM t_sales s
JOIN t_country c ON c.id = s.country_id
GROUP BY c.name;
```
t_sales holds the transactions. t_country holds four rows. Standard PostgreSQL plans this in three moves:
Sequential scan over t_sales.
Hash join to t_country, building the hash table from the large side.
HashAggregate or GroupAggregate over the joined result, grouped on a text column.
Two expensive decisions sit inside those three steps. The join touches every sales row before anyone groups anything, even though the lookup table has four rows in it. And the grouping key is text, so the aggregate hashes and compares strings instead of integers.
Hardware changes neither decision. More RAM runs the same plan shape faster, which is why a memory upgrade buys a good month and then the query is slow again.
What aggregation pushdown changes
Aggregation pushdown reverses the order. PGEE groups on the numeric key during the scan, then joins that small result to the lookup table.
| Step | Standard PostgreSQL | With aggregation pushdown |
|---|---|---|
| 1 | Sequential scan of t_sales | Partial HashAggregate on country_id |
| 2 | Hash join against t_country | Nested loop with index scan |
| 3 | Aggregation on text keys | Finalize GroupAggregate |
The join now handles aggregate output, roughly one row per country, instead of 200 million transaction rows. Numeric grouping is cheaper than string grouping. An index scan suits a four-row lookup table better than a hash join. And because less work reaches the finalize stage, JIT compilation overhead drops as well.
Enabling it
Pushdown is an optimizer enhancement in PGEE, the enterprise PostgreSQL distribution built by CYBERTEC. It is not a community PostgreSQL feature. The documented settings are:
```sql
SET enable_agg_pushdown = on;
SET max_parallel_workers_per_gather = 4;
```
Confirm the parameter exists on your build before you plan around it:
```sql
SHOW enable_agg_pushdown;
```
What the internal test measured
Workload: t_sales with 200 million rows at roughly 10 GB, joined to a four-row t_country lookup table, grouped by country.
| Metric | Standard PostgreSQL | PGEE | Improvement |
|---|---|---|---|
| Execution time | 5,387 ms | 2,684 ms | 50.2% faster |
| Throughput | 37M rows/sec | 74.5M rows/sec | 2.01x |
CYBERTEC positions the PGEE query optimizer as delivering 50%+ faster analytics. On this workload, our own measurement landed in the same range.
What this test does not prove
Be careful with a number like 50.2%, including this one.
It is one workload shape on one dataset, measured in a lab rather than in production. The lookup table has four rows, which is close to the best case for a delayed join. The aggregate output is tiny relative to the fact table, which is also close to the best case. Pushdown pays off when the grouping key is numeric and the dimension table is small. Change either condition and the gain shrinks.
If your reporting queries group on high-cardinality text keys, or the lookup table holds millions of rows, or the query is bound by disk I/O rather than by join and aggregate work, do not expect the same improvement. The setting also has to be enabled. It is not automatic, and it is not a general speedup for every query on the server.
How to check whether your workload fits
Run EXPLAIN (ANALYZE, BUFFERS) on the slow query and find where the time actually goes. If the aggregate is not a meaningful share of it, pushdown will not save you.
Look at the grouping key. Numeric keys give the optimizer the most to work with; text keys give it the least.
Check the cardinality of the lookup table. Small dimension tables are the case this optimization was built for.
Test on a copy of production data before changing anything in production. Row counts and data distribution move the result more than server specifications do.
Compare plans, not only timings. A plan that changed shape explains why the numbers moved, and a plan that did not change tells you to stop looking here.
Where Worlber fits
PGEE is CYBERTEC's product. Worlber works with CYBERTEC's enterprise PostgreSQL portfolio and takes on the work around it: assessing whether a workload actually benefits from the enterprise optimizer, migrating a database onto PGEE where that is the right call, and tuning performance afterwards.
That last part matters more than the license. An optimizer setting nobody has tuned for the real query mix is an unused flag on a bill.
*Benchmark figures above come from an internal Worlber test on a single dataset and are provided for illustration. Results on your data will differ.*
Bring us your slowest aggregation query
Send one reporting query and its execution plan. Worlber will look at where the time goes and tell you whether PGEE's optimizer changes the outcome for that workload. If it does not help, we will say so, and you keep the analysis either way.