On 2021-05-28 12:38, Ron wrote:
> On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
>> On 2021-05-28 08:12, Adrian Klaver wrote:
>>> 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?
>>
>> After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL
>> ANALYZE". On 10 through 12, it took about 45 minutes & significant
>> CPU activity, & temporarily doubled the size of the disk space
>> required. As you know, that disk space is not shrinkable under AWS's
>> RDS. On v13, it took 10 hours with limited CPU activity, & actually
>> slightly less disk space required.
>
> Under normal conditions, VACUUM FULL is pointless on a freshly-loaded
> database; in RDS, it's *anti-useful*.
>
> That's why Adrian asked if you did a plain ANALYZE.
Just now did. No change in EXPLAIN ANALYZE output.