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?
I didn't have the time in March to diagnose the problem, other than some
futile adjustments to server parameters, so I reverted back to a saved
copy of my v9.6 data.
On Sunday, being retired, I decided to attempt to solve the issue in
earnest. I have now spent five days (about 14 hours a day), trying
various things. Keeping the v9.6 data online for web users, I've
"forked" the data into a new copy, & updated it in turn to PostgreSQL
v10, v11, v12, & v13. All exhibit the same problem: As you will see
below, it appears that versions 10 & above are doing a sequential scan
of some of the "large" (200K rows) tables. Note that the expected &
actual run times for v9.6 & v13.2 both differ by more than *two orders
of magnitude*. Rather than post a huge eMail (ha ha), I'll start with
this one, that shows an "EXPLAIN ANALYZE" from both v9.6 & v13.2,
followed by the related table & view definitions. With one exception,
table definitions are from the FCC (Federal Communications Commission);
the view definitions are my own.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
