Re: CPU load

Поиск
Список
Период
Сортировка
От Albe Laurenz
Тема Re: CPU load
Дата
Msg-id D960CB61B694CF459DCFB4B0128514C2028AAD22@exadv11.host.magwien.gv.at
обсуждение исходный текст
Ответ на Re: CPU load  (kiki@fesb.hr)
Ответы Re: CPU load  (kiki@fesb.hr)
Список pgsql-performance
kiki wrote:
> First I have increased shared_buffers from 2000 to 8000. Since the
> postgresql is on Debian I had to increase SHMMAX kernel value.
> Everything is working much faster now.

Good to hear that the problem is gone.

> There is still heavy load of postmaster process (up to 100%) for a simple
> query
>
> EXPLAIN ANALYSE  SELECT * FROM system_alarm WHERE id_camera='3' AND
> confirmed='false' AND dismissed='false' ORDER BY date DESC, time DESC
> LIMIT 1;
>
> (the table is indexed by id_camera, has around 1 milion rows, and this
> query returns around 700000 rows and is executed (EXPLAIN ANALYSE) in
> around 4800 ms, and this table is queried a lot although not so often
> queried modified)
>
> but I don't think that is strange behavior of the postgresql.
> And it is exhibited all the time; the postgresql reset does not influence
> it at all.

I'd expect a sequential scan for a query that returns 70% of the table.

But I cannot believe that this query returns more than one row since
it has a "LIMIT 1". Can you enlighten me?

In the above query (with LIMIT 1), maybe an index on "date" could help.

Yours,
Laurenz Albe


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

Предыдущее
От: "Harald Armin Massa"
Дата:
Сообщение: Re: CPU load
Следующее
От: kiki@fesb.hr
Дата:
Сообщение: Re: CPU load