On 2021-05-28 12:38, Ron wrote:
On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems
(4 at one point), gradually moving to v9.0 w/ replication in 2010.
In 2017 I moved my 20GB database to AWS/RDS, gradually upgrading to
v9.6, & was entirely satisfied with the result.
In March of this year, AWS announced that v9.6 was nearing end of
support, & AWS would forcibly upgrade everyone to v12 on January
22, 2022, if users did not perform the upgrade earlier. My first
attempt was successful as far as the upgrade itself, but complex
queries that normally ran in a couple of seconds on v9.x, were
taking minutes in v12.
Did you run a plain
ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the
tables in the new install?
After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL
ANALYZE". On 10 through 12, it took about 45 minutes & significant
CPU activity, & temporarily doubled the size of the disk space
required. As you know, that disk space is not shrinkable under AWS's
RDS. On v13, it took 10 hours with limited CPU activity, & actually
slightly less disk space required.
Under normal conditions, VACUUM FULL is pointless on a freshly-loaded
database; in RDS, it's *anti-useful*.
That's why Adrian asked if you did a plain ANALYZE.
Just now did. No change in EXPLAIN ANALYZE output.
