Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5CB5637B9D3 for ; Mon, 29 Sep 2008 05:46:49 -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 74441-07 for ; Mon, 29 Sep 2008 05:46:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from depesz.com (depesz.com [78.46.78.198]) by postgresql.org (Postfix) with ESMTP id 6576E37B9B1 for ; Mon, 29 Sep 2008 05:46:37 -0300 (ADT) Received: from depesz by depesz.com with local (Exim 4.69) (envelope-from ) id 1KkEOk-0006zM-V2; Mon, 29 Sep 2008 10:46:31 +0200 Date: Mon, 29 Sep 2008 10:46:30 +0200 From: hubert depesz lubaczewski To: kiki@fesb.hr Cc: Albe Laurenz , pgsql-performance@postgresql.org Subject: Re: CPU load Message-ID: <20080929084630.GA26233@depesz.com> Reply-To: depesz@depesz.com 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> <50443.161.53.168.65.1222676985.squirrel@www.fesb.hr> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <50443.161.53.168.65.1222676985.squirrel@www.fesb.hr> User-Agent: Mutt/1.5.18 (2008-05-17) 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/258 X-Sequence-Number: 31571 On Mon, Sep 29, 2008 at 10:29:45AM +0200, kiki@fesb.hr wrote: > >> EXPLAIN ANALYSE SELECT * FROM system_alarm WHERE id_camera='3' AND > >> confirmed='false' AND dismissed='false' ORDER BY date DESC, time DESC > >> LIMIT 1; > Sorry, without LIMIT returns around 700000 rows. > Tried to index date column and time column but the performance is pretty > much the same. > Everything is OK, I just don’t understand way is this query burdening the > processor so much. 1. please do not top-post. 2. for this query, you can use this index: create index xxx on system_alarm (id_camera, date, time) where confirmed = 'false' and dismissed = 'false'; or you can make it without where: create index xxx on system_alarm (id_camera, confirmed, dismissed, date, time); but if you usually have the criteria "confirmed = 'false' and dismissed = 'false'" then the first index should be faster. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007