RE: Intermittent Query Performance Issue

[Murthy Nunna <mnunna@xxxxxxxx>]

There are no other queries running at the time. pg_stat_activity shows only this long running query.

 

From: Ron Johnson <ronljohnsonjr@xxxxxxxxx>
Sent: Friday, April 19, 2024 9:08 PM
To: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: Intermittent Query Performance Issue

 

[EXTERNAL] – This message is from an external sender

On Fri, Apr 19, 2024 at 1:03 PM Murthy Nunna <mnunna@xxxxxxxx> wrote:

Hi,

 

I am running pg 14.4

 

I have a simple query :

select max(c) from tab1 where name = 'xxx’ ;

 

This query runs some times very slow. It takes about 40 minutes.

Most of the time it completes in few seconds.

When it runs slow, wait_event in pg_stat_activity shows “DataFileRead”. Obviously, it is reading from file storage and that is why it is taking so long.

 

What else is happening on that system when it runs very slow?  To me, “DataFileRead” screams IO contention.

 

 This is a big table 512GB. It has some child tables.

 

Is there a way I can reproduce the slowness at will, so I can attempt to tune it? Or any ideas I can try to fix the issue? I hate to try something not knowing if the problem is fixed or not. That is the reason I would like to reproduce the issue first.