Re: Postgresql 14 performance

Hi Mladen,

Thank you for your help.

Yes, the plan is huge, because there is a view object. The system has a
very low load today and there are 2-3 active sessions normally, no
long-running query.
I checked the activity view, and there is no wait event for my query,
which has a NULL value for the wait event column.

Here is my memory status:

total used free shared buff/cache
available
Mem: 251 6 1 24 243
220

How can we explain the cache doesn't work as expected? I mean, I expect
more performant the second or third time running because of the cache.
However, every time I run it I get the same exec time value.

Mladen Gogala <gogala(dot)mladen(at)gmail(dot)com>, 21 Ağu 2022 Paz, 21:28 tarihinde
şunu yazdı:

> 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-1217https://dbwhisperer.wordpress.com
>
>