Menu

1 / 2

PostgreSQL Aggregate and Window Function Tuning

DEV Community·Philip McClarence·7 months ago
#Pt4cngYB
#postgres#performance#database#sql#sort
Reading 0:00
15s threshold

GROUP BY and window functions look declarative — the query says what it wants, and PostgreSQL figures out how to compute it. In practice the planner has strong opinions about how : whether to hash or sort, whether to parallelise, whether to spill memory to disk, whether a matching index changes the plan entirely. Learn to read what the planner picked and why, and aggregate-heavy queries become one of the easiest categories to tune.

This article is the fifth in the Complete Guide to PostgreSQL SQL Query Analysis & Optimization series. Every EXPLAIN block below is captured from a real run on the series' Neon Postgres 17.8 database (500,000-row sim_bp_orders and friends).

The two aggregate strategies

For GROUP BY , the planner chooses primarily between two implementations — plus some parallel and distinct variants layered on top.

HashAggregate builds a hash table keyed by the group-by columns; each incoming row probes the hash and either creates a new entry or updates an existing one's running aggregate…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.