Re: CPU load

Поиск
Список
Период
Сортировка
От kiki@fesb.hr
Тема Re: CPU load
Дата
Msg-id 50443.161.53.168.65.1222676985.squirrel@www.fesb.hr
обсуждение исходный текст
Ответ на Re: CPU load  ("Albe Laurenz" <laurenz.albe@wien.gv.at>)
Ответы Re: CPU load  (hubert depesz lubaczewski <depesz@depesz.com>)
Re: CPU load  ("Albe Laurenz" <laurenz.albe@wien.gv.at>)
Список pgsql-performance
Sorry, without LIMIT returns around 700000 rows.
Tried to index date column and time column but the performance is pretty
much the same.
Everything is OK, I just don’t understand way is this query burdening the
processor so much.

Regards,
Maja

> 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
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>



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

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