X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 968E4D1CACD for ; Mon, 16 Feb 2004 18:02:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 32787-02 for ; Mon, 16 Feb 2004 14:02:30 -0400 (AST) Received: from easily.co.uk (mercury0.easily.co.uk [213.161.76.90]) by svr1.postgresql.org (Postfix) with ESMTP id 3F292D1D643 for ; Mon, 16 Feb 2004 14:02:28 -0400 (AST) Received: from [213.152.63.90] (account f4vo5dsy5djd HELO chuckie.co.uk) by easily.co.uk (CommuniGate Pro SMTP 4.1.3) with ESMTP id 49070624; Mon, 16 Feb 2004 18:02:24 +0000 Message-ID: <403105A2.9040907@chuckie.co.uk> Date: Mon, 16 Feb 2004 18:02:10 +0000 From: Nick Barr User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Nick Barr Cc: David Teran , PgSQL Performance ML Subject: Re: select max(id) from aTable is very slow References: <62A9E13D-60A0-11D8-B420-000A95A6F0DC@cluster9.com> <4031045C.2070707@chuckie.co.uk> In-Reply-To: <4031045C.2070707@chuckie.co.uk> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/172 X-Sequence-Number: 5696 Nick Barr wrote: > 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: >> >> ------------------------------------------------------------------------ >> -------------------------------------------------------- >> Aggregate (cost=153635.15..153635.15 rows=1 width=4) (actual >> time=9738.263..9738.264 rows=1 loops=1) >> -> Seq Scan on job_property (cost=0.00..137667.32 rows=6387132 >> width=4) (actual time=0.102..7303.649 rows=6387132 loops=1) >> Total runtime: 9738.362 ms >> (3 rows) >> >> >> >> I recreated the index on column id and ran vacuum analyze >> job_property but this did not help. I tried to force index usage >> with SET ENABLE_SEQSCAN TO OFF; but the explain analyze still looks >> like the query is done using a seqscan. >> >> Is the speed more or less normal for a 'dual G5 with 2 GHZ and 4 GB >> of Ram and a SATA hd' or do i miss something? >> >> regards David >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly > > > Try using: > > SELECT id FROM theTable ORDER BY is DESC LIMIT 1; > > Using COUNT, MAX, MIN and any aggregate function on the table of that > size will always result in a sequential scan. There is currently no > way around it although there are a few work arounds. See the following > for more information. > > http://archives.postgresql.org/pgsql-performance/2004-01/msg00045.php > http://archives.postgresql.org/pgsql-performance/2004-01/msg00054.php > http://archives.postgresql.org/pgsql-performance/2004-01/msg00059.php > > HTH > > Nick > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster Oops that should be SELECT id FROM theTable ORDER BY id DESC LIMIT 1; Nick