Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1BFD737BABD for ; Mon, 29 Sep 2008 10:07:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 41219-04 for ; Mon, 29 Sep 2008 10:07:15 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from marjan.fesb.hr (marjan.fesb.hr [161.53.166.3]) by postgresql.org (Postfix) with ESMTP id 97EA737BA5B for ; Mon, 29 Sep 2008 10:07:15 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by marjan.fesb.hr (Postfix) with ESMTP id 89BA830291; Mon, 29 Sep 2008 15:07:12 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at marjan.fesb.hr Received: from marjan.fesb.hr ([127.0.0.1]) by localhost (marjan.fesb.hr [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id O0juTeDX7cUH; Mon, 29 Sep 2008 15:07:09 +0200 (CEST) Received: from www.fesb.hr (localhost [127.0.0.1]) by marjan.fesb.hr (Postfix) with ESMTP id F203F303BC; Mon, 29 Sep 2008 15:07:08 +0200 (CEST) Received: from 161.53.168.65 (SquirrelMail authenticated user kiki) by www.fesb.hr with HTTP; Mon, 29 Sep 2008 15:07:08 +0200 (CEST) Message-ID: <53178.161.53.168.65.1222693628.squirrel@www.fesb.hr> In-Reply-To: References: <50724.193.198.80.76.1222370001.squirrel@www.fesb.hr> <49663.161.53.168.65.1222428487.squirrel@www.fesb.hr> <49550.161.53.168.65.1222672642.squirrel@www.fesb.hr> <7be3f35d0809290026o43e7879fu760d1ddbc360b177@mail.gmail.com> <50519.161.53.168.65.1222677569.squirrel@www.fesb.hr> Date: Mon, 29 Sep 2008 15:07:08 +0200 (CEST) Subject: Re: CPU load From: kiki@fesb.hr To: "Albe Laurenz" Cc: "Harald Armin Massa" , pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.9a MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-2 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200809/261 X-Sequence-Number: 31574 > 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