pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: hubert depesz lubaczewski <depesz@depesz.com>
To: kiki@fesb.hr
Cc: Albe Laurenz <laurenz.albe@wien.gv.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: CPU load
Date: Mon, 29 Sep 2008 10:46:30 +0200
Message-ID: <20080929084630.GA26233@depesz.com> (raw)
In-Reply-To: <50443.161.53.168.65.1222676985.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>
	<D960CB61B694CF459DCFB4B0128514C2028AAD22@exadv11.host.magwien.gv.at>
	<50443.161.53.168.65.1222676985.squirrel@www.fesb.hr>

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



view thread (16+ messages)  latest in thread

Message-ID: <20080929084630.GA26233@depesz.com>
Permalink:  ../20080929084630.GA26233@depesz.com/
Also on:    postgresql.org/message-id/20080929084630.GA26233@depesz.com

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: depesz@depesz.com, kiki@fesb.hr, laurenz.albe@wien.gv.at
  Subject: Re: CPU load
  In-Reply-To: <20080929084630.GA26233@depesz.com>

* 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