Re: AWS forcing PG upgrade from v9.6 a disaster

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?)...