From: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
To: pgsql-performance@postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
Date: Sat, 29 May 2021 12:59:47 -0700
Message-ID: <94ed2da7-7622-e57b-1562-3f7caf40b4a8@mailpen.com> (raw)
In-Reply-To: <1a2f01ce-b3da-3066-0642-3e311587ece3@aklaver.com>
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>
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?)...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: postgresql@mailpen.com
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
In-Reply-To: <94ed2da7-7622-e57b-1562-3f7caf40b4a8@mailpen.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox