Re: Intermittent Query Performance Issue

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Intermittent Query Performance Issue
Дата
Msg-id CAMkU=1wj5vy+gqze=0QaEmhPypNJEL493a-k5qZx3JgBhN1uMw@mail.gmail.com
обсуждение исходный текст
Ответ на Intermittent Query Performance Issue  (Murthy Nunna <mnunna@fnal.gov>)
Список pgsql-admin
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.

Cheers,

Jeff

В списке pgsql-admin по дате отправления:

Предыдущее
От: Sathish Reddy
Дата:
Сообщение: Set fillfactor to partition child tables
Следующее
От: Jeff Janes
Дата:
Сообщение: Re: Bg_writer and checkpointer