Skip site navigation (1) Skip section navigation (2)

select max(id) from aTable is very slow

From: David Teran <david(dot)teran(at)cluster9(dot)com>
To: PgSQL Performance ML <pgsql-performance(at)postgresql(dot)org>
Subject: select max(id) from aTable is very slow
Date: 2004-02-16 16:51:37
Message-ID: 62A9E13D-60A0-11D8-B420-000A95A6F0DC@cluster9.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


Responses

pgsql-performance by date

Next:From: Nick BarrDate: 2004-02-16 17:56:44
Subject: Re: select max(id) from aTable is very slow
Previous:From: Tom LaneDate: 2004-02-16 03:28:48
Subject: Re: optimization ideas for frequent, large(ish) updates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group