Re: Seeking reason behind performance gain in 12 with HashAggregate

On Mon, Jan 13, 2020 at 2:15 PM Alvaro Herrera wrote: > On 2020-Jan-13, Shira Bezalel wrote: > > > Hi All, > > > > I'm testing an upgrade from Postgres 9.6.16 to 12.1 and seeing a > > significant performance gain in one specific query. This is really great, > > but I'm just looking to understand

Re: Seeking reason behind performance gain in 12 with HashAggregate

On 2020-Jan-13, Shira Bezalel wrote: > Hi All, > > I'm testing an upgrade from Postgres 9.6.16 to 12.1 and seeing a > significant performance gain in one specific query. This is really great, > but I'm just looking to understand why. pg12 reads half the number of buffers. I bet it's because of

Re: Seeking reason behind performance gain in 12 with HashAggregate

Thanks Tomas. I ran a vacuum full on the 9.6 table -- still no difference in the query plan. The shared buffers hit went up slightly to 36069. Shira On Mon, Jan 13, 2020 at 1:12 PM Tomas Vondra wrote: > On Mon, Jan 13, 2020 at 12:44:14PM -0800, Shira Bezalel wrote: > >Hi Michael, > > > >I appre

Re: Seeking reason behind performance gain in 12 with HashAggregate

On Mon, Jan 13, 2020 at 12:44:14PM -0800, Shira Bezalel wrote: Hi Michael, I appreciate your question. I ran a vacuum analyze on the 9.6 table and it yielded no difference. Same number of buffers were read, same query plan. VACUUM ANALYZE won't shrink the table - the number of buffers will be

Re: Seeking reason behind performance gain in 12 with HashAggregate

Hi Michael, I appreciate your question. I ran a vacuum analyze on the 9.6 table and it yielded no difference. Same number of buffers were read, same query plan. Thanks, Shira On Mon, Jan 13, 2020 at 10:07 AM Michael Lewis wrote: > I am not at all familiar with PostGIS so perhaps this is a sill

Re: Seeking reason behind performance gain in 12 with HashAggregate

I am not at all familiar with PostGIS so perhaps this is a silly question, is bloat an issue on the older instance? Correlation isn't causation, but half the buffers scanned and half the runtime in the v12 plan has me curious why that might be. >

Re: Seeking reason behind performance gain in 12 with HashAggregate

Hi Justin, I'm seeing no difference in the query plan with JIT disabled in 12.1. Thanks, Shira On Mon, Jan 13, 2020 at 8:42 AM Justin Pryzby wrote: > On Mon, Jan 13, 2020 at 08:29:05AM -0800, Shira Bezalel wrote: > > Here is the EXPLAIN (ANALYZE ON, BUFFERS ON) output from both systems: > > >

Re: Seeking reason behind performance gain in 12 with HashAggregate

On Mon, Jan 13, 2020 at 08:29:05AM -0800, Shira Bezalel wrote: > Here is the EXPLAIN (ANALYZE ON, BUFFERS ON) output from both systems: > > 9.6 plan > 12.1 plan > Is there something that was changed/improved in either 10, 11

Seeking reason behind performance gain in 12 with HashAggregate

Hi All, I'm testing an upgrade from Postgres 9.6.16 to 12.1 and seeing a significant performance gain in one specific query. This is really great, but I'm just looking to understand why. Reading through the release notes across all the new versions (10, 11, 12) hasn't yielded an obvious cause, but