Re: CPU load

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

In response to

Browse pgsql-performance by date

  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