Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id B3C422E008B for ; Tue, 27 May 2008 11:47:47 -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 09529-01-6 for ; Tue, 27 May 2008 11:47:21 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rn-out-0910.google.com (rn-out-0910.google.com [64.233.170.187]) by developer.postgresql.org (Postfix) with ESMTP id 47ED72E0091 for ; Tue, 27 May 2008 11:46:07 -0300 (ADT) Received: by rn-out-0910.google.com with SMTP id v73so1503182rnb.0 for ; Tue, 27 May 2008 07:46:06 -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=ifQfs+vKuHGgAVIJvYvcC97N9O+aDSlc767Gg/luQOA=; b=UVcAXW3tXvOz7arNTSNfhHwzofJBBBdKYCRfWXkgPri2RPJtdVL2Wh7s1qukUJJX+nGV4pakosO/z2OYCHPGzuMX/diKR3/9ZlC7pQUhbth/7IfguJufKg7+ABEJ4ogx8bufS/UDyhJE33a9zf6QZ13jdGYTxB7apmYMDduyPsU= 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=sKYB0ahKrFSRu8xiLyC/wssw4P4FoFiGo5Urr/cpW2dwEK+xEDssDIOrGETkeCnfAOlflVKSPFpGpI+iwE437gncxxJCebYKj42MsynoSToS+rv6zGOxZfKfo+KaG0fpnuUj9QH2ZzVLOF3CdLCCQlTCI0Cp1UxVuHScqxitQi0= Received: by 10.115.18.1 with SMTP id v1mr1396769wai.15.1211899565253; Tue, 27 May 2008 07:46:05 -0700 (PDT) Received: by 10.114.56.5 with HTTP; Tue, 27 May 2008 07:46:05 -0700 (PDT) Message-ID: <82fa9e310805270746u56334348m34055cb31219ff45@mail.gmail.com> Date: Tue, 27 May 2008 07:46:05 -0700 From: mark To: depesz@depesz.com Subject: Re: [GENERAL] select query takes 13 seconds to run with index Cc: Justin , pgsql-performance@postgresql.org, PFC In-Reply-To: <20080527082201.GA17943@depesz.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <82fa9e310805261549m2362fbe8sbf18789938fee833@mail.gmail.com> <483B4729.7050402@emproshunts.com> <82fa9e310805261632y46f306fdh84e2da62ffbd7873@mail.gmail.com> <20080527082201.GA17943@depesz.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/346 X-Sequence-Number: 30314 On Tue, May 27, 2008 at 1:22 AM, hubert depesz lubaczewski wrote: > 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 OK I did this ALTER TABLE pokes ALTER uid set statistics 500; ALTER TABLE ANALYZE pokes; ANALYZE and then it became super fast!! thanks a lot!!! my question: -> is 500 too high? what all does this affect? -> now increasing this number does it affect only when i am running analyze commands, or will it slow down inserts and other operations? EXPLAIN ANALYZE select * from pokes where uid = 578439028 order by id desc limit 6; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=467.80..467.81 rows=6 width=134) (actual time=0.016..0.016 rows=0 loops=1) -> Sort (cost=467.80..468.09 rows=117 width=134) (actual time=0.016..0.016 rows=0 loops=1) Sort Key: id Sort Method: quicksort Memory: 25kB -> Index Scan using idx_pokes_uid on pokes (cost=0.00..465.70 rows=117 width=134) (actual time=0.011..0.011 rows=0 loops=1) Index Cond: (uid = 578439028) Total runtime: 0.037 ms