pá 28. 5. 2021 v 21:39 odesílatel Ryan Bair <ryandb...@gmail.com> napsal:
> 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. > yes the very strange is pretty high planning time Planning Time: 173.753 ms This is unusually high number - maybe the server has bad CPU or maybe some indexes bloating Regards Pavel 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$> >> >> >>
