Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

Next:From: paulDate: 2008-09-29 13:25:53
Subject: dedicated server & postgresql 8.1 conf tunning
Previous:From: kikiDate: 2008-09-29 13:07:08
Subject: Re: CPU load

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group