Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
To: pgsql-performance@lists.postgresql.org
References: <88dec70b-eb63-0b72-0b5d-253851ef127b@mailpen.com>
 <163214e4-0588-230b-9ed5-f682087e0e18@wi3ck.info>
 <4e238cc5-f63e-0435-63bf-2515500d5424@dunslane.net>
 <20210528213052.GC5649@momjian.us>
 <E007EEB7-097D-4D0E-9C4D-C8BB28810E7F@thebuild.com>
From: "Dean Gibson (DB Administrator)" <postgresql@mailpen.com>
Organization: UltiMeth Systems
Message-ID: <cf15c19d-43d8-95ff-17a0-ded099f70861@mailpen.com>
Date: Fri, 28 May 2021 21:08:28 -0700
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.10.2
MIME-Version: 1.0
In-Reply-To: <E007EEB7-097D-4D0E-9C4D-C8BB28810E7F@thebuild.com>
Content-Type: multipart/alternative;
 boundary="------------0C4DEE6194A0C5AA172C71B5"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/cf15c19d-43d8-95ff-17a0-ded099f70861%40mailpen.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------0C4DEE6194A0C5AA172C71B5
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 2021-05-28 19:43, Christophe Pettus wrote:
> ...
> The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter changes, since you get a new default parameter group after the upgrade.
>
> That being said, this does look like something happened to the planner to cause it to pick a worse plan in v13.  The deeply nested views make it kind of hard to pin down, but the core issue appears to be in the "good" plan, it evaluates the _Club.club_count > 5 relatively early, which greatly limits the number of rows that it handles elsewhere in the query.    Why the plan change, I can't say.
>
> It might be worth creating a materialized CTE that grabs the "club_count > 5" set and uses that, instead of having it at the top level predicates.

I spent quite a bit of time over the past five days experimenting with 
various parameter values, to no avail, but I don't mind trying some more.

I have other queries that fail even more spectacularly, & they all seem 
to involve a generated table like the "club" one in my example.  I have 
an idea that I might try, in effectively changing the order of 
evaluation.  I'll have to think about that.  Thanks for the suggestion!  
However, one "shouldn't" have to tinker with the order of stuff in SQL;  
that's one of the beauties of the language: the "compiler" (planner) is 
supposed to figure that all out.  And for me, that's been true for the 
past 15 years with PostgreSQL.

Note that this problem is not unique to v13.  It happened with upgrades 
to v10, 11, &12.  So, some fundamental change was made back then (at 
least in the RDS version).  Since I need a bulletproof backup past next 
January, I think my next task will be to get an EC2 instance running 
v9.6, where AWS can't try to upgrade it.  Then, at my leisure, I can 
fiddle with upgrading.

--------------0C4DEE6194A0C5AA172C71B5
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 2021-05-28 19:43, Christophe Pettus
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:E007EEB7-097D-4D0E-9C4D-C8BB28810E7F@thebuild.com">...
      <pre class="moz-quote-pre" wrap="">
The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter changes, since you get a new default parameter group after the upgrade.

That being said, this does look like something happened to the planner to cause it to pick a worse plan in v13.  The deeply nested views make it kind of hard to pin down, but the core issue appears to be in the "good" plan, it evaluates the _Club.club_count &gt; 5 relatively early, which greatly limits the number of rows that it handles elsewhere in the query.    Why the plan change, I can't say.

It might be worth creating a materialized CTE that grabs the "club_count &gt; 5" set and uses that, instead of having it at the top level predicates.</pre>
    </blockquote>
    <br>
    I spent quite a bit of time over the past five days experimenting
    with various parameter values, to no avail, but I don't mind trying
    some more.<br>
    <br>
    I have other queries that fail even more spectacularly, &amp; they
    all seem to involve a generated table like the "club" one in my
    example.  I have an idea that I might try, in effectively changing
    the order of evaluation.  I'll have to think about that.  Thanks for
    the suggestion!  However, one "shouldn't" have to tinker with the
    order of stuff in SQL;  that's one of the beauties of the language: 
    the "compiler" (planner) is supposed to figure that all out.  And
    for me, that's been true for the past 15 years with PostgreSQL.<br>
    <br>
    Note that this problem is not unique to v13.  It happened with
    upgrades to v10, 11, &amp;12.  So, some fundamental change was made
    back then (at least in the RDS version).  Since I need a bulletproof
    backup past next January, I think my next task will be to get an EC2
    instance running v9.6, where AWS can't try to upgrade it.  Then, at
    my leisure, I can fiddle with upgrading.<br>
  </body>
</html>

--------------0C4DEE6194A0C5AA172C71B5--