From: | hubert depesz lubaczewski <depesz(at)depesz(dot)com> |
---|---|
To: | kiki(at)fesb(dot)hr |
Cc: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: CPU load |
Date: | 2008-09-29 08:46:30 |
Message-ID: | 20080929084630.GA26233@depesz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Mon, Sep 29, 2008 at 10:29:45AM +0200, kiki(at)fesb(dot)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 dont 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(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2008-09-29 08:50:27 | Re: CPU load |
Previous Message | kiki | 2008-09-29 08:39:29 | Re: CPU load |