Please try to avoid top-posting where inappropriate.
kiki wrote:
>>> 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.
>
> 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.
Yes, for the query without the LIMIT clause I wouldn't expect any gain from
indexing.
Probably the CPU load is caused by the sorting.
Does it look different if you omit ORDER BY?
Maybe the sort will perform better if you increase work_mem in postgresql.conf,
you could experiment with that.
Yours,
Laurenz Albe
