Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 928419FC129 for ; Fri, 12 Oct 2007 19:04:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 53301-03 for ; Fri, 12 Oct 2007 19:04:40 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from skynet.simkin.ca (skynet.simkin.ca [72.51.27.117]) by postgresql.org (Postfix) with ESMTP id A1CFD9FBC3D for ; Fri, 12 Oct 2007 19:04:45 -0300 (ADT) Received: from charon.medialogik.com (charon.medialogik.com [72.51.27.114]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by skynet.simkin.ca (Postfix) with ESMTP id D6056400E for ; Fri, 12 Oct 2007 15:04:43 -0700 (PDT) From: Alan Hodgson Organization: Simkin Network Consulting To: pgsql-performance@postgresql.org Subject: Re: How to speed up min/max(id) in 50M rows table? Date: Fri, 12 Oct 2007 15:04:40 -0700 User-Agent: KMail/1.9.6 References: <470FE136.2010601@emolecules.com> In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200710121504.40365@hal.medialogik.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/159 X-Sequence-Number: 27181 On Friday 12 October 2007, henk de wit wrote: > > select payment_id from transactions order by payment_id desc limit 1; > > This one is indeed instant! Less than 50ms. In my case I can't use it for > max though because of the fact that payment_id can be null (which is an > unfortunate design choice). The other variant however didn't become > instant. I.e. I tried: > > select time from transactions where payment_id = 67 order by time asc > limit 1; > > But this one is still really slow. If you had a compound index on payment_id,time (especially with a WHERE time IS NOT NULL conditional) it would likely speed it up. -- Ghawar is dying