Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
To: pgsql-performance@postgresql.org
References: <3fa6fd38-1f92-98ab-0055-3e0eb7e4e772@ultimeth.com>
 <2a1b34f4-73d4-585b-f4fc-3bd9b9ba248e@aklaver.com>
 <88255b75-e7e3-b29c-0e75-f989ea320570@mailpen.com>
 <70edb767-6ba0-1065-2f74-f01440e3caea@gmail.com>
 <d73e2731-31ba-df14-9b9c-974342200ae8@mailpen.com>
 <2b7b9a25-e3c7-b980-a1e3-d21d8d91d34d@gmail.com>
 <988e2438-c322-c1d5-ca88-683943a80087@mailpen.com>
 <1a2f01ce-b3da-3066-0642-3e311587ece3@aklaver.com>
From: "Dean Gibson (DB Administrator)" <postgresql@mailpen.com>
Organization: UltiMeth Systems
Message-ID: <94ed2da7-7622-e57b-1562-3f7caf40b4a8@mailpen.com>
Date: Sat, 29 May 2021 12:59:47 -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: <1a2f01ce-b3da-3066-0642-3e311587ece3@aklaver.com>
Content-Type: multipart/alternative;
 boundary="------------D4132C46D49BE8F672A61E01"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/94ed2da7-7622-e57b-1562-3f7caf40b4a8%40mailpen.com>
Precedence: bulk

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

On 2021-05-29 09:25, Adrian Klaver wrote:
> On 5/28/21 5:38 PM, Dean Gibson (DB Administrator) wrote:
>
> Can you repeat your EXPLAIN (ANALYZE, BUFFERS) of the query from your 
> first post and post them here:
>
> https://explain.depesz.com/
>
> Other information:
> 1) A diff of your configuration settings between 9.6 and 13.2.
>
> 2) Are you running on the same AWS instance type for the two versions 
> of Postgres?
>
> It is not necessary to repeat the table/view definitions as they are 
> available in the first post.

Done.

1.There's probably about a hundred, but almost all are differences in 
the default values.  The most interesting (from my point of view) is my 
setting work_mem in 8000 on v9.6, & 16000 (after 8000 didn't help) on 
v13.  Doing a compare right now between the DEFAULT parameters for 9.6 & 
13, RDS reports 93 differences in the default parameters between the two.

2. For v13, I moved from db.t2.micro to db.t3.micro, because RDS 
required that for v13.  However, for the v10, 11, 12 upgrades, I kept 
db.t2.micro.

Meanwhile, I've been doing some checking.  If I remove "CAST( 
license_status AS CHAR ) = 'A'", the problem disappears.  Changing the 
JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the 
problem, but there is an extra row where license_status is NULL, due to 
the RIGHT JOIN.  Currently trying to figure that out (why did the CAST 
... match 'A', if it is null?)...


--------------D4132C46D49BE8F672A61E01
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-29 09:25, Adrian Klaver
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:1a2f01ce-b3da-3066-0642-3e311587ece3@aklaver.com">On
      5/28/21 5:38 PM, Dean Gibson (DB Administrator) wrote:
      <br>
      <br>
      Can you repeat your EXPLAIN (ANALYZE, BUFFERS) of the query from
      your first post and post them here:
      <br>
      <br>
      <a class="moz-txt-link-freetext" href="https://explain.depesz.com/">https://explain.depesz.com/</a>
      <br>
      <br>
      Other information:
      <br>
      1) A diff of your configuration settings between 9.6 and 13.2.
      <br>
      <br>
      2) Are you running on the same AWS instance type for the two
      versions of Postgres?
      <br>
      <br>
      It is not necessary to repeat the table/view definitions as they
      are available in the first post.<br>
    </blockquote>
    <br>
    Done.<br>
    <br>
    1.There's probably about a hundred, but almost all are differences
    in the default values.  The most interesting (from my point of view)
    is my setting work_mem in 8000 on v9.6, &amp; 16000 (after 8000
    didn't help) on v13.  Doing a compare right now between the DEFAULT
    parameters for 9.6 &amp; 13, RDS reports 93 differences in the
    default parameters between the two. <br>
    <br>
    2. For v13, I moved from db.t2.micro to db.t3.micro, because RDS
    required that for v13.  However, for the v10, 11, 12 upgrades, I
    kept db.t2.micro.<br>
    <br>
    Meanwhile, I've been doing some checking.  If I remove "CAST(
    license_status AS CHAR ) = 'A'", the problem disappears.  Changing
    the JOIN to a RIGHT JOIN, &amp; replacing WHERE with ON, also
    "solves" the problem, but there is an extra row where license_status
    is NULL, due to the RIGHT JOIN.  Currently trying to figure that out
    (why did the CAST ... match 'A', if it is null?)...<br>
    <br>
  </body>
</html>

--------------D4132C46D49BE8F672A61E01--