From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | <kiki(at)fesb(dot)hr> |
Cc: | "Harald Armin Massa" <haraldarminmassa(at)gmail(dot)com>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: CPU load |
Date: | 2008-09-29 13:15:27 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C20290164E@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | paul | 2008-09-29 13:25:53 | dedicated server & postgresql 8.1 conf tunning |
Previous Message | kiki | 2008-09-29 13:07:08 | Re: CPU load |