On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote: > [Reposted to the proper list] > > 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. > > 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, including adding additional indexes. Keeping the v9.6 > data online for web users, I've "forked" the data into new copies, & > updated them 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 both differ for > v9.6 & v13.2, 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. > > > Have you tried reproducing these results outside RDS, say on an EC2 instance running vanilla PostgreSQL? cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
