Re: AWS forcing PG upgrade from v9.6 a disaster

pá 28. 5. 2021 v 21:39 odesílatel Ryan Bair <ryandbair(at)gmail(dot)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 <lance(at)illinois(dot)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 <andrew(at)dunslane(dot)net>
>> *Date: *Friday, May 28, 2021 at 2:08 PM
>> *To: *Dean Gibson (DB Administrator) <postgresql(at)mailpen(dot)com>,
>> pgsql-performance(at)lists(dot)postgresql(dot)org <
>> pgsql-performance(at)lists(dot)postgresql(dot)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$>
>>
>>
>>