Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
To: pgsql-performance@postgresql.org
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>
From: "Dean Gibson (DB Administrator)" <postgresql@mailpen.com>
Organization: UltiMeth Systems
Message-ID: <a7a3e931-0267-23bb-4af7-f54a85d3e12e@mailpen.com>
Date: Sat, 29 May 2021 15:47:39 -0700
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.10.2
MIME-Version: 1.0
In-Reply-To: <557397e1-096c-c879-e0fc-80dccb36fa15@jtek.fr>
Content-Type: multipart/alternative;
 boundary="------------9CE8351552A512156F28B67A"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/a7a3e931-0267-23bb-4af7-f54a85d3e12e%40mailpen.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------9CE8351552A512156F28B67A
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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/


--------------9CE8351552A512156F28B67A
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    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.<br>
    <br>
    <div class="moz-cite-prefix">On 2021-05-29 03:40, Lionel Bouton
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:557397e1-096c-c879-e0fc-80dccb36fa15@jtek.fr">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <div class="moz-cite-prefix">Le 29/05/2021 à 02:38, Dean Gibson
        (DB Administrator) a écrit :<br>
      </div>
      <blockquote type="cite"
        cite="mid:988e2438-c322-c1d5-ca88-683943a80087@mailpen.com">
        <meta http-equiv="Content-Type" content="text/html;
          charset=UTF-8">
        The original VACUUM FULL ANALYZE ran in 10 hours.  The plain
        ANALYZE ran in 88 seconds.<br>
      </blockquote>
      <br>
      One possibility is that your data has a distribution that defeats
      the ANALYZE sampling strategy.<br>
      <br>
      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.<br>
      The last time I had to use this setting to solve this kind of
      problem I ended with :<br>
      <br>
      default_statistics_target = 500<br>
      <br>
      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.<br>
      <br>
      For reference and more fine-tuned settings using per table
      statistics configuration and multi-column statistics for complex
      situations, see :<br>
      - <a class="moz-txt-link-freetext"
        href="https://www.postgresql.org/docs/13/runtime-config-query.html"
        moz-do-not-send="true">https://www.postgresql.org/docs/13/runtime-config-query.html</a><br>
      - <a class="moz-txt-link-freetext"
        href="https://www.postgresql.org/docs/13/planner-stats.html"
        moz-do-not-send="true">https://www.postgresql.org/docs/13/planner-stats.html</a><br>
      <br>
      <pre class="moz-signature" cols="72">-- 
Lionel Bouton
gérant de JTEK SARL
<a class="moz-txt-link-freetext" href="https://www.linkedin.com/in/lionelbouton/" moz-do-not-send="true">https://www.linkedin.com/in/lionelbouton/</a>
</pre>
    </blockquote>
    <br>
  </body>
</html>

--------------9CE8351552A512156F28B67A--