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.
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
