kiki wrote: > I expanded work_mem to 256 Mb and created index on table > > create index xxx on system_alarm (id_camera, date, time) where confirmed = > 'false' and dismissed = 'false'; That index is not used for the query (as could be expected). You better remove it. > the processor load now executing the query is max. 70% > > the query execution with and without order is: > > istra_system=> EXPLAIN ANALYSE SELECT * FROM system_alarm WHERE > id_camera='3' AND confirmed='false' AND dismissed='false' ; > > Seq Scan on system_alarm (cost=0.00..24468.33 rows=735284 width=47) (actual time=90.792..1021.967 rows=724846 loops=1) > Filter: ((id_camera = 3) AND (NOT confirmed) AND (NOT dismissed)) > Total runtime: 1259.426 ms > (3 rows) > > istra_system=> EXPLAIN ANALYSE SELECT * FROM system_alarm WHERE > id_camera='3' AND confirmed='false' AND dismissed='false' ORDER BY date > DESC, time ; > > Sort (cost=96114.18..97952.39 rows=735284 width=47) (actual time=2303.547..2602.116 rows=724846 loops=1) > Sort Key: date, "time" > -> Seq Scan on system_alarm (cost=0.00..24468.33 rows=735284 width=47) (actual time=100.322..1115.837 rows=724846 loops=1) > Filter: ((id_camera = 3) AND (NOT confirmed) AND (NOT dismissed)) > Total runtime: 2916.557 ms > (5 rows) > > I think this is OK. I think so too. I would say it is OK for the query to use much CPU during sort as long as this does not last for too long. Yours, Laurenz Albe
