PostgreSQL 9.6 has a lot of good features; many of the changes are in the SQL planner, aiming to improve performance by carefully selecting the right execution plan. The great thing here is that doing less work makes many queries much, much faster than they were before.
First, we are now using Foreign Key data in the planner to improve estimates.
Next, we are combining aggregates to avoid duplicating effort.
We’re also improving the way that GROUP BY estimation occurs.
And we’re using partial indexes for index-only scans in more cases.
And we’ve improved estimates for distinct rows, leading to more accurate planning of hash joins and other plan types.
Congratulations to my 2ndQuadrant colleagues Tomas Vondra and David Rowley for their insight, rigour and persistence in chasing down these issues.
All of these things were planned. They didn’t occur randomly, they were part of a coordinated attack on planning problems with big data as part of the AXLE project.