Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 660662E004D for ; Mon, 26 May 2008 20:26:44 -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 15951-10 for ; Mon, 26 May 2008 20:26:37 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.emproshunts.com (mail.emproshunts.com [66.64.191.19]) by developer.postgresql.org (Postfix) with ESMTP id 99CDA2E0032 for ; Mon, 26 May 2008 20:26:41 -0300 (ADT) Received: from [192.168.1.49] by mail.emproshunts.com (GMS 15.00.3626/KX5895.00.ca7cb0e7) with ESMTP id qamqhaaa for pgsql-general@postgresql.org; Mon, 26 May 2008 19:26:39 -0400 Message-ID: <483B4729.7050402@emproshunts.com> Date: Mon, 26 May 2008 19:26:33 -0400 From: Justin User-Agent: Thunderbird 2.0.0.14 (Windows/20080421) MIME-Version: 1.0 To: mark , pgsql-general@postgresql.org Subject: Re: select query takes 13 seconds to run with index References: <82fa9e310805261549m2362fbe8sbf18789938fee833@mail.gmail.com> In-Reply-To: <82fa9e310805261549m2362fbe8sbf18789938fee833@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [192.168.1.49] X-DNSBL: 0 X-AntiSpam: Checked for restricted content by Gordano's AntiSpam Software X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/952 X-Sequence-Number: 133351 mark wrote: > Hi, is there anyway this can be made faster? id is the primary key, > and there is an index on uid.. > > thanks > > > EXPLAIN ANALYZE select * from pokes where uid = 578439028 order by id > DESC limit 6; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------------------------------- > Limit (cost=0.00..9329.02 rows=6 width=135) (actual > time=13612.247..13612.247 rows=0 loops=1) > -> Index Scan Backward using pokes_pkey on pokes > (cost=0.00..5182270.69 rows=3333 width=135) (actual > time=13612.245..13612.245 rows=0 loops=1) > Filter: (uid = 578439028) > Total runtime: 13612.369 ms > (4 rows) > > First this should be posted on performance list. how many records are in this table? The uid 578,439,028 assuming this is auto incremented key that started 1 this means there is 578 million records in the table. The estimate is way off, when was the last time Vaccum was on the table? What verison of Postgresql are you running Size of the Table Table layout Load on the database