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

Le 29/05/2021 à 02:38, Dean Gibson (DB Administrator) a écrit :
> On 2021-05-28 16:51, Ron wrote:
>> On 5/28/21 5:06 PM, Dean Gibson (DB Administrator) wrote:
>>> 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.
>>
>> Did it run in less than 10 hours?
>>
>
> The original VACUUM FULL ANALYZE ran in 10 hours.  The plain ANALYZE
> ran in 88 seconds.

One possibility is that your data has a distribution that defeats the
ANALYZE sampling strategy.

If that is the case you can force ANALYZE to do a better job by
increasing the default_statistics_target value (100 by default) and
reload the configuration. This will sample more data from your table
which should help the planner find out what the value distribution looks
like for a column and why using an index for conditions involving it is
a better solution.
The last time I had to use this setting to solve this kind of problem I
ended with :

default_statistics_target = 500

But obviously the value suited to your case could be different (I'd
increase it until the planner uses the correct index). Note that
increasing it increases the costs of maintaining statistics (so you
don't want to increase this by several orders of magnitude blindly) but
the default value seems fairly conservative to me.

For reference and more fine-tuned settings using per table statistics
configuration and multi-column statistics for complex situations, see :
- https://www.postgresql.org/docs/13/runtime-config-query.html
- https://www.postgresql.org/docs/13/planner-stats.html

--
Lionel Bouton
gérant de JTEK SARL
https://www.linkedin.com/in/lionelbouton/