X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CD48FD1B193 for ; Thu, 3 Jun 2004 10:07:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22233-10 for ; Thu, 3 Jun 2004 13:07:48 +0000 (GMT) Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net [194.217.242.85]) by svr1.postgresql.org (Postfix) with ESMTP id CF2A8D1B173 for ; Thu, 3 Jun 2004 10:07:44 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-35.mail.demon.net with esmtp (Exim 3.35 #1) id 1BVrwi-000Cm9-0Z; Thu, 03 Jun 2004 14:07:48 +0100 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 4C62916707; Thu, 3 Jun 2004 14:07:47 +0100 (BST) Message-ID: <40BF22A5.8060102@archonet.com> Date: Thu, 03 Jun 2004 14:07:49 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.6 (X11/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: David Teran Cc: PgSQL Performance ML Subject: Re: select max(id) from aTable is very slow References: <62A9E13D-60A0-11D8-B420-000A95A6F0DC@cluster9.com> In-Reply-To: <62A9E13D-60A0-11D8-B420-000A95A6F0DC@cluster9.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/27 X-Sequence-Number: 7085 David Teran wrote: > Hi, > > we have a table with about 6.000.000 rows. There is an index on a > column with the name id which is an integer and serves as primary key. > > When we execute select max(id) from theTable; it takes about 10 > seconds. Explain analyze returns: Due to the open-ended nature of PG's aggregate function system, it can't see inside the max() function to realise it doesn't need all the values. Fortune favours the flexible however - the simple workaround is to use the equivalent: SELECT id FROM theTable ORDER BY id DESC LIMIT 1; -- Richard Huxton Archonet Ltd