Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id D718C2E0061 for ; Tue, 27 May 2008 05:13:00 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 64377-06 for ; Tue, 27 May 2008 05:12:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 30.mail-out.ovh.net (30.mail-out.ovh.net [213.186.62.213]) by developer.postgresql.org (Postfix) with SMTP id CD9992E0031 for ; Tue, 27 May 2008 05:12:52 -0300 (ADT) Received: (qmail 27246 invoked by uid 503); 27 May 2008 08:13:10 -0000 Received: from unknown (HELO mail243.ha.ovh.net) (213.186.33.59) by 30.mail-out.ovh.net with SMTP; 27 May 2008 08:13:10 -0000 Received: from b0.ovh.net (HELO queue-out) (213.186.33.50) by b0.ovh.net with SMTP; 27 May 2008 08:12:41 -0000 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13.peufeu.com) (88.161.102.87) by ns0.ovh.net with SMTP; 27 May 2008 08:12:41 -0000 Date: Tue, 27 May 2008 10:19:37 +0200 To: mark , Justin Subject: Re: [PERFORM] select query takes 13 seconds to run with index From: PFC Cc: pgsql-general@postgresql.org, pgsql-performance@postgresql.org Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: <82fa9e310805261549m2362fbe8sbf18789938fee833@mail.gmail.com> <483B4729.7050402@emproshunts.com> <82fa9e310805261632y46f306fdh84e2da62ffbd7873@mail.gmail.com> Content-Transfer-Encoding: Quoted-Printable Message-ID: In-Reply-To: <82fa9e310805261632y46f306fdh84e2da62ffbd7873@mail.gmail.com> User-Agent: Opera Mail/9.24 (Linux) X-Ovh-Tracer-Id: 14010416965936612952 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|H 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.644 tagged_above=0 required=5 tests=AWL=0.644 X-Spam-Level: X-Archive-Number: 200805/967 X-Sequence-Number: 133366 >> The estimate is way off, when was the last time Vaccum was on the tab= le? > about a week ago i ran this VACUUM VERBOSE ANALYZE; > this table is never updated or deleted, rows are just inserted... You should analyze it more often, then... Postgres probably thinks the = = table has the same data distribution as last week ! Analyze just takes a couple seconds... >> Load on the database > how do i measure load on database? Just look at vmstat. Also if you very often do SELECT .. WHERE x =3D ... ORDER BY id DESC yo= u'll = benefit from an index on (x,id) instead of just (x).