Re: Intermittent Query Performance Issue

On Fri, Apr 19, 2024 at 1:02 PM Murthy Nunna <mnunna(at)fnal(dot)gov> 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.
>

It sounds like your query is walking down an index on "c", then stopping
once it finds a single row where name = 'xxx’. How long this will take
depends on how high the max value within 'xxx' is relative to all the other
values. If that is the case, then the constant value for 'xxx' should be
slow every time, until the data changes. This could be fixed by having a
multicolumn index on (name, c).

An alternative explanation could be a huge chunk of rows with a high value
of c have recently been deleted, or have been inserted but not committed.
Then your query would need to wage through all those invisible rows looking
for one visible one.

Cheers,

Jeff

>