The problem is the plan. The planner massively underestimated the number of rows arising from the _EN/_AM join.
Usually postgres is pretty good about running ANALYZE as needed, but it might be a good idea to run it manually to rule that out as a potential culprit. On Fri, May 28, 2021 at 3:19 PM Campbell, Lance <la...@illinois.edu> wrote: > Also, did you check your RDS setting in AWS after upgrading? I run four > databases in AWS. I found that the work_mem was set way low after an > upgrade. I had to tweak many of my settings. > > > > Lance > > > > *From: *Andrew Dunstan <and...@dunslane.net> > *Date: *Friday, May 28, 2021 at 2:08 PM > *To: *Dean Gibson (DB Administrator) <postgre...@mailpen.com>, > pgsql-performance@lists.postgresql.org < > pgsql-performance@lists.postgresql.org> > *Subject: *Re: AWS forcing PG upgrade from v9.6 a disaster > > > 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://urldefense.com/v3/__https://www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$ > <https://urldefense.com/v3/__https:/www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$> > > >
