pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: kiki@fesb.hr
To: Albe Laurenz <laurenz.albe@wien.gv.at>
Cc: Harald Armin Massa <haraldarminmassa@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: CPU load
Date: Mon, 29 Sep 2008 15:07:08 +0200 (CEST)
Message-ID: <53178.161.53.168.65.1222693628.squirrel@www.fesb.hr> (raw)
In-Reply-To: <D960CB61B694CF459DCFB4B0128514C202901614@exadv11.host.magwien.gv.at>
References: <50724.193.198.80.76.1222370001.squirrel@www.fesb.hr>
	<dcc563d10809251300i7e8dd8aej12d08a4082671bbf@mail.gmail.com>
	<D960CB61B694CF459DCFB4B0128514C2028AA976@exadv11.host.magwien.gv.at>
	<49663.161.53.168.65.1222428487.squirrel@www.fesb.hr>
	<D960CB61B694CF459DCFB4B0128514C2028AABB1@exadv11.host.magwien.gv.at>
	<a1ec7d000809260801q17238d58wa6e4731e3a1d795b@mail.gmail.com>
	<49550.161.53.168.65.1222672642.squirrel@www.fesb.hr>
	<7be3f35d0809290026o43e7879fu760d1ddbc360b177@mail.gmail.com>
	<50519.161.53.168.65.1222677569.squirrel@www.fesb.hr>
	<D960CB61B694CF459DCFB4B0128514C202901614@exadv11.host.magwien.gv.at>

> 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 don’t 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




view thread (16+ messages)  latest in thread

Message-ID: <53178.161.53.168.65.1222693628.squirrel@www.fesb.hr>
Permalink:  ../53178.161.53.168.65.1222693628.squirrel@www.fesb.hr/
Also on:    postgresql.org/message-id/53178.161.53.168.65.1222693628.squirrel@www.fesb.hr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: kiki@fesb.hr, laurenz.albe@wien.gv.at, haraldarminmassa@gmail.com
  Subject: Re: CPU load
  In-Reply-To: <53178.161.53.168.65.1222693628.squirrel@www.fesb.hr>
  References: <50724.193.198.80.76.1222370001.squirrel@www.fesb.hr> <dcc563d10809251300i7e8dd8aej12d08a4082671bbf@mail.gmail.com> <D960CB61B694CF459DCFB4B0128514C2028AA976@exadv11.host.magwien.gv.at> <49663.161.53.168.65.1222428487.squirrel@www.fesb.hr> <D960CB61B694CF459DCFB4B0128514C2028AABB1@exadv11.host.magwien.gv.at> <a1ec7d000809260801q17238d58wa6e4731e3a1d795b@mail.gmail.com> <49550.161.53.168.65.1222672642.squirrel@www.fesb.hr> <7be3f35d0809290026o43e7879fu760d1ddbc360b177@mail.gmail.com> <50519.161.53.168.65.1222677569.squirrel@www.fesb.hr> <D960CB61B694CF459DCFB4B0128514C202901614@exadv11.host.magwien.gv.at> <53178.161.53.168.65.1222693628.squirrel@www.fesb.hr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox