Content-Type: multipart/alternative;
 boundary="------------188NfgTp2HgTsaNCrU0hZ1qL"
Message-ID: <62c9a8d8-8c0f-3c11-5cca-e05e126c25d8@gmail.com>
Date: Sun, 21 Aug 2022 14:27:50 -0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.12.0
Subject: Re: Postgresql 14 performance
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <CAC0w7LL+uFgPvctiHMxrVZV-DnNVg2K_wFaeZwM96XQFkjk1uA@mail.gmail.com>
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: 
 <CAC0w7LL+uFgPvctiHMxrVZV-DnNVg2K_wFaeZwM96XQFkjk1uA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/62c9a8d8-8c0f-3c11-5cca-e05e126c25d8%40gmail.com>
Precedence: bulk

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

On 8/21/22 12:05, Kenny Bachman wrote:
> Hi Team,
>
> Have a lovely Sunday.
> I have a problem with PostgreSQL performance. I am using PostgreSQL 
> 14.5 on Linux physical server. My query was taking 1 or 2 seconds 
> yesterday, but today it is taking 5-6 seconds. Also, nothing has 
> changed in the database. Also, when I run the query for the second 
> time, the query runs for the same time, so I expect it to be more 
> performant the second time because of the cache.
> (the database cache hit ratio is 97,8)
>
> Lastly, some other queries take milliseconds normally, but sometimes 
> they take minutes. I didn't understand why this is happening. (There 
> is no lock, no table bloating and up to date analyze)
>
>
> I am adding the explain analyze output as an attachment.

Hi Kenny,

The query is small but the plan is huge and likely to use a lot of 
memory. What is with the rest of the system? Any paging? There are lots 
of steps that are never executed in the plan. The plan looks great, 
mostly unique index equality scans. Did you check pg_stat_activity for 
wait_event_type and wait_event? What is your SQL waiting for?

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--------------188NfgTp2HgTsaNCrU0hZ1qL
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 8/21/22 12:05, Kenny Bachman wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAC0w7LL+uFgPvctiHMxrVZV-DnNVg2K_wFaeZwM96XQFkjk1uA@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Hi Team,
        <div><br>
        </div>
        <div>Have a lovely Sunday. </div>
        <div>I have a problem with PostgreSQL performance. I am using
          PostgreSQL 14.5 on Linux physical server. My query was taking
          1 or 2 seconds yesterday, but today it is taking 5-6 seconds.
          Also, nothing has changed in the database. Also, when I run
          the query for the second time, the query runs for the same
          time, so I expect it to be more performant the second time
          because of the cache.<br>
        </div>
        <div>(the database cache hit ratio is 97,8)</div>
        <div><br>
        </div>
        <div>Lastly, some other queries take milliseconds normally, but
          sometimes they take minutes. I didn't understand why this is
          happening. (There is no lock, no table bloating and up to date
          analyze)</div>
        <div><br>
        </div>
        <div><br>
        </div>
        <div>I am adding the explain analyze output as an attachment.</div>
      </div>
    </blockquote>
    <p>Hi Kenny,</p>
    <p>The query is small but the plan is huge and likely to use a lot
      of memory. What is with the rest of the system? Any paging? There
      are lots of steps that are never executed in the plan. The plan
      looks great, mostly unique index equality scans. Did you check
      pg_stat_activity for wait_event_type and wait_event? What is your
      SQL waiting for?</p>
    <p>Regards<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>

--------------188NfgTp2HgTsaNCrU0hZ1qL--