From: | kiki(at)fesb(dot)hr |
---|---|
To: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
Cc: | "Harald Armin Massa" <haraldarminmassa(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: CPU load |
Date: | 2008-09-29 13:07:08 |
Message-ID: | 53178.161.53.168.65.1222693628.squirrel@www.fesb.hr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> kiki wrote:
>> The speed of the query is not a problem but the strange thing is the
>> processor load with postmaster when the query is executed.
>> I dont now how to reduce processor load.
>
> Did you try without the ORDER BY?
> Where are the execution plans?
>
> Yours,
> Laurenz Albe
>
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';
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.
Thanx
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2008-09-29 13:15:27 | Re: CPU load |
Previous Message | Albe Laurenz | 2008-09-29 12:06:44 | Re: CPU load |