Re: Postgresql 14 performance

Hello Jeff,

Thank you for your response.

>Do you mean just no structural changes, or no changes of any kind?
I mean, no database parameter changes were made.

> Did they start to "sometimes take minutes" just today, or was that a pre
existing issue?
Not just today. Sometimes it takes 50 seconds, sometimes 20 seconds,
sometimes 10 milliseconds. (at completely random times.)

> How up to date are the stats? Did you just finish ANALYZE right before
you captured the plan?
Yes, I ran the VACUUM ANALYZE command for the all tables of the view and I
captured the plan after the vacuum analyze operation finished.

> Do you happen to have one for the same query from before the problem
started? Also, it would be better to capture BUFFERS as part of the plan,
and preferably with track_io_timing turned on
I added the exec plan with the buffers option, and I don't have query plan
from before the problem.

Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, 21 Ağu 2022 Paz, 22:06 tarihinde şunu
yazdı:

> On Sun, Aug 21, 2022 at 12:05 PM Kenny Bachman <kenny(dot)bachman17(at)gmail(dot)com>
> 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.
>>
>
> Do you mean just no structural changes, or no changes of any kind?
>
>
>> 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)
>>
>
> Did they start to "sometimes take minutes" just today, or was that a pre
> existing issue?
>
> How up to date are the stats? Did you just finish ANALYZE right before
> you captured the plan?
>
> This estimate looks pretty horrible and probably a problem with your stats:
>
> -> Index Scan using "IX_T_WF_STEP_StepDefTypeCd" on "T_WF_STEP"
> wf_cstp (cost=0.43..1098968.54 rows=4019 width=8) (actual
> time=0.096..4278.668 rows=802420 loops=1)
>
> And this one suggests you vacuuming is not very up to date:
>
> Heap Fetches: 803045
>
>
>>
>> I am adding the explain analyze output as an attachment.
>>
>
> Do you happen to have one for the same query from before the problem
> started? Also, it would be better to capture BUFFERS as part of the plan,
> and preferably with track_io_timing turned on.
>
> Cheers,
>
> Jeff
>