Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A685C37BA3A for ; Mon, 29 Sep 2008 04:26:12 -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 61150-09 for ; Mon, 29 Sep 2008 04:26:07 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id 78D1137BA24 for ; Mon, 29 Sep 2008 04:26:06 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id 29so391973ugo.7 for ; Mon, 29 Sep 2008 00:26:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=vaTxkRWDTJZOEeiVa6kh3bd33DC94SCqyNibLI/4MHA=; b=iE8LMbBJvilJIoNsJB3M/EsLjNthOLQzFL71xAIA+Hc1Z3Q0E1CNzVfePcPjp+k33w bE3a6DcpZG0y8eqPEYGstvFp4JIUcV4/gdqU2yG842jfAAeb6GUbpvRJjoc2MwQLf3c1 xcSKol7oQXYZIKmm/KiUt9pxy48dBMau4Hu6c= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=VDg/EnA/S+KZ2Lxwi4ekTRdFbjaICkn40Lmbcpnzu5NQz5KYsLq4GmbrtCKs0idPrP nODnD2TeAdZnlk15MwlMMyRHP07MuSIAAalM0Otc9/YIoR4JhulMDls9BSlQ6GtxNBgj 1zvRuJ66kSgRULAfcRRiSrHhDGVtPiywYK9lI= Received: by 10.67.92.17 with SMTP id u17mr38189ugl.75.1222673163860; Mon, 29 Sep 2008 00:26:03 -0700 (PDT) Received: by 10.67.97.7 with HTTP; Mon, 29 Sep 2008 00:26:03 -0700 (PDT) Message-ID: <7be3f35d0809290026o43e7879fu760d1ddbc360b177@mail.gmail.com> Date: Mon, 29 Sep 2008 09:26:03 +0200 From: "Harald Armin Massa" To: kiki@fesb.hr Subject: Re: CPU load Cc: pgsql-performance@postgresql.org In-Reply-To: <49550.161.53.168.65.1222672642.squirrel@www.fesb.hr> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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> 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/254 X-Sequence-Number: 31567 Hello Maja, > EXPLAIN ANALYSE SELECT * FROM system_alarm WHERE id_camera=3D'3' AND > confirmed=3D'false' AND dismissed=3D'false' ORDER BY date DESC, time DESC > LIMIT 1; > > (the table is indexed by id_camera, has around 1 milion rows, and this > query returns around 700000 rows and is executed (EXPLAIN ANALYSE) in > around 4800 ms, and this table is queried a lot although not so often > queried modified) 700.000 of 1.000.000 rows is around 70% ... that are nearly all rows. As much as I read you, this table is not often modified. What reason is there for quering all that data again and again instead of keeping it in memory (should it be really needed) ? Harald --=20 GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Stra=DFe 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned!