Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
To: pgsql-performance@lists.postgresql.org
References: <88dec70b-eb63-0b72-0b5d-253851ef127b@mailpen.com>
 <163214e4-0588-230b-9ed5-f682087e0e18@wi3ck.info>
 <4e238cc5-f63e-0435-63bf-2515500d5424@dunslane.net>
 <20210528213052.GC5649@momjian.us>
 <E007EEB7-097D-4D0E-9C4D-C8BB28810E7F@thebuild.com>
 <cf15c19d-43d8-95ff-17a0-ded099f70861@mailpen.com>
 <40d30a5c-ae45-3caf-f74a-f091e8ad0a2c@yandex.ru>
From: "Dean Gibson (DB Administrator)" <postgresql@mailpen.com>
Organization: UltiMeth Systems
Message-ID: <82627a7c-98ca-b60c-c49a-0f1ca132a639@mailpen.com>
Date: Sat, 29 May 2021 13:17:40 -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: <40d30a5c-ae45-3caf-f74a-f091e8ad0a2c@yandex.ru>
Content-Type: multipart/alternative;
 boundary="------------2598C1860DEFA39395C5CAF1"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/82627a7c-98ca-b60c-c49a-0f1ca132a639%40mailpen.com>
Precedence: bulk

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

On 2021-05-28 22:24, Alexey M Boltenkov wrote:
> On 05/29/21 07:08, Dean Gibson (DB Administrator) wrote: [deleted]
>
> BTW what is the planner reason to not use index in v13.2? Is index in 
> corrupted state? Have you try to reindex index 
> "FccLookup"."_LicStatus_pkey" ?
>
> 1.5M of seqscan's are looking really bad.
>
>                      SubPlan 2
>                        ->  Limit  (cost=0.15..8.17 rows=1 width=32) 
> (actual time=0.006..0.007 rows=1 loops=55)
>                              -> *Index Scan using "_LicStatus_pkey" on 
> "_LicStatus"*  (cost=0.15..8.17 rows=1 width=32) (actual 
> time=0.005..0.005 rows=1 loops=55)
>                                    Index Cond: ("_HD".license_status = 
> status_id)
>
>
> SubPlan 2
>                        ->  Limit  (cost=0.00..1.07 rows=1 width=13) 
> (actual time=0.001..0.001 rows=1 loops=1487153)
>                              -> *Seq Scan on "_LicStatus"*  
> (cost=0.00..1.07 rows=1 width=13) (actual time=0.000..0.000 rows=1 
> loops=1487153)
>                                    Filter: ("_HD".license_status = 
> status_id)
>                                    Rows Removed by Filter: 1
>

Doing your REINDEX didn't help.  Now in the process of reindexing the 
entire database.  When that's done, I'll let you know if there is any 
improvement.

--------------2598C1860DEFA39395C5CAF1
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>
    <div class="moz-cite-prefix">On 2021-05-28 22:24, Alexey M Boltenkov
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:40d30a5c-ae45-3caf-f74a-f091e8ad0a2c@yandex.ru">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <div class="moz-cite-prefix">On 05/29/21 07:08, Dean Gibson (DB
        Administrator) wrote: [deleted]<br>
      </div>
      <p><font size="-2"><tt>BTW what is the planner reason to not use
            index in v13.2? Is index in corrupted state? Have you try to
            reindex index </tt></font><font size="-2"><tt><font
              face="monospace">"FccLookup"."_LicStatus_pkey" ?<br>
            </font></tt></font></p>
      <p><font size="-2"><tt><font face="monospace">1.5M of seqscan's
              are looking really bad.<br>
            </font></tt></font></p>
      <p><font size="-1" color="#007f00">                     SubPlan 2<br>
                                 -&gt;  Limit  (cost=0.15..8.17 rows=1
          width=32) (actual time=0.006..0.007 rows=1 loops=55)<br>
                                       -&gt;  <b>Index Scan using
            "_LicStatus_pkey" on "_LicStatus"</b>  (cost=0.15..8.17
          rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=55)<br>
                                             Index Cond:
          ("_HD".license_status = status_id)</font></p>
      <p><br>
        <font size="-1" color="#007f00"><font size="-1" color="#bf0000">                    
            SubPlan 2<br>
                                   -&gt;  Limit  (cost=0.00..1.07 rows=1
            width=13) (actual time=0.001..0.001 rows=1 loops=1487153)<br>
                                         -&gt;  <b>Seq Scan on
              "_LicStatus"</b>  (cost=0.00..1.07 rows=1 width=13)
            (actual time=0.000..0.000 rows=1 loops=1487153)<br>
                                               Filter:
            ("_HD".license_status = status_id)<br>
                                               Rows Removed by Filter: 1</font></font></p>
    </blockquote>
    <br>
    Doing your REINDEX didn't help.  Now in the process of reindexing
    the entire database.  When that's done, I'll let you know if there
    is any improvement.<br>
  </body>
</html>

--------------2598C1860DEFA39395C5CAF1--