Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id ED66E2E003D; Tue, 27 May 2008 05:22:13 -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 77334-04; Tue, 27 May 2008 05:22:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from depesz.com (depesz.com [88.198.37.40]) by developer.postgresql.org (Postfix) with ESMTP id 49B802E0031; Tue, 27 May 2008 05:22:07 -0300 (ADT) Received: from depesz by depesz.com with local (Exim 4.63) (envelope-from ) id 1K0uRV-0004q7-3V; Tue, 27 May 2008 10:22:01 +0200 Date: Tue, 27 May 2008 10:22:01 +0200 From: hubert depesz lubaczewski To: mark Cc: Justin , pgsql-general@postgresql.org, pgsql-performance@postgresql.org Subject: Re: [PERFORM] select query takes 13 seconds to run with index Message-ID: <20080527082201.GA17943@depesz.com> Reply-To: depesz@depesz.com References: <82fa9e310805261549m2362fbe8sbf18789938fee833@mail.gmail.com> <483B4729.7050402@emproshunts.com> <82fa9e310805261632y46f306fdh84e2da62ffbd7873@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: <82fa9e310805261632y46f306fdh84e2da62ffbd7873@mail.gmail.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/968 X-Sequence-Number: 133367 On Mon, May 26, 2008 at 04:32:50PM -0700, mark wrote: > >> EXPLAIN ANALYZE select * from pokes where uid = 578439028 order by id > >> DESC limit 6; > > The estimate is way off, when was the last time Vaccum was on the table? > about a week ago i ran this VACUUM VERBOSE ANALYZE; > this table is never updated or deleted, rows are just inserted... 1. boost default_statistics_target 2. run analyze more often - daily job for example 3. create index q on pokes (uid, id); should help depesz