From: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
To: pgsql-performance@postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
Date: Sat, 29 May 2021 15:47:39 -0700
Message-ID: <a7a3e931-0267-23bb-4af7-f54a85d3e12e@mailpen.com> (raw)
In-Reply-To: <557397e1-096c-c879-e0fc-80dccb36fa15@jtek.fr>
References: <3fa6fd38-1f92-98ab-0055-3e0eb7e4e772@ultimeth.com>
<2a1b34f4-73d4-585b-f4fc-3bd9b9ba248e@aklaver.com>
<88255b75-e7e3-b29c-0e75-f989ea320570@mailpen.com>
<70edb767-6ba0-1065-2f74-f01440e3caea@gmail.com>
<d73e2731-31ba-df14-9b9c-974342200ae8@mailpen.com>
<2b7b9a25-e3c7-b980-a1e3-d21d8d91d34d@gmail.com>
<988e2438-c322-c1d5-ca88-683943a80087@mailpen.com>
<557397e1-096c-c879-e0fc-80dccb36fa15@jtek.fr>
I tried 500, to no avail. Since each change involves a delay as RDS
readjusts, I'm going down a different path at the moment.
On 2021-05-29 03:40, Lionel Bouton wrote:
> Le 29/05/2021 à 02:38, Dean Gibson (DB Administrator) a écrit :
>> 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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: postgresql@mailpen.com
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
In-Reply-To: <a7a3e931-0267-23bb-4af7-f54a85d3e12e@mailpen.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox