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