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 5E5BBD1D25D for ; Mon, 16 Feb 2004 17:57:11 +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 31050-01 for ; Mon, 16 Feb 2004 13:57:07 -0400 (AST) Received: from easily.co.uk (mercury0.easily.co.uk [213.161.76.90]) by svr1.postgresql.org (Postfix) with ESMTP id 0DA76D1B456 for ; Mon, 16 Feb 2004 13:57:05 -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 49067027; Mon, 16 Feb 2004 17:56:56 +0000 Message-ID: <4031045C.2070707@chuckie.co.uk> Date: Mon, 16 Feb 2004 17:56:44 +0000 From: Nick Barr User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) 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=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/171 X-Sequence-Number: 5695 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